To read the article online, visit http://www.4GuysFromRolla.com/articles/070203-1.aspx

Examining the Data Access Application Block

By John Jakovich


A More Recent Version of the Data Access Application Block is Available
This article highlights using the Data Access Application Block version 2.0. In January 2005 Microsoft consolidated their applications, updated them, and repackaged them as The Enterprise Library. For more information on the Enterprise Library's version of the DAAB, check out Working with the Enterprise Library's Data Access Application Block.

Introduction


In an earlier article of mine, An Introduction and Overview of the Microsoft Application Blocks, we outlined the purpose and benefits of Microsoft Application Blocks. Application Blocks encapsulate two common patterns encountered in application programming, data access and exception management. Application Blocks provide value by making our programming more efficient and our programs more maintainable. They speed the application development process by "wrapping" common tasks. In this article we will examine code samples that illustrate the advantages of using the Data Access Application Block.

Recall that in the first article, we outlined the steps to install Application Blocks on your system. For completeness, let's quickly review the basic procedure:

  1. Download the Application Block installer package and install the Application Block (the URL for the Data Access Application Block is http://download.microsoft.com/download/VisualStudioNET/daabref/RTM/NT5/EN-US/DataAccessApplicationBlock.msi),
  2. Navigate to the "Microsoft Application Blocks for .NET" item from Start Menu -> Programs Files and select the language of your choice (C# or VB).
  3. The Application Block Project will open in Visual Studio. Build the project.
  4. Once the assembly has been built, add a reference to Microsoft.ApplicationBlocks.Data.dll to your project and add using (C#) or Imports (VB) statements for the Microsoft.ApplicationBlocks.Data, System.Data, and System.Data.SqlClient namespaces.
That's it! Once you've followed these steps you should be ready to begin using the Data Access Application Block.

Using the Data Access Application Block to Execute SQL Statements


Once you have the references set and the correct using or Imports statements in your class files, you will have access to the Data Access Application Blocks SqlHelper class. The SqlHelper class contains static methods that facilitate the execution of common data access tasks, including:

  • Calling stored procedures or SQL text commands,
  • Specifying parameter details, and
  • Returning SqlDataReader, DataSet, XmlReader objects, or single values.

In order to illustrate the advantage of using the Data Access Block, let's take a look at sample code that creates a SqlDataReader object and binds it to a DataGrid without using the Data Access Block. In general, returning a DataReader involves establishing a connection, creating a SqlCommand, and executing the command against the database. The resulting SqlDataReader object can then be bound to a DataGrid:

//create the connection string  and sql to be executed 
string strConnTxt = "Server=(local);Database=Northwind;Integrated Security=True;";
string strSql = "select * from products where categoryid = 1";
				
//create and open the connection object
SqlConnection objConn = new SqlConnection(strConnTxt);
objConn.Open();
	
//Create the command object
SqlCommand objCmd = new SqlCommand(strSql, objConn);
objCmd.CommandType = CommandType.Text;
	
//databind the datagrid by calling the ExecuteReader() method
DataGrid1.DataSource = objCmd.ExecuteReader();
DataGrid1.DataBind();
	
//close the connection
objConn.Close();

Now lets look at the same task using the SqlHelper class's static ExecuteReader() method:

	
//create the connection string and sql to be executed
string strSql = "select * from products where categoryid = 1";
string strConnTxt = "Server=(local);Database=Northwind;Integrated Security=True;";
	
DataGrid4.DataSource = SqlHelper.ExecuteReader(strConnTxt, CommandType.Text, strSql);
DataGrid4.DataBind();

As you can see, there is considerably less code in the second example. To execute a SQL statement and return a SqlDataReader, the ExecuteReader() method requires only the connection string, command type and SQL to be executed. The SqlHelper class contains all of the "plumbing" necessary to establish a connection, create a SqlCommand and execute the command against the database with a single static method call.

The main advantage of the Application Blocks is that they greatly reduce the amount of code you need to write by encapsulating common tasks in a wrapper class. While at first glance this may not seem that profound of a benefit, realize that writing less code means more than just shorter time needed to write the code. It also means fewer bugs and typos, and an overall lower total cost to produce the software. As Scott Mitchell shared in an earlier article of his (The Low-Down on #includes):

Axiom 1, of Scott's Axioms of Programming:


Writing more code leads to more bugs, which leads to a longer development cycle, which leads to higher costs. Therefore, a minimalistic approach should be taken to writing code.

Using the Data Access Application Block to Execute Stored Procedures


The ExecuteReader() method also has several overloads that enable you to perform stored procedures and transactions. Lets take a quick look at the same method, but this time we'll execute a stored procedure:

	
DataGrid5.DataSource = SqlHelper.ExecuteReader(strConnTxt, CommandType.StoredProcedure, 
                            "getProductsByCategory", new SqlParameter("@CategoryID", 1));
DataGrid5.DataBind();

To execute a stored procedure and return a SqlDataReader, we call the same static ExecuteReader() method but this time the function signature is different. Having a single function name with many different parameter forms to call it is known as method overloading. Overloading enables us to use the same SqlHelper class method but pass different parameters. To call a stored procedure, instead of passing the SQL statement, I send the name of the stored procedure and a SqlParameter object.

It's important to note that there's nothing magical about the ExecuteReader() method (or any other method in the Data Access Application Block). All Microsoft has done is create a wrapper that removes us from the details of creating the objects required to return a SqlDataReader. If you examine the Application Block's code, (which I encourage you to do so), you would see the same familiar objects we have used for database access in the past. Now, we simply don't have to worry about creating and maintaining them.

Lets take a look at a more dramatic example of the benefits of using the Data Access Application Block. For this example, we will retrieve a DataSet containing the results from a stored procedure (getProductsByCategory) that takes a single parameter (CategoryID). Again, to illustrate the amount of code saved by using the Data Access Application Block, let's first look at the necessary code when not using the Data Access Block:

	
// Open a connection to Northwind
SqlConnection objConn = new 
    SqlConnection("Server=(local);Database=Northwind;Integrated Security=True;");
objConn.Open();
		
//Create the stored procedure command object
SqlCommand objCmd = new SqlCommand("getProductsByCategory", objConn);
objCmd.CommandType = CommandType.StoredProcedure;

//create the parameter object for the stored procedure parameter
objCmd.Parameters.Add("@CategoryID", SqlDbType.Int); 
objCmd.Parameters["@CategoryID"].Value = 1; 
		
//create our DataAdapter and DataSet objects
SqlDataAdapter objDA = new SqlDataAdapter(objCmd);
DataSet objDS = new DataSet("Category_Results");
		
//fill the dataset
objDA.Fill(objDS);
		
//databind the datagrid
DataGrid1.DataSource = objDS;
DataGrid1.DataBind();
		
//close connection
objConn.Close();

Now, we will call the same stored procedure and return a DataSet using the SqlHelper class's ExecuteDataset() method:

string strConn = "Server=(local);Database=Northwind;Integrated Security=True;";
DataSet objDS = SqlHelper.ExecuteDataset(strConn, CommandType.StoredProcedure, 
                   "getProductsByCategory", new SqlParameter("@CategoryID", 1) );
			
DataGrid2.DataSource = objDS;
DataGrid2.DataBind();

Through the usage of the Application Block, we reduced the lines of code from 12 to 4! Once again, the reason for the simplified code is that the ExecuteDataset() method has all the necessary logic to create and manage the required objects to execute the stored procedure and return the DataSet.

Conclusion


Using the Data Access Application Block will speed your application development by encapsulating the code required to execute stored procedures or SQL text commands, specify parameters and return SqlDataReader, DataSet, XmlReader objects. Because the Data Access Block was developed using the latest best practices, you can be confident that your code will be efficient and scalable.

Using the Data Access Application Block frees us from the tedious details of data access and allows us to concentrate on the important business logic in our applications. It also reduces the amount of code we must write, which leads not only to quicker development times, but reduced errors, bugs, and typos as well.

Happy Programming!

  • By John Jakovich

    A DAAB Presentation
    In October 2003 I gave a presentation on the Data Access Application Block to the San Diego ASP.NET SIG. You can download the presentation if you like. Enjoy!
    -- Scott Mitchell

  • Article Information
    Article Title: ASP.NET.Examining the Data Access Application Block
    Article Author: John Jakovich
    Published Date: July 2, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/070203-1.aspx


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers