Coding an N-Tier Application in C# Without Any Wizard: Part II

• Download source code - 40.1 KB

Introduction

This is the last part of this article series. In this article, we are going to do a lot with a DataGrid: update, delete, and search.

My next article will take you to the world of Remoting. We are going to take the tiers that we have built and host them into different machines. I have looked for an easy to understand article on Remoting, and most start by explaining things nicely but end up complicated. Bu,t I promise I will not part from the way I explain my articles. It will be the simplest Remoting article you have ever read. I am not going to display the N-Tier diagram again, we all know about it, I will just get straight to the point.

Background

In this article, we are going to search, update, and delete using a DataGrid. We are going to use Stored Procedures with the “Match-All-Updating” concurrency logic. The logic of the Stored Procedure should be encapsulated, and the logic of the DAL (Data Access Layer) should be encapsulated too. Only the correct functions and methods should be called by the BLL (Business Logic Layer), and the Presentation layer should only call the functions of the BLL, and still the Logic of the BLL should be encapsulated. Lastly, in the previous article we created a table “Client Details”, we are going to use that table, but we need to create another table and name it “Products” to make this interesting.

Using the code

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

Start

We are going to search, update, and delete records in a DataGridView. We have to figure out how are we going to do that. The first thing we are going to do is create another table, to add to the one we created in the previous article. Open your query analyser and create a table like this:

Create table Products
(
    Pro_ID int Primary key not null, 
    Client_ID int Foreign key (Client_ID) references Client_Details not null,
    [Prod_Name] [varchar](50) NULL,
    Prod_Description varchar(50) null,
    Purchase_Date DateTime Not null,
    Price Money null,
    Delivered int null
)

Press F5 to run it, and populate your table with data:

insert into Products (Pro_ID,Client_ID,Prod_Name,Prod_Description,Purchase_Date,Price)
values(2024,102,'Valuation Roll 2001','Property Values for 2001',Getdate(),15000)

Populate at least four records, and query your table to make sure that you have values in your tables:

select * from Client_Details

select * from Products

Both tables should return records. The Client_ID is the Primary Key of the table created in the previous article, and it’s a foreign key in the Product table. We are not yet done with our database work. Next, we are going to create the Stored Procedures for our CRUD application.

Database Work (SQL)

In my case, I have both SQL Server 2000 and SQL Server 2005, you can use any of your choice. If you are using SQL Server 2000, go to query analyser and start coding. Remember, even if you have SQL Server 2005, we don’t use wizards to do our database work. Let’s create Stored Procedures like this: Our first Stored Procedure is for a user to search the DataGrid based on the client name, and please note that all our procedures will display the data from a Join. Now, our Stored Procedure will look like this:

--SEARCHING THE GRID 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SEARCH_CLIENT] 

@CLIENT_NAME VARCHAR(12)
AS

select p.Pro_id,p.Client_ID,p.Prod_Name as [Product Name],
p.Prod_Description as [Description],
p.Price,c.Client_Name, p.Delivered from Products p
inner Join Client_Details c on 
p.Client_ID = c.Client_ID
where c.Client_name LIKE @CLIENT_NAME + '%' 
/*I USED '%', TO ALLOW USERS TO SEARCH WITHOUT THE WILDCARD
  BUT IT A USER ENTERS AN EMPTY STRING, IT WILL RETURN
  ALL RECORDS YOU CAN CHOOSE TO ALLOW USERS TO ENTER IT THEMSELVES */
*/

Now, remember that the above Stored Procedure will search using a Join, and when we update, we have to know which table we want to update. In this article, we are going to update only one table, and one field: “Delivered” in the "Product" table, by altering the status of the product on the field “Delivered” that exists in the table “Products”. Which means, we are only going to update the Products table. Our Update Stored Procedure will look like this:

