Introduction:
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. 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.
string connectionString = (string)
ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("INSERT_PERSON",connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar,50));
command.Parameters["@Name"].Value = txtName.Text;
command.Parameters.Add(new SqlParameter("@Age",SqlDbType.NVarChar,10));
command.Parameters["@Age"].Value = txtAge.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
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.
using Microsoft.ApplicationBlocks.Data;
SqlHelper.ExecuteNonQuery(connection,"INSERT_PERSON",new
SqlParameter("@Name",txtName.Text) ,new SqlParameter("@Age",txtAge.Text) );
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.
using Microsoft.ApplicationBlocks.Data; SqlDataReader dr = SqlHelper.ExecuteReader(connection,CommandType.StoredProcedure,"SELECT_PERSON"); STORED PROCEDURE SELECT_PERSON: SELECT * FROM tblPerson;
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.
// Selects all the rows from the database
DataSet ds = SqlHelper.ExecuteDataset(connection,CommandType.StoredProcedure,"SELECT_DATA");
// Sends the parameters and returns the dataset
DataSet ds = SqlHelper.ExecuteDataset(connection,CommandType.StoredProcedure,new SqlParameter("@Name",txtName.Text), new SqlParameter("@Age",txtAge.Text));
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:
CREATE PROCEDURE getPersonDetails @PersonID int, @Name nvarchar(40) OUTPUT, @Age nvarchar(40) OUTPUT AS SELECT @Name = Name, @Age = Age FROM tblPerson WHERE PersonID = @PersonID
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.
SqlParameter [] arParms = new SqlParameter[3];
// @PersonID Input Parameter
arParms[0] = new SqlParameter("@PersonID", SqlDbType.Int );
arParms[0].Value = personID;
// @ProductName Output Parameter
arParms[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 40);
arParms[1].Direction = ParameterDirection.Output;
// @UnitPrice Output Parameter
arParms[2] = new SqlParameter("@Age", SqlDbType.NVarChar,40);
arParms[2].Direction = ParameterDirection.Output;
// Execute the stored procedure
SqlHelper.ExecuteNonQuery( connectionString, CommandType.StoredProcedure, "getPersonDetails", arParms);
// create a string array of return values and assign values returned from stored procedure
string [] arReturnParms = new string[2];
arReturnParms[0] = arParms[1].Value.ToString();
arReturnParms[1] = arParms[2].Value.ToString();
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.
SqlConnection conn = new SqlConnection(connectionString); conn.Open(); // Call ExecuteXmlReader static method of SqlHelper class that returns an XmlReader // We pass in an open database connection object, command type, and command text XmlReader xreader = SqlHelper.ExecuteXmlReader(conn, CommandType.Text, "SELECT * FROM Products FOR XML AUTO" ); return xreader;
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 !
Attachments:
Project File: MicrosoftAppBlock_azam.zip