Data Access with Microsoft Application Blocks

icrosoft recently launched the Microsoft Patterns and Practices section of their Web site, offering several complete architectural and design application blocks, complete with source code, that you can use in your .NET applications.

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, 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.

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

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.

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. 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.

The 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.

Table 2: SqlHelper class method definitions.

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.

All methods except for UpdateDataset 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 supports only a SqlConnection object. Also, any method returning an XmlReader object cannot support a connection string parameter because the .NET implementation of the ExecuteXmlReader method of the SqlCommand class does not support a CommandBehavior parameter. All methods also require a command string (again except for the UpdateDataset method). 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 that’s supported only 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,” 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.

Table 3:SqlHelperParameterCache class method definitions.

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)

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, because you have the source code there’s nothing to stop you from overloading the existing methods and including a timeout parameter?but if a version 3.0 appears, 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 calls 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 my SQL command text. 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.

The Data Access Application Block can save you a lot of time. You simply 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 access parameters by name from 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.

CoDe Magazine has an article on the Exception Management Application Block. This framework provides a simple, robust way to manage and publish application exceptions.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job