--UPDATING THE GRID 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Update Stored Procedure
ALTER PROCEDURE [dbo].[Update_Status_Grid]
(--Carry Original values before Update
@Original_Pro_id int,
@Original_Delivered  int = null,
@Delivered int
)
as 
Update Products 
set Delivered = @Delivered
where (Pro_ID = @Original_Pro_id )
And   (Delivered = @Original_Delivered 
OR     @Original_Delivered     Is Null And Delivered  Is Null)

--Refreshing Data
;
select p.Pro_id,p.Client_ID,p.Prod_Name as [Product Name],
p.Prod_Description as [Description],
p.Price,c.Client_Name, p.Delivered from Products p
inner Join Client_Details c on 
p.Client_ID = c.Client_ID
where (Pro_id = @Original_Pro_id )

Mmmm…. Interesting, let me tell you what is happening in the above procedure. First, we have to store the original values and the values to be used for updating the variables, and before we update, we need to check if the values have not changed since we retrieved the records using the first procedure. If the values have not changed, the update can take place. And, at the end, we are refreshing the data. We used the original value to construct the Where clause that finds the original record. This way, if a single field has been modified, the records will not mach and the change will not take place. This is concurrency control, and you can choose to write the above procedure in a different way to tackle concurrency.

--Delete THE GRID 

Create PROCEDURE Delete_Record
(--Carry Original values before Update
@Pro_id int
)
as 
Delete Products 
where Pro_ID =@Pro_id

The Stored Procedure is simple, it just deletes based on the Primary Key. Now, we are done with our database work, it's time for coding. Open your Visual Studio and select Windows Application in C#, and name it “Client_App”. Put some nice background as I did, though it is not important. And after you have done that, your Form will look like this:

If you’re still using Visual Studio 2003, you add a “DataGrid”, and in VS2005, you add a “DataGridView”. As you can see from the above Form, we are first going to search, and after that, update and delete. Your Solution Explorer should now look like this:

Remember, we are building an N-Tier application. What we are going to do is add other tiers like we did in the previous articles. The next step is to add a BLL as a Class Project. Add another class project to the existing project and name it BLL, and add another and name it “DAL”. Now, your Solution Explorer will look like this:

As you can see, the bold one, Client_App is the Presentation Layer, and that is what users will interact with. We also have the BLL (Business Logic Layer) and our DAL (Data Access Layer). Before we code, I would like you to change the name of the classes to correspond to their projects. E.g., change BLL class1.cs to “BLL.cs”. When done, your project in the Solution Explorer should look like this:

Now everything is done, it's time for coding. The question is where do we start. OK, I prefer from back to front, which means we fo from the DAL (Data Access Layer) to the PL (Presentation Layer).

DAL (Data Access Layer)

Our Presentation layer is done but not with code. We are going to start by coding the DAL. Open the DAL class file. We need to add namespaces that are not automatically available, let's import those namespaces and code for each Stored Procedure created earlier. The following is the code for the DAL class:

public class DAL
{
    String strcon = @"User id =SA;Password=MYNEWPASORWS;" + 
                    @"Server=SERVER1;Database=ValRollClients";
    SqlCommand cmdselect = null;
    SqlCommand cmdupdate = null;
    SqlCommand cmddelete = null;
    SqlConnection con;
    SqlDataAdapter da;

   /*=====================================================================
    //This Function is Being Called from the BLL, and it uses SP's, and   
    //Delete the Data
    ======================================================================*/

    public void Delete_Client(DataSet dsdata)
    {

        con = new SqlConnection(strcon);
        cmddelete = new SqlCommand();
        cmddelete.CommandText = "Delete_Record";
        cmddelete.CommandTimeout = 0;
        cmddelete.CommandType = CommandType.StoredProcedure;
        cmddelete.Connection = con;

        cmddelete.Parameters.Add("@Pro_id", 
                  SqlDbType.Int,4,"Pro_id");

        cmddelete.Parameters["@Pro_id"].Value = "Prop_Id";


        try
        {
            con.Open();

            cmddelete.ExecuteNonQuery();

            con.Close();
        }
        catch (SqlException)
        {
            throw;
        }
    }

    public DataSet Search_Client(String strClient)
    {
        DataSet ds = new DataSet();
        con = new SqlConnection(strcon);
        cmdselect  = new SqlCommand();
        cmdselect.CommandText = "SEARCH_CLIENT";
        cmdselect.CommandTimeout = 0;
        cmdselect.CommandType = CommandType.StoredProcedure;
        cmdselect.Connection = con;
        da = new SqlDataAdapter(cmdselect);
        cmdselect.Parameters.Add(new SqlParameter("@CLIENT_NAME",
                  SqlDbType.VarChar, 12,"Client_name"));
        cmdselect.Parameters["@CLIENT_NAME"].Value = strClient;

        try
        {
            con.Open();

            da.Fill(ds,"Products");    

            con.Close();
        }
        catch (SqlException)
        {   
            throw;
        }
         return ds;
    }

    public void  Update_Records(DataSet dsdata)
    {
        da = new SqlDataAdapter();
        con = new SqlConnection(strcon);
        //For Update
        cmdupdate = new SqlCommand();
        cmdupdate.CommandType = CommandType.StoredProcedure;
        cmdupdate.CommandText = "Update_Status_Grid";
        cmdupdate.CommandTimeout = 0;
        cmdupdate.Connection = con;
        //For Delete
        cmddelete = new SqlCommand();
        cmddelete.CommandText = "Delete_Record";
        cmddelete.CommandTimeout = 0;
        cmddelete.CommandType = CommandType.StoredProcedure;
        cmddelete.Connection = con;


        //Adding Parameters  for Update 
        cmdupdate.Parameters.Add("@Original_Pro_id", SqlDbType.Int, 4, "Pro_id");
        cmdupdate.Parameters["@Original_Pro_id"].SourceVersion = DataRowVersion.Original;
        cmdupdate.Parameters.Add("@Original_Delivered",SqlDbType.Int,4,"Delivered");
        cmdupdate.Parameters["@Original_Delivered"].SourceVersion = DataRowVersion.Original;
        cmdupdate.Parameters.Add("@Delivered", SqlDbType.Int, 4, "Delivered");

        //   Adding Parameters for Delete
        cmddelete.Parameters.Add("@Pro_id", SqlDbType.Int, 4, "Pro_id");

        cmddelete.Parameters["@Pro_id"].Value = "Prop_Id";

        //Telling the Adapter that we are going to use this Command Object for that

        da.UpdateCommand = cmdupdate;
        da.DeleteCommand = cmddelete;

        try
        {
            con.Open();
            da.Update(dsdata, "Products");
            con.Close();
        }
        catch (SqlException)
        {
            throw;
        }
    }
}

Our DAL class is complete. Let's look at our BLL. How is it going to communicate with the DAL and with the PL? 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 to the BLL project to the DAL project, and in the client project, we are going to add a reference to the BLL only.

BLL (Business Logic Layer)

In our BLL, we are going to handle all the business rules and error handlers. As you have seen, in our DAL class, we only trapped the exceptions but never displayed them. 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 constancy 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 the DAL. The first thing we are going do is to add a reference to the BLL to the DAL project, so that we can call the method Save. After you have done that, let's go and code our BLL. Our BLL will look like this:

public class BLL
{
    public DataSet Search_Client(String strClient)
    {
        DAL.DAL obj = new DAL.DAL();

        DataSet dsdata = new DataSet();
        try
        {
              dsdata = obj.Search_Client(strClient);

        }
        catch (SqlException e)
        {
            MessageBox.Show(e.Message,"Trapped in BLL");
        
        }
        return dsdata;
    }

    public void Update_Records(DataSet dsdata)
    {
    
        DAL.DAL obj = new DAL.DAL();

        try
        {
           obj.Update_Records(dsdata);

        }
        catch (SqlException e)
        {
            MessageBox.Show(e.Message, "Trapped in BLL");
          
        }
    }
}

