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:
Navigate to the "Microsoft Application Blocks for .NET" item from Start Menu -> Programs Files and
select the language of your choice (C# or VB).
The Application Block Project will open in Visual Studio. Build the project.
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:
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.
