Click here to Skip to main content
15,867,750 members
Articles / Database Development / SQL Server

Coding an N-Tier Application in C# without any Wizard - Part I

Rate me:
Please Sign up or sign in to vote.
2.71/5 (68 votes)
6 Apr 2009CPOL8 min read 242K   4.2K   106   77
Beginner to intermediate in N-Tier

Introduction

Previously, I wrote an article about building an n-tier application in VB.NET. If you check the article, I was at a certain level of programming at that time and I have utilized wizards that were included in Visual Studio, and that never give me a chance to understand what the wizard does behind the scenes. I found myself stuck again because of the limitation of this wizard supplied by VS. Don't get me wrong, they help, but only when you develop at a beginner level. Just imagine if you are new to .NET and you use a data adapter wizard to get data from two tables using a join. Well the wizard will not complain, but when it comes time to update, it's a problem for you. The wizard will not be able to help you, then you have to code your data layer yourself. Imagine telling a new person in programming that he must use a data layer. He won't understand until you explain step by step what is a data layer. Now in this article, we are going to create an n-tier application without any wizard. We will concentrate on inserting only until the next article. This is an introduction until the next article.

This is how a tier will look like when it's complete:

tier.jpg

Background

This is an n-tier application, that has physical tiers separated. Of course, we have to design the user interface and come back to DAL, but I think it's not a good programming practice to have a lot of text in your presentation layer. Don't get me wrong, it does not affect anything from the system, but if you can look at your presentation layer(form), Visual Studio will generate its code and believe me, it does not look good to write such big comments in there. OK, let's get back to the topic.

I love n-tier applications, they are easily maintained, and easy to design. So the whole project consists of tier, the PL(presentation layer) that is your user interface, ASP.NET pages, BLL (business logic layer) those are your classes that take care of formatting your data. Validating data and other things we will do later in this project and the DAL (data access layer) in this layer, that's where we run SQL queries to the SQL database or Access or Oracle. Please note that in this layer, we don't take care of the errors.

Using the Code

We are going to use multiple comments in our article and we are going to use C# as our language.

Points of Interest

While writing code in n-tier applications using wizards and naked SQL statements, I have learned a lot of things that led me to write this article. I once promised Dave that I will never use a wizard to do my database work and it's working for me, and I have learned again to use both VB.NET and C#, thanks to CodeProject.

History

I would like to take this chance and dedicate this article to the careways group I went for an interview that gave me a valiant to code in C# and believe me, it was so easy to work with it. Academically, I used to develop in C++ under the Linux environment and Java. The idea of OOP in C++ or Java was a great experience and I longed with all my heart to work with that. I preferred C++ over Java, because of multiple inheritance, but now C# will have to do like Java and implement interfaces. Still I am very grateful that I am back to that world. thanks to the careways group team, ian roets and his team, I say I would love to work with you, you brought me back to the world, when VB.NET kept me captive with its user friendly IDE. Lastly guys don’t laugh at this, this is not a joke , I keep on using “//” to comment my VB.NET code, do you see what you have done? I love it because I can interchange the languages, but for now I will stick to C#.

Start

Now before we start coding, let's build our project and understand it. Open Visual Studio and select a new project, choose C# Windows project and name it "client". This is going to be our presentation layer and add some textboxes so that it will look like this:

pl_presentation_layer_d.jpg

After you are done, go to file, add a new project and select a classlibrary and name it "clsbll". After you did that, note that in your clsbll project, there will be "class1". change it to "bll", and add another project again and name it "clsdal" and do the same thing by renaming the class1 to "DAL" and when you are done, your solution explorer will look like this:

project_explorerd.jpg

Now, there are three projects in this solution. For this part of this article, we are going to look at the insert only. We have seen our presentation layer, then now it's time for database work. Let's just create our own database and table that we can even use for part II of this article.

Database Work (SQL)

In my case, I have both SQL 2000 and SQL 2005. You can use any of your choice. If you use 2000, go to query analyzer and start coding. Remember even if you have SQL 2005, we don't use wizards to do our database work. Let's create a database, like this:

SQL
create database valrollclients

OK, now it's time to create our table for clients:

SQL
create table client_details
(
client_id int identity(100,01) primary key not null,
client_name char(12) null ,
client_lastname char(15) null, 
client_age int null
)