Now our BLL is complete 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. We have to set the references so that we can use the functions and methods of our BLL. After we are done setting the reference, double click on the Search button and start coding.

BLL.BLL obj = new BLL.BLL();
String strsearch = txtsearch.Text.Trim();
try
{
    dsdata = obj.Search_Client(strsearch);

    if (dsdata.Tables[0].Rows.Count < 1)
    {
        MessageBox.Show("Record not Found");
    }
    else
    {
        dataGridView1.DataSource = dsdata;
        dataGridView1.DataMember = "Products";
    }
}
catch (ApplicationException ex)
{   
    MessageBox.Show(ex.Message);
}

After you are done, remember that there is an Update button, code it the following way:

BLL.BLL obj = new BLL.BLL();
            
try
{
    if (dsdata.HasChanges()) 
    {
        obj.Update_Records(dsdata);
        MessageBox.Show("Updated"); 
    }
    else
    {
        MessageBox.Show("No Changes Made");
    }
}
catch (ApplicationException ex)
{
    MessageBox.Show(ex.Message);
}

Now our Update button is going to do two things for us, Update and Delete. That means, if the Delivered field is being modified, then the Update command will be called to execute the update Stored Procedure, and if a user selects the record and presses Delete from the keyboard, the record will be gone. But when the user clicks on the Update button, the Adapter looks for the Delete command that we have created, and only then the record will be deleted from our table. Now, let us test our application. Press F5 in your keyboard, and you will see your PL (Presentation Layer); search for a name, or just Leave the textbox blank, and click the Search button. You will see all the records in the Join. First, you must start by testing the Update command.

Click on the Update button, before you do anything on the grid, and the event that has been trapped gets you a message that says:

Now, this comes from the following lines of code in our PL. That means, if there are no changes in the DataSet, a message to the user will be displayed that says there were no changes made to the DataSet since it was retrieved.

if (dsdata.HasChanges()) 
{
    obj.Update_Records(dsdata);
    MessageBox.Show("Updated"); 
}
else
{
    MessageBox.Show("No Changes Made");
}

From the above screen, scroll to your left and look for a field “Delivered”:

Change a digit in the first one and click Update, and you will receive a message that that says “Updated”. Search the grid again, and look at the field again.

To confirm that the record has been saved, without relying on our message that we displayed or the grid itself, you can run a Select * statement in your SQL database, and you will notice that the product we have deleted is gone:

As you can see, the product of 2002 is gone:

We did not use any wizard. Our code is not vulnerable to SQL injection. The only thing you should always practice is putting your connection string in the Settings file or the app.config file. Don’t hard code it. This will be helpful the next time you want to change the server name, or database name, or password; then, you won't need to compile the application again.

Conclusion

We have written this application without help from Wizards. In my next article (not Part III), an introduction to Remoting, we are going to host these tiers in different physical machines (computers). The tricky part is the communication of the layers across boundaries. I am going to explain how we can design our DAL and our BLL to communicate through remoting across different boundaries. Please note that we are going to continue with our tradition of N-Tiers when building applications; no matter how small they are, we will always N-Tier. I would like to thank you all for your support and e-mails. We will never stop learning, especially me. The next article will be my last for Windows development. After the Remoting article, I will be doing web articles.

Points of interest

After the Remoting article, I will be moving to web, as you can see all my examples so far have been done in WinForms, but they can still be applied to the Web. The same logic we use in Windows we can used in the Web too. The next article in web will be “Introduction to N-Tier ASP.NET AJAX 3.5”. Sounds cool. Well, my interests are in the design logic of the application, not the titivations of the application, meaning graphics for better user experience. That will come, but not now.

History

I would like to take a chance to thank everyone who wrote me e-mails about my articles, everyone who answered my coding questions when I got stuck. I am in Africa, and it feels good if people around the world think what am doing is good and appreciate it. I have been helped a lot in CodeProject, and I had helped many people. Maybe one day, I will wake up and decide to code for companies in other countries. But for now, South Africa is treating me well.