Use Data Access Application Block in .Net Database Driven Project

The .Net framework introduced ADO.NET class library, which is a great leap forward from the old ADO 2.5. Like many things in life, good things just keep coming one after the other. Microsoft simplified data access even further with application block, which is now in its 2.0 version, released June of 2003 (version 1.0 was released in 2002). In either traditional ADO or ADO.NET, data access has the same standard procedure for code statements. Without being too specific, here are the steps involved: 1. Declare and initialize a connection object, with the connection string set to the right database. 2. Declare and initialize a command object that uses the connection just declared. 3. Specify the CommandText and CommandType properties. 4. Open the connection mentioned in step 1. 5. Use one of the query methods of the command object mentioned in step 2. 6. Close the connection. If you are using dataset, you need an additional SqlDataAdapter to talk to the dataset. That's another line of code. Microsoft data access block recognizes this pattern and molds the steps into an "application block," or in other words, some one liners, to achieve the same results that were done by the 6 steps mentioned above. You can use the application block whenever you need to connect to the database and do something. The figure below shows what the application block does graphically. Let's take a look at the main class and its basic methods. The source code is available from Microsoft Download Center. There is actually just two classes, called SQLHelper and SqlHelperParameterCache, in one source file called SQLHelper.cs (or .vb). You can compile the source code and then reference it in your own projects. We will concentrate on the SQLHelper class functionalities in this article. Methods are all overloaded with different signatures. Here are the most popular ones: SQLHelper.ExecuteNonQuery SQLHelper.ExecuteDataSet SQLHelper.ExecuteReader SQLHelper.ExecuteScalar SQLHelper.ExecuteXmlReader The methods functionalities are almost self-explanatory just by looking at their names. Here I will highlight the use of one method: ExecuteDataSet. It has 9 overloaded methods. The basic parameters needed are connection, command type, command text, and parameters. Here is a simple walk-through using this method to retrieve data from the sample Northwind database on SQL Server. Create a new C# Windows application project (similar for ASP web application). Drop a button and a datagrid control on the Windows form, like shown below. Then add the event handler for the button as follows, which makes use of the SQLHelper's ExecuteDataSet method. private void button1_Click(object sender, System.EventArgs e) { System.Data.DataSet myset = new DataSet(); //declare a dataset object string conn_string = "server =ServerInstanceName;database=northwind;Integrated Security=SSPI"; myset = SqlHelper.ExecuteDataset(conn_string, System.Data.CommandType.Text, "Select * from Categories"); //retrieve dataset using the application block dataGrid1.DataSource= myset.Tables[0].DefaultView; //display data table using datagrid } If we do it the traditional way, we would write: private void button2_Click(object sender, System.EventArgs e) { System.Data.DataSet myset = new DataSet(); string conn_string = "server =ServerInstanceName;database=northwind;Integrated Security=SSPI"; System.Data.SqlClient.SqlConnection myconn = new System.Data.SqlClient.SqlConnection(conn_string); System.Data.SqlClient.SqlCommand mycommand = new System.Data.SqlClient.SqlCommand("select * from Categories", myconn); System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter(mycommand); myAdapter.Fill(myset); dataGrid1.DataSource= myset.Tables[0].DefaultView; } Clearly one line of application block code is equivalent to 4 lines of conventional code. If you already know how to use the SqlCommand, SqlConnection, and SqlDataAdapter classes, then this application block can speed up your development, so that you don't have to copy and paste a lot of the same codes when doing data access. For a full documentation on the data access application block, please see the documents that come with the source code. There are about 1400 lines of code in the C# source code, so do not reinvent the wheel; just go ahead and use it.