So our database and our tables are in place. What we need is a stored procedure that we are going to use. Let's define our stored procedure:

SQL
set nocount on
create procedure prcinsert_client
(
@client_name char(12) ,
@client_lastname char(15) ,
@client_age int 
)
as 
insert into client_details
values(@client_name,@client_lastname,@client_age)

Now our database work is done in SQL. Let's go back to Visual Studio.

DAL(Data Access Layer)

Now our presentation layer is done but not with code, now we need to start from back to front. We are going to start by coding the DAL. Open the DAL class. We need to add namespaces that are not automatically available. Let's import those namespaces.

C#
using system.data.sqlclient;
using system.data;

public class dal
    {   /*declare a string variable and assign a connection string to it*/
        string strcon  = @"user id  = sde ; password = passrd;
        server =dfgserver;database =valrollclients";
        
         //in this line, we are declaring a command object for inserting
         sqlcommand cmdinserted;
         sqlconnection con; //declaring a connection object 
        
        /*The following method is going to insert client details into the client_details
        table. our presentation layer will need us to save the data into our database,
        the main objective of this project is to show how we do n-tier application
        and from this example, we are only going to add data into the database. Now
        the following method is going to save data that is valid into the database,
        the data is accepted from the bll, that means the data has passed the
        business rules that we have set and some calculations have been done if
        needed. This method accepts 3 parameters, that means bll has to supply 
        3 valid parameters to the dal to insert the data into the database, and we
        are going to use a stored procedure to avoid SQL injection and besides
        that using stored procedure is easier than using naked SQL statements, if
        you want information on SQL injection follow the link
        http://msdn2.microsoft.com/en-us/library/ms161953.aspx
        Now the SQL procedure is already defined as we have seen.*/

        public void insert_clients(string client_name, string client_lastname,
           int client_age)
        {
            /* instantiating the inserted command object that we have declared earlier
            if you look at this code, it can be written in one line, but for the
            purpose of understanding, we have to break it into parts */
            cmdinserted = new sqlcommand();

            /*now here we are passing the storedprocedure name in the command text
            the command text carries the SQL statement, if we decided to use naked
            SQL statement, we would have had something like
            "select * from table", but we used a storedprocedure instead. */
            cmdinserted.commandtext = "[dbo].[prcinsert_client]";

            /*in this line, we want to avoid the time out exceptions by setting the
            number to infinite, but Microsoft recommended to use a number. */
            cmdinserted.commandtimeout = 0;

            //we are telling the command object that we are going to use a
            //stored procedure
            cmdinserted.commandtype = commandtype.storedprocedure; 

            //we are initializing the connection object and pass
            //the connection string in the constructor
            con = new sqlconnection(strcon); 

            //we are telling the connection object what connection we are going to use
            //below we are adding parameters to the command object to insert, as
            //we have seen that our stored procedure will require some parameters.
            //ok let's explain this statement in detail 

            cmdinserted.connection = con; 
            cmdinserted.parameters.add("@client_name",
               sqldbtype.varchar, 12).value = client_name;

            cmdinserted.parameters.add("@client_lastname",
               sqldbtype.varchar, 15).value = client_lastname;

            cmdinserted.parameters.add("@client_age ",
               sqldbtype.int, 4).value = client_age;
           /*from the command object inserted we add a parameter and name it
           client_age, remember that the parameter should be in quotes and the 
           datatype and the dimension must be the same as the one defined in the
           table, or else you will encounter the problems and another thing, the order 
           * of variable declaration in your procedure should be the same as the
           * order in your VB or C# code.
           * now the last part of the code, we are assigning the value to the parameter
           * from the string that has been accepted from BLL in our method. I hope
           * the explanation is clear*/
            try
            {
                con.open(); //open connection

                cmdinserted.executenonquery(); //execute the stored procedure

                con.close();//close connection
            }
            catch (sqlexception) //catch an error
            {
                throw; //throw it back to the calling method 
            }
        }
    }

Now that was our DAL class which has only one method. Now remember that our client should not access our DAL directly and that means our BLL should be the middleman between the DAL and the client. That means we have to add a reference in a BLL project to a DAL project and in the client project, we are going to add a reference to only a BLL.

BLL(Business Logic Layer)

Now in our BLL, we are going to handle all the business rules, errors. As you have seen in our DAL class, we only trapped the exceptions but never displayed them from there, we only threw them to the calling method, and that method will only come from BLL and the BLL will send the message to the client. That means BLL is meant to control the validity and consistency of data between the layers, the data that is coming from DAL is made ready to be presented by the client and the data that is coming from the client application (presentation layer) is checked for validity and passed to DAL. So the first thing we are going do is to add a reference on the BLL to the DAL project, so that we can call the method save. After we have done that, let's go and code our BLL.

C#
public class bll
{
    public int save_client(string clientname, string clientlastname, string clientage)
    {
        boolean bopassed = true; //declare a boolean variable
        /*assigning the boolean results from the check_rules function to 
         * the variable bopassed, that means the data is valid*/
        bopassed = check_rules(clientname, clientlastname, clientage);
        //creating an object of a class and instantiating it
        clsdal.dal obj = new clsdal.dal();
        int Res = 0;
        try
        {
            if (bopassed == true) //if the rules are passed then 
            {
                //save the records 
                obj.insert_clients
		(clientname, clientlastname, convert.toint32(clientage));
                Res = 1;
            }
            else //else 
            {
                //the rules are not passed send the user a notification that something 
                //is wrong
                Res = 0;
            }
        }
        catch (SqlException)
        {
            throw;
        }

        return Res;
    }

        /* let's check the rules, this is just an example of what kind of things should
         * be present in the bll, it can be calculations and the data after the
         * calculations \ should be brought back to correct datatypes that the dal
         * can understand and they should be valid..*/
 
        private boolean  check_rules(string client_name,string clientlastname,
            string clientage)
        { /*we are accepting the input that was accepted from the client and they are
           validated for empty string later the age will be converted to an integer*/

            boolean bolres = true; //declaring a boolean variable
            if (client_name == "")  //testing for empty string
            {
                bolres = false;  //if its empty set the bolres variable to false 
             }
                if (clientlastname == "")
                {
                    bolres = false;
                }

                 if (clientage =="")
                 {
                        bolres = false;
                 }
               
            
           /*return a boolean value based on the test, if one of the fields say "false"
           then the function will return false, because remember the SQL
           stored procedure will require all the fields to present.*/
           return bolres;
                }
    } 

Now that our BLL is complete and commented and done, let's go to our client again and finish the job.

PL(Presentation Layer)

This is the part that our everyday users see and use. Now we have to set the reference so that we can use the functions and methods of our BLL, after you are done setting the reference, double click on the save button and start coding.

