ToolKit
LANGUAGES: C# | VB.NET
ASP.NET VERSIONS: ALL
The Microsoft Data Access Application Block
Simplify Your SQL Server Data Access Logic
By Ken McNamee
Admittedly, the Microsoft Data Access Application Block (DAAB; http://msdn.microsoft.com/library/en-us/dnpag2/html/daab.asp) is not a tool in the same sense that you are accustomed to me writing about in this column. DAAB has no flashy GUI interface. It doesn t even run from the command line. It is simply an API a helper file that encapsulates and simplifies the standard, boilerplate ADO.NET code that you ve probably written over and over hundreds of times in the past few years. So, in that sense, a development tool is merely something that helps make your job as a developer easier, less error-prone, and less tedious. DAAB certainly qualifies in all those respects.
Longhand Data Access
If you ve been writing database-driven ASP.NET Web sites and haven t been using DAAB, then the example shown in Figure 1 should look very familiar to you. This is data access in longhand. You must create all the ADO.NET objects, such as the connection to the database, the command that specifies the stored procedure to call, and the parameters to pass into the stored procedure. You also have to create the data adapter that manages making the call to the database and filling the DataSet object with the returned resultset.
public static DataSet GetOrderDetails(int orderID)
{
SqlConnection conn = new SqlConnection(m_connString);
SqlCommand cmd = new SqlCommand("CustOrdersDetail",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@OrderID",orderID));
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
try
{
conn.Open();
da.Fill(dataSet);
}
catch (Exception ex)
{
//log exception...
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
return dataSet;
}
Figure 1A: Data Access the plain ADO.NET way.
Public Shared Function GetOrderDetails(
ByVal orderID As Integer) As DataSet
Dim conn As New SqlConnection(m_connString)
Dim cmd As New SqlCommand("CustOrdersDetail",conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(new SqlParameter("@OrderID",orderID))
Dim da As New SqlDataAdapter(cmd)
Dim dataSet As New DataSet()
Try
conn.Open()
da.Fill(dataSet)
Catch ex As Exception
'log exception
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Try
Return dataSet
End Function
Figure 1B: The VB.NET version.
The Simpler Life
As you can see from Figure 1, there is very little code in there that is specific to that particular database call. Aside from the name of the stored procedure and the one parameter, most of the code simply manages the connection to the database and the process of calling the stored procedure. If you don t take a shortcut, you ll find yourself writing this code over and over again for every call you make to the database. DAAB is simply the shortcut you need to greatly simplify this process. Take, for instance, the code shown in Figure 2. This is a rewrite of the code shown in Figure 1, except we are now using DAAB.
public static DataSet GetOrderDetails(int orderID)
{
return SqlHelper.ExecuteDataset(m_connString,
CommandType.StoredProcedure,
"CustOrdersDetail",
new SqlParameter("@OrderID", orderID));
}
Figure 2A: SQL Server data access the simpler way using the Microsoft Data Access Application Block.
Public Shared Function GetOrderDetails( _
ByVal orderID As Integer) As DataSet
Return SqlHelper.ExecuteDataset(m_connString, _
CommandType.StoredProcedure, _
"CustOrdersDetail", _
new SqlParameter("@OrderID", orderID))
End Function
Figure 2B: The VB.NET version.
This single call to SqlHelper.ExecuteDataset only requires the minimum amount of information that is specific to that stored procedure call. All of the other ADO.NET code is handled for you internally by DAAB, including closing the SqlConnection when the call has been completed.
Hopefully it is apparent that using DAAB is far simpler than writing all the ADO.NET code yourself. Releasing you from that tedium should be reason enough to switch. However, because writing less code means there is a decreased likelihood of introducing bugs, then DAAB can also make your application less error-prone.
Extensibility
Usually when we say something in the development world is extensible we mean that it exposes some API that you can program against to create additional features. In the case of DAAB, you are given the complete source code, which you are free to extend by modifying as you see fit. The entire DAAB is merely one file with two classes, and comes in C# and VB.NET versions (which are functionally identical). You can add, remove, and modify it to your heart s content. If you want to change the implementation of ExecuteScalar so that it always returns an integer value, you can. If you only ever use DataTables in your application, then you can add an ExecuteDataTable method to the SqlHelper class instead of having to call ExecuteDataset, and then access the Tables collection of the DataSet every time.
One common modification I usually make is to add an overload for the various ExecuteXXX methods so that they take neither a connection string nor a SqlConnection object as a parameter. The methods are coded to internally derive the connection string from a Web.config <appSetting>, thus requiring the developer to pass in one less parameter. It s a simple change, but just one example of how DAAB can be used to provide a better data access process tailored toward your ASP.NET application.
Conclusion
In my opinion, using DAAB is a no-brainer. You can probably write your own data access helper classes, but I see no reason for that when Microsoft has already provided DAAB for free. And if there is something you don t like about the way it works, or there is some feature you d like to add, then you are completely free to do so.
In addition to the features I ve already described, DAAB also has the ability to discover and cache the collection of SqlParameters for a stored procedure call. Also, you can easily write robust data access code because the SqlHelper class static methods include overloads for passing in a SqlTransaction object so that you can include a group of DAAB calls together in one transaction. Finally, DAAB also supports the ability to fill an existing DataSet object or even a strongly-typed DataSet. Because of all this, I have yet to come across a situation where I felt that I was developing myself into a corner by using DAAB. It simply does everything I need to do as far as data access goes and with far less code.
Ken McNamee is a Senior Software Developer with Vertigo Software, Inc., a leading provider of software development and consulting services on the Microsoft platform. Prior to this, he led a team of developers in re-architecting the Home Shopping Network s e-commerce site, http://www.HSN.com, to 100% ASP.NET with C#. Readers can contact him at [email protected].
