Data Access with Microsoft Application Blocks

ADO.NET SQL Data access made simple and efficient.

Microsoft has created a set of libraries known as Application Blocks. These libraries will help developers reduce the amount of code they must write while using the current best practices. One of the components, Data Access Application Block for .NET, addresses Microsoft SQL Server data access by wrapping up data access into a helper class.

When Microsoft introduced .NET, they introduced a new data access architecture called ADO.NET. This new data access architecture is the next evolution from COM-based ADO. ADO.NET provides a lot of new features for accessing data such as datasets, which are essentially an in-memory database, and data adapters that allow different data providers to interact with a DataSet. This model allows datasets to be completely data provider-independent giving you the option of changing the data provider used to fill a DataSet and update data from a DataSet. You can even use multiple data providers with a single DataSet?fancy that! Of course you will find some classes such as a Connection class, Parameter class, and Command class that perform the same basic function as their ADO predecessor. With ADO.NET's new features and enhancements, of course, comes extra complexity when you write your data access code; you never had to deal with a data adapter in the past. Wouldn't you like someone to encapsulate all that complexity into a series of classes and methods to handle 95% of your data access logic needs? You're in luck.

Microsoft has introduced the Patterns & Practices Web site. (http://www.microsoft.com/resources/practices/), which contains Microsoft's suggested practices and design patterns guidelines for designing, developing, deploying, and managing the life cycle of software solutions. The Patterns & Practices Web site contains the following four types of methods for sharing these guidelines: (design) Patterns, Reference Architectures, Reference Building Blocks and IT Services, and Lifecycle Practices. Together these guidelines provide far better guidance for developing strong solutions compared to simple white papers or books alone.

The Reference Building Blocks and IT Services guidelines provide designs and components for building reusable sub-systems in your software solutions. The components of this guideline are referred to as Application Blocks. Table 1 lists the eleven Application Block components that were available at the time this article was written.

One of the Reference Building Blocks that the Microsoft practices & patterns team has introduced is a data access helper framework called the Data Access Application Block for .NET, which you can download from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp. This data access helper framework provides a series of static methods for simplifying CRUD (Create, Read, Update, and Delete) operations. It contains two main classes: the SqlHelper class, and the SqlHelperParameterCache class. The SqlHelper class provides for simplified data access to a SQL server database and the SqlHelperParameterCache class caches SqlParameters for a given command as well as connection so that you can reuse them at any given time. Together these classes encapsulate the code you will need to perform most of your routine data access logic.

SqlHelper Class

The SqlHelper class simplifies the task of writing data access by wrapping up the data access logic for you. You just have to call a single method with a few parameters, and voila! you have an instant DataSet, DataReader etc. This class is very well thought out as it contains methods for returning DataSets, DataReaders, XmlReaders, scalar values, and the number of rows affected for non-query commands. It also has specialized methods for performing database updates from a DataSet, and a method for populating a pre-defined DataSet such as a typed DataSet. The methods fall into two major categories: those that accept parameters from a DataRow object, and those that don't. The two exceptions to the categories are the CreateCommand method and UpdateDataset method. In addition, the FillDataSet method that populates the DataSet that is passed in does not have a DataRow parameter implementation. You can easily tell which methods use the DataRow object for passing in parameters by the "TypedParams" at the end of the method name. Table 2 has a list of SqlHelper class methods.

All methods except for the UpdateDataset method require some type of connection or transaction to be specified by passing in a SqlConnection object, SqlTransaction object, or connection string. Note that the CreateCommand method only supports a SqlConnection object. Also, any method returning an XmlReader object cannot support a connection string parameter since the .NET implementation of the ExecuteXmlReader method of the SqlCommand class does not support a CommandBehavior parameter. All methods also require, except again for the UpdateDataset method, a command string. In addition, depending on the overload or method you use, you may also be required to specify a CommandType parameter of StoredProcedure or Text. (You cannot use a TableDirect CommandType because it is only supported by the .NET Framework Provider for OLE DB).

All methods except for CreateCommand, UpdateDataset, and DataRow ("TypedParams") support overloads for running a stored procedure or in-line text without specifying parameters. However, if you want to specify parameters for a SqlCommand you have three options:

you can specify an array of SqlParameter objects
you can specify an array of objects
if you're using one the "TypedParams" methods you can specify a DataRow object.

One important thing to be aware of when you use the DataRow parameter method or the Object array parameter overload is that they both utilize the SqlHelperParameterCache class to retrieve and cache the SqlParameter array for future use. Your first use of these methods will take a slight performance hit while the SqlHelperParameterCache retrieves the SqlParameter from the stored procedure. Subsequent calls then simply pull the SqlParameter array from the cache. You should note a few other important things about using the Object array parameter overloads.

Only use the Object array to pass in parameters if you don't plan to use any output parameters.
The Object array parameter overload only works with stored procedures.
You must put the Object array parameters in the same order as they are defined in the stored procedure.

Also, one important thing to note about using the DataRow parameter methods is that the Column name in the DataRow must match the corresponding SQL parameter name without the "@". This means that if you had a DataRow column named "AccountId," then in order for you to pass it as a parameter to your SQL command you must have a input parameter defined with the name "@AccountId" and its data type must be the same as the DataRow column.

When you specify the SqlParameter arrays or object arrays as parameters you do not have to pass them to the SqlHelper method as an array. Instead you can specify a variable list of arguments that will convert to an array method parameter. Look at the example below.

public DataSet GetMyData(string myConnection,
   string myCommand, string myParam1,
   int myParam2)
{
   return SqlHelper.ExecuteDataSet(myConnection,
      myCommand, myParam1, mParam2);
}

The example shows a command string, a connection string, and two parameters of different types being passed into the ExecuteDataSet method of the SqlHelper class. I could have specified any number of parameters, all of which could be of different data types, because this particular overload accepts the array of parameters as object types. This comes in very handy for writing quick and dirty data access code.

SqlHelperParameterCache Class

The SqlHelperParameterCache class is used in conjunction with the SqlHelper class to support the caching of SqlParameters, thus allowing you to reuse parameters without having to recreate them every time you need to run an SQL command. This parameter cache class uses a hash table to store parameter caches based on connection string and command text. You can set up a parameter cache in one of two ways: you can specify an array of SqlParameters yourself or you can specify a stored procedure and connection string, and it will build an array of SqlParameter objects using the SqlCommandBuilder class. To retrieve the SqlParameter array just use the same connection string and command text you used to store the array to retrieve it.

Table 3 describes the methods available for the SqlHelperParameterCache class. There are some important things you should know about when using this class. First, the GetSpParameterSet will only work with stored procedures. Second, in order to retrieve the SqlParameter array the connection string must be syntactically and semantically correct, capitalization and all. Finally, remember that whenever you retrieve a SqlParameter array it is actually a clone of what is in the cache. You can use this to your advantage by presetting SqlParameter values and storing those preset SqlParameter objects in the cache. One last thing to note is that if you use the GetSpParameterSet more than one time for a particular connection string and command string, the method will try to locate the SqlParameters in cache first before attempting to utilize the SqlCommandBuilder class to determine the list of parameters from the stored procedure. If the GetSpParameterSet has to retrieve the parameters via the SqlCommandBuilder class, it will store them into the cache so that they can be used again later.

Working with the Data Access Application Block

Together the SqlHelper class and the SqlHelperParameterCache class create a robust package for data access. Unfortunately, very rarely does one size fit all and the Data Access Application Block is no exception. It will handle most of what you need but you still may have to do some data access code by hand. For example, if you know your query may take more than 15 seconds to run, you probably don't want to use the SqlHelper class. You cannot specify a command timeout so you are stuck with the SqlCommand's default of 20 seconds. Now of course since you have the source code there is nothing to stop you from overloading the existing methods and including a timeout parameter, but if there is to be a version 3.0 you may have to implement your code again. Another area that you may have to get around is that since the SqlHelperParameterCache returns a set of SqlParameters as an array, there currently is no way to access the parameters by name, only by indexer. However, Listing 1 demonstrates a few lines of code you can use to get around that minor issue as well.

Listing 2 illustrates how to use the Data Access Application Blocks. The main public method, "GetSalesReport," will call two private methods, "GetSalesByYear" and "GetSalesByCategory" to populate a DataSet with two DataTables.

In the "GetSalesByYear" method I used the ExecuteDataSet method of the SqlHelper class to return my DataSet as well as the GetSpParameterSet method of the SqlHelperParameterCache class to retrieve and cache an array of SqlParameter objects. I created a string variable and set it to what my SQL command text will be. I used this command string to create a cache of SqlParameters to pass to the ExecuteDataSet method. Next I set up the SqlParameter objects so I can pass some parameters to the stored procedure. Instead of creating the SqlParameters by hand, I use the GetSpParameterSet method to get the SqlParameter list for me. I simply pass in the Sqlcommand string and the SqlConnectionString via the conn.ConnectionString property. As I mentioned before, this method will attempt to find an array of SqlParameter objects for the given command and connection string, clone that array, and return the cloned SqlParameter array. Otherwise it will use the SqlCommandBuilder to retrieve the parameters from the database, cache the SqlParameters, clone the SqlParameter array, and return the cloned array. Next I need to specify the values for my cloned copy of the SqlParameter array. This can pose a problem if the order of the parameters in the stored procedure changes. You'll have to live with this limitation unless you create a method for retrieving SqlParameter objects by name like in Listing 1. Finally I call the ExecuteDataSet method with the connection object, command type, command string, and SqlParameter array. Voila!

Now in the second method, "GetSalesByCategory," I use a different approach. I pass in the DataSet and use the FillDataSet method of the SqlHelper class to populate it. Note that I am not using the SqlHelperParameterCache. Well, at least not directly. Remember that the method overloads that take an array of objects use the SqlHelperParameterCache behind the scenes to figure out the details for each SQL parameter. The same rule about the order of the parameters in the stored procedures applies here. If the order in the stored procedure changes then you need to modify your code to match that order. Otherwise your procedure will be called with incorrect parameters resulting in an exception or garbage data being passed to your procedure. Another thing to remember is that this particular overload only works with input SqlParameters, so you cannot retrieve output parameters. And you cannot pass in optional parameters. Finally, I want to point out that I did not pass the values as an array. I use the C# params keyword for the object array, allowing me to specify my values by simply listing them out. (The VB.NET equivalent of this is the ParamArray keyword.)

Walking through the "GetSalesByCategory" method, I set a string variable equal to the SQLcommand string I intend to use. Next I created a string array of table names that the FillDataset method uses to map the resultset(s) returned by the SQLcommand to specific table names. (This is really useful with typed DataSets to fill the typed DataTables). Finally, I simply call the FillDataSet method with the SqlCommand object, command string, and the variables to be passed into the specified SQL command. As you can see, using this specific overload can result in a lot less coding, especially when you need to pass in a lot of parameters to an SQL Command.

Summary

The Data Access Application Block can save a lot of time by allowing you to specify a few parameters and presto, you're done. However, with simplification comes reduced flexibility and control. Using Data Access Application Block may force you to make a few concessions, especially if you have queries that run a long time and you need to get at parameters by name out of the SqlParameter array. One nice thing about the Data Access Application Block is that you get the source code with the download, so with a few minor tweaks, this Application Block will meet most if not all of your data access logic needs.

Application Block	Description
Aggregation Application Block	This Application Block contains a component that sends requests to a series of dispatchers to return data from various service providers in a single data entity such as a dataset or XML document.
Asynchronous Invocation Application Block	This Application Block manages asynchronous communication between an application and service providers.
Authorization and Profile Application Block	This Application Block gives you the ability to manage role-based authorization and profile information in your applications.
Caching Application Block	This Application Block provides an infrastructure to cache data from service providers such as a Web service or database.
Configuration Management Application Block	This Application Block provides a simple and consistent interface for reading and writing application configuration data.
Data Access Application Block	This Application Block contains a component that has functionality to simplify the most common methods for accessing Microsoft SQL Server database servers via the .NET SqlClient class.
Exception Management Application Block	This Application Block provides a framework for logging exception information and publishing to the event log, or you can create your own custom publisher.
Logging Application Block	This Application Block uses the Enterprise Instrumentation Framework (EIF) to assist in the development of instrumented applications.
Smart Client Offline Application Block	This Application Block provides an infrastructure to develop applications with offline capabilities.
Updater Application Block	This Application Block helps you recognize, validate, and download updated application files from a central location.
User Interface Process Application Block	This Application Block provides a series of components that assist the developer in creating UI process components.

Method(s)	Returns	Purpose
ExecuteNonQuery,ExecuteNonQueryTypedParams	void
ExecuteDataSet,ExecuteDataSetTypedParams	DataSet	Executes the SqlDataAdapter's Fill method to return an untyped DataSet.
ExecuteReader,ExecuteReaderTypedParams	SqlDataReader	Executes the SqlCommand ExecuteReader method to return a SqlDataReader.
ExecuteScalar,ExecuteScalarTypedParams	object	Executes the SqlCommand ExecuteScalar method to return a single object.
ExecuteXmlReader,ExecuteXmlReaderTypedParams	XmlReader	Executes the SqlCommand ExecuteXmlReader method to return an XmlReader.
FillDataSet	void	Executes the SqlDataAdapter's Fill method to fill the DataSet specified in the parameter list of the method.
UpdateDataSet	void	Executes the SqlDataAdapter's Update method to update the table specified in the parameter list.
CreateCommand	SqlCommand	Returns a SqlCommand Object to be used for the UpdateDataSet method.

Method(s)	Returns	Purpose
CacheParameterSet	void	(string connectionString, string commandString, SqlParameter[] ParamArray)
GetCachedParameterSet	SqlParameter[]	(string connectionString, string commandString)
GetSpParameterSet	SqlParameter[]	(string connectionString, string spName, bool includeReturnValueParameter)

Data Access with Microsoft Application Blocks

Published in:

Filed under:

SqlHelper Class

SqlHelperParameterCache Class

Working with the Data Access Application Block

Summary

Listing 1: SqlParameter Helper method example

Listing 2: Data Access Application Block Northwind example

Table 1: Current Application Block components available at the time this article was written.

Table 2: SqlHelper class method definitions.

Table 3: SqlHelperParameterCache class method definitions.

This article was filed under:

This article was published in:

Have additional technical questions?