C#
clsbll.bll obj = new clsbll.bll();
string clientname = txtclientname.text ;
string clientlastname = txtclientlastname.text;
string clientage = txtclientage.text;
int Res = 0;
        try 
        {
        Res = obj.save_client(clientname,clientlastname,clientage); 
        }
        catch(SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
        if(res  == 1)  
        {   
            MessageBox.Show("Data Saved");
        }
        else
        {
        MessageBox.Show("Data not Saved");
        }

After you are done, remember that there is a clear button, this is just an extra thing, it has nothing to do with n-tier. There is a clear button. Double click on it to add code.

C#
txtclientname.clear();
txtclientlastname.clear() ;
txtclientage.clear();

This code will clear the textboxes when pressed. Now if we test our application like this, press F5 on our keyboard and you will see your PL(presentation layer), if you try to click save button without any input, you will get an error message that was trapped in our BLL. This shows that the BLL is good and responsible for messages and error handling. That means the check_rules function returned a false and again that means the rules had been violated.

err_blld.jpg

And if you add input and try it again, you will receive a message that confirmed that the rules had not been violated.

record_savedd.jpg

To confirm that the record has been saved, without relying on our message that we displayed in the BLL, you can run a select * statement in your SQL database, and you will see the record that you have entered.

sql_reocrdd.jpg

Conclusion

We wrote this application without any help from a wizard, but for now we added only into our table. In part II of this article, we are going to update, delete and search the datagrid. The data will be coming from a join, and that means the datagrid will be displaying data from two tables as if they are from one table. Now the tricky part that a wizard cannot handle is the update and delete. I am going to explain how can we design our DAL and our BLL without a wizard using storedprocedures in n-tier. I thank you all for your support and don't punish me for not formatting this article neatly. I wrote the tags myself and it was a pain, I could not get a toolbar in CodeProject, but CodeProject's Sean tried to get me the toolbar, I thank you for your help. I believe the article that was been deleted yesterday was going to be better than this one. I explained well and clear, if I was given a chance to finish it, it would be nice for beginners.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Vimalsoft
South Africa South Africa
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.
This is a Organisation

4 members

Comments and Discussions

 
GeneralGreat article! Pin
TheBassmann11-Jun-15 2:37
TheBassmann11-Jun-15 2:37 
GeneralMy vote of 5 Pin
srilekhamenon12-Feb-14 2:17
professionalsrilekhamenon12-Feb-14 2:17 
GeneralRe: My vote of 5 Pin
Vimalsoft(Pty) Ltd12-Feb-14 5:07
professionalVimalsoft(Pty) Ltd12-Feb-14 5:07 
QuestionN Tier Pin
Simba Constantine T11-Feb-14 21:12
Simba Constantine T11-Feb-14 21:12 
AnswerRe: N Tier Pin
Vimalsoft(Pty) Ltd12-Feb-14 5:08
professionalVimalsoft(Pty) Ltd12-Feb-14 5:08 
QuestionThank You very much Pin
Ravikumar phad25-Jan-14 1:08
Ravikumar phad25-Jan-14 1:08 
AnswerRe: Thank You very much Pin
Vimalsoft(Pty) Ltd12-Feb-14 5:08
professionalVimalsoft(Pty) Ltd12-Feb-14 5:08 
GeneralMy vote of 4 Pin
R.Binu Port Blair25-Apr-13 3:30
R.Binu Port Blair25-Apr-13 3:30 
GeneralRe: My vote of 4 Pin
Vimalsoft(Pty) Ltd12-Feb-14 5:09
professionalVimalsoft(Pty) Ltd12-Feb-14 5:09 
GeneralMy vote of 5 Pin
Paulo Andrade31-Mar-13 1:34
Paulo Andrade31-Mar-13 1:34 
GeneralRe: My vote of 5 Pin
Vimalsoft(Pty) Ltd31-Mar-13 2:26
professionalVimalsoft(Pty) Ltd31-Mar-13 2:26 
Questionhi Pin
selvaaa_09620-Dec-11 18:30
selvaaa_09620-Dec-11 18:30 
AnswerRe: hi Pin
Vimalsoft(Pty) Ltd20-Dec-11 19:25
professionalVimalsoft(Pty) Ltd20-Dec-11 19:25 
GeneralMy vote of 5 Pin
sri_mncl20-Oct-11 2:18
sri_mncl20-Oct-11 2:18 
Questionthanx Pin
imranusoof2-Aug-11 21:58
imranusoof2-Aug-11 21:58 
AnswerRe: thanx Pin
Vimalsoft(Pty) Ltd2-Aug-11 23:42
professionalVimalsoft(Pty) Ltd2-Aug-11 23:42 
GeneralThank you! Pin
CrshTstDmmy27-Jan-11 0:39
CrshTstDmmy27-Jan-11 0:39 
GeneralRe: Thank you! Pin
Vimalsoft(Pty) Ltd27-Jan-11 2:16
professionalVimalsoft(Pty) Ltd27-Jan-11 2:16 
GeneralMy vote of 5 Pin
CrshTstDmmy27-Jan-11 0:37
CrshTstDmmy27-Jan-11 0:37 
GeneralN-Tier in C# without any Wizard Pin
humberbt9-Oct-10 12:40
humberbt9-Oct-10 12:40 
GeneralRe: N-Tier in C# without any Wizard Pin
Vimalsoft(Pty) Ltd10-Oct-10 20:40
professionalVimalsoft(Pty) Ltd10-Oct-10 20:40 
GeneralMy vote of 4 Pin
humberbt6-Oct-10 2:29
humberbt6-Oct-10 2:29 
GeneralRe: My vote of 4 Pin
Vimalsoft(Pty) Ltd10-Oct-10 20:38
professionalVimalsoft(Pty) Ltd10-Oct-10 20:38 
GeneralMy vote of 5 Pin
kourosh235-Oct-10 22:47
kourosh235-Oct-10 22:47 
GeneralRe: My vote of 5 Pin
Vimalsoft(Pty) Ltd10-Oct-10 20:31
professionalVimalsoft(Pty) Ltd10-Oct-10 20:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.