Skip Navigation LinksHome > Categories > Code from a Category

Microsoft Data Access Application Block


User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 85
Add Date: 01/08/2012
Microsoft .net framework consists of ADO .NET which enables the developer to interact with the database. ADO .NET provides many rich features that can be used to retrieve and display data in a number of ways.


Introduction:

Apart from the flexibility provided by the ADO .NET, sometimes we find ourselves repeating the same code again and again. Consider that at some point in our application we need to pass some parameters and retrieve some information from the database. We can perform this task by writing 5-6 lines of code which is cool. But when later we need to pass the parameters we have to write those 5-6 lines again which is not cool.

For this reason Microsoft introduced Data Access Application Block which can be used to perform common task in with less code. Another good reason to use the Microsoft .net data access application block is that it makes the application consistent meaning that if different companies are using the Data Access Block than you will find the code more easier to understand. You can download Microsoft .net Data Access Application Block from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp. Download the application Block and simple install it. Lets see how we can use it. Using Microsoft .net Data Access Application Block:

In this article I have used Microsoft .NET Data Access Application Block Version 2.0. Once you download and installed the application block you need to refer it in your application. For this first you need to create the .dll file for the Microsoft .net Data Access Application Block project. You can simple add the Data Access Application Block project in your current project and build the solution (This can also be performed using the command line tools). Once the solution is build the .dll file is created under the bin directory. Delete the Data Access Project from your current project as you only needed it to create the .dll. And now add a reference in your project which will refer to Microsoft.ApplicationBlock.Data. Once you have made the reference you are ready to use the Application Block in your project.

Accessing data without Data Access Application Block:

Lets first see how we can access data without using the Application Block so , that we can compare the flexibility of both the approaches. Below is a simple example that inserts two parameters into the database. You can note that as the number of parameters increases the lines of code also increases.


As you can see that we had to write a lot of code to insert the parameters into the database. Now suppose that you have 10 parameters instead of two it would take you 1 hour to write a simple Insert module. Lets see how you can accomplish this using the Microsoft .net Data Access Application Block.



As you see in the above code we perform the whole operation in a single line instead of writing 5-6 lines. First of all you should always add the namespace Microsoft.ApplicationBlocks.Data without using the namespace you will not be able to use the Application Block. The next interesting thing that you might note is the SqlHelper class. The SqlHelper class is developed by Microsoft developers which contains the static methods to access the database. You can view the SqlHelper class by opening it in any word editor.

Lets see some more features of the Microsoft Data Access Application Block. Retrieving Multiple Rows using SqlDataReader and Data Access Application Block:

Consider a situation that you need to retrieve multiple rows from the database. This retrieval can be for only displaying purposes and you want this task to be completed very fast. Since you only need to display the rows and you need it very fast your best bet is to use SqlDataReader since its a forward only reader. Lets see how you can use SqlDataReader to get the rows you wanted in an efficient and quick manner.



As you can see executing the reader is pretty simple. All you have to do is pass few parameters and that's it and it will return the datareader object which you can use to bind to the datagrid. Also remember that Execute Reader method of the SqlHelper class has several overloads which you can use according to your needs. You can pass parameters or simple execute a simple procedure like I did.

I have also shown the stored procedure which simple selects all the rows from the tblPerson and returns them.
Retrieving Multiple Rows using DataSet:
You can also use a dataset to retrieve multiple rows. The question that comes to your mind right now should be that when should you use DataReader and when you should use DataSet. You should use DataReader when your sole purpose is to display the data to the end user. Since datareader is a forward only reader its very fast in reading the records. SqlDataAdapter also uses SqlDataReader when reading records from the database.

If on the other hand you want are in a distributed environment and want your data to travel between different layers than using a DataSet will be a better choice.


In the above code you can see that I showed two ways of using ExecuteDataSet method. The method has many overloads that you can use to satisfy your needs. The Execute DataSet method will be the most commonly used method you will use in your application. Retrieving a Single Row

Sometimes you have a need to retrieve a single row instead of group of rows. Whenever you need to retrieve a single row you will have to change your stored procedure. I am not saying that this is the only way to referring to a single row since you can retrieve all the rows into a dataset and than pick the row you like. I am talking about retrieving a single row from the database. Lets see what you need to do in your stored procedure to get one row out of it. Stored Proc:



As you see in the stored procedure that we have marked the parameters with OUTPUT which means that when the execution of stored procedure is completed those parameters with OUTPUT keyword will be returned to the caller. Lets now see the C# code of how we can use to retrieve a single row.

Explanation of the code:
1) First we made the array of type SqlParameters. The reason that we made the array is because we need to pass several parameters.
2) Then we assign parameter whose index is '0' to the parameter that we like to send which, in this case is PersonID.
3) We did not specify the direction of the Parameter. If the direction is not specified the parameter is considered to be an input parameter by default.
4) Later we defined parameter Age which is on index '1' in the parameter array.
5) This time we specified the parameter direction since we want the parameter value to be returned when the sql query completes.
6) Next we use the ExecuteNonQuery method of the SqlHelper class to execute the query.
7) Finally, we retrieved the values from the parameters. Values are retrieved since parameters are marked with direction output and also that in the stored procedure we have marked the variables as OUTPUT variables which will be returned to the caller program.

You can perform the same operations in a number of ways. I like it this way since its more clear. As you can see you first created an array of Parameters. After the array is created you simply assigns the value and also informs the C# compiler that which one of the parameters are OUTPUT.

Retrieving XML Data:

You can not only retrieve data from the database but also from any XML File. Lets see a small code sample which shows this operation.


Explanation of the Code:

1) First we made the simple Sql Connection
2) The ExecuteXmlReader method is responsible for fetching the xml from the table. FOR XML is a keyword that is used to retrieve Xml fragments.
3) And finally we returned the XML reader which is populated with the result that came from the ExecuteXmlReader method of the SqlHelper class.
I hope you enjoyed the Tutorial. Happy Coding !

Post a Comment
Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below: 		(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.