XtremeData
LANGUAGES: C#
ASP.NET VERSIONS: 1.0 |1.1
Minimize Data Access Code
Use the Data Access Application Block to enhance productivity and improve the quality of your code.
By Dan Wahlin
ASP.NET developers spend a large amount of time writing database-specific code to integrate relational data into Web applications. This work typically involves creating tables and stored procedures as well as instantiating classes such as SqlConnection and SqlCommand that tie the application to the database. After creating connection object after connection object, developers quickly realize that wrapper classes can be created to minimize the redundancy of code and the number of lines written per application to access the database. Unfortunately, code written to simplify this process often differs from individual to individual - and without an agreed upon standard, it may not fit well into a team environment. It can also, of course, cause debugging and maintenance issues.
What is needed is a standard way to access databases that provides consistency for a team and also minimizes the amount of development time. The solution should demonstrate good coding practices and design patterns without adding additional overhead. Although there are many skilled developers that are up to the task of creating such a data access architecture (and many architectures have been created), Microsoft has released a coding solution that can be used immediately called the Data Access Application Block (download it from http://msdn.microsoft.com). In this article I'll introduce you to this application block and demonstrate how it can be used to enhance productivity and increase the quality of your code.
A Simple API
The Data Access Application Block provides an extremely simple API that can be accessed through two classes. The first class, named SqlHelper, provides the bulk of the functionality found in the API. SqlHelper has several different methods, such as ExecuteDataSet, ExecuteReader, and ExecuteNonQuery, that make it easy (and unnecessary in some cases) to create connections, commands, and data adapters. The second class, named SqlHelperParameterCache, can be used to cache SqlParameter objects based on a given connection string and command text string.
Before I show you a simple application that leverages a few aspects of the Data Access Application Block, let's take a look at how the application block code can expedite development time by reducing the number of lines of code you need to write. Take a look at the C# code used to connect with a SQL Server database, create a data adapter that calls a stored procedure, and then fill a DataSet (see Figure 1). This type of code is quite frequent in applications and tends to be redundant. More lines of code would be necessary if the stored procedure expected parameters to be passed to, or needed to be wrapped in, a transaction.
string connStr =
ConfigurationSettings.AppSettings["connStr"];
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("StoredProcName", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Figure 1. The C# code shown here is typical for applications that need to fill datasets. Upon establishing a database connection and creating a SqlDataAdapter, a DataSet is filled with data.
The code in Figure 2 performs the same task shown in Figure 1 by using the static ExecuteDataSet method of the SqlHelper class. You can see that the number of lines of code is cut in half. Although minimizing the amount of code written is certainly a good thing, having a consistent code base across an application written by a group of developers is arguably even better because it is much easier to debug and maintain the application.
string connStr =
ConfigurationSettings.AppSettings["connStr"];
DataSet ds = SqlHelper.ExecuteDataSet(connStr,
CommandType.StoredProcedure, "StoredProcName");
Figure 2. By using Microsoft's Data Access Application Block code, the amount of code you write is greatly minimized, which makes applications easier to debug and maintain.
Put the API to Work
The next code listings I've included will show you how the Data Access Application Block can be used to create an ASP.NET application that selects and updates data from multiple tables in Microsoft's sample Northwind database. The application first allows a user to enter her customer ID into a textbox. If the ID is found, the customer's order history is displayed along with customer profile information (see Figure 3).
Figure 3. Several different ASP.NET
Label and Hyperlink controls are used to display data from the Northwind
database. The controls receive their data from the ExecuteReader method of the
SqlHelper class.
All the customer data shown in Figure 3 is retrieved by calling a single stored procedure named CustDetails, which handles selecting customer data and order history data in one database call. See Figure 4 for the CustDetails stored procedure code.
CREATE PROCEDURE CustDetails
(
@CustomerID nchar(5)
)
AS
BEGIN
SELECT CustomerID, ContactName,
Address, City, Region, PostalCode
FROM Customers
WHERE CustomerID = @CustomerID
EXEC CustOrdersOrders @CustomerID
END
Figure 4. The CustDetails stored procedure minimizes database roundtrips by querying the Customers and Orders tables in the Northwind database.
Notice that CustDetails accepts a single input parameter named @CustomerID. Typically parameters are passed from ASP.NET Web forms to the database by creating one or more instances of the SqlParameter class (located in the System.Data.SqlClient namespace). Because the SqlHelper's ExecuteReader method allows an array of parameter values to be passed in, however, retrieving the necessary data can be done with one or two lines of code.
The example in Figure 5 shows this code, as well as how the two resultsets returned from the CustDetails stored procedure can be accessed using the SqlDataReader's NextResult method.
private void FillDetails() {
pnl1.Visible = false;
pnl2.Visible = true;
SqlDataReader reader = SqlHelper.ExecuteReader(connStr,
"CustDetails", this.txtCustomerID.Text);
reader.Read();
this.lblCustomerID.Text = reader["CustomerID"].ToString();
this.lblContactName.Text =
reader["ContactName"].ToString();
this.lblAddress.Text = reader["Address"].ToString();
this.lblCity.Text = reader["City"].ToString();
this.lblRegion.Text = reader["Region"].ToString();
this.lblPostalCode.Text =
reader["PostalCode"].ToString();
//See if we have more results to read through for orders
if (reader.NextResult()) {
this.dlOrders.DataSource = reader;
this.dlOrders.DataBind();
}
}
Figure 5. Passing one or more parameters to a stored procedure requires much less code when you use the SqlHelper class. This code demonstrates how to pass the customer ID entered by the end user to the CustDetails stored procedure and how data returned can be bound to different ASP.NET server controls. For the sake of brevity this example doesn't use business and data access layers; however, these layers are still recommended.
Note that SqlParameter objects can still be created and passed to the ExecuteReader method (as well as other SqlHelper methods) because it is overloaded:
ExecuteReader(string connectionString,
CommandType commandType,
string commandText,
params SqlParameter[] commandParameters)
I'll provide more details on this later in the article when I discuss the SqlHelperParameterCache class.
Once a customer views her profile and order history, she can choose to modify the profile by clicking the Modify hyperlink server control shown earlier in Figure 3. Doing this hides the labels and replaces them with textboxes and a submit button. To keep coding at a minimum, the labels and textboxes are defined next to each other within the HTML code (see Figure 6).
| Customer Details:
forecolor="white">Modify |
|
| Name: |
visible="False" /> |
| Address: |
visible="False" /> |
| City: |
visible="False" /> |
| Region: |
visible="False" /> |
| Postal Code: |
visible="False" /> |
|
text="Update" visible="False" /> |
|
Figure 6. Storing the Label and TextBox controls in the same location within the HTML makes it much faster to develop an application and eases maintenance. Figure 7 shows how to dynamically show and hide these controls depending upon if the user is in read-only or edit mode.
Look at ShowHide
Upon clicking the Modify link in the browser, the btnModify_Click event handler is called on the server, which in turn, calls a method named ShowHide. The ShowHide method handles showing labels or textboxes depending on what the user desires. Both methods appear in this code (see Figure 7). Looking at ShowHide you'll see that it accepts two parameters as input. The first parameter represents the Panel control that contains the controls needing to be shown or hidden while the second Boolean parameter is used to check whether the user would like to modify her data or not. Each control in the Panel is iterated through, and if the control's Type is a Label, TextBox, or Button it is shown or hidden as appropriate.
private void btnModify_Click(object sender,
System.EventArgs e) {
ShowHide(pnl2,true);
this.txtCustomerID.Text = this.lblCustomerID.Text;
this.txtContactName.Text = this.lblContactName.Text;
this.txtAddress.Text = this.lblAddress.Text;
this.txtCity.Text = this.lblCity.Text;
this.txtRegion.Text = this.lblRegion.Text;
this.txtPostalCode.Text = this.lblPostalCode.Text;
}
private void ShowHide(Panel pnl, bool editMode) {
foreach (Control ctl in pnl.Controls) {
// Don't show Label when in edit mode
if (ctl is Label) ctl.Visible = !editMode;
if (ctl is TextBox) ctl.Visible = editMode;
if (ctl is Button) ctl.Visible = editMode;
}
}
Figure 7. Label and TextBox controls placed together in the HTML can easily be shown or hidden upon demand by checking their Type using the C# "is" keyword. The ShowHide method shown here does this by looping through controls contained within a Panel control.
After the customer makes the necessary modifications to her profile and clicks the submit button, the SqlHelper's ExecuteNonQuery method is utilized to call an update stored procedure named CustUpdate. This procedure expects six different parameters to be passed to it (see Figure 8). Rather than explicitly creating these parameters in the code, however, they can simply be passed to SqlHelper's ExecuteNonQuery (see Figure 9), which allows a param array of stored procedure parameter values in one of its overloaded methods. Using this method provides a nice boost in productivity because the amount of code required is greatly minimized compared to doing everything by hand using native System.Data.SqlClient classes.
CREATE PROCEDURE CustUpdate
(
@CustomerID nchar(5),
@ContactName nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10)
)
AS
BEGIN
UPDATE Customers
SET ContactName = @ContactName,
Address = @Address,
City = @City,
Region = @Region,
PostalCode = @PostalCode
WHERE CustomerID = @CustomerID
END
Figure 8. The CustUpdate stored procedure accepts six different parameters that are used to update the Customer table in the Northwind database.
private void btnModifySubmit_Click(object sender,
System.EventArgs e) {
ShowHide(pnl2,false);
SqlHelper.ExecuteNonQuery(connStr,"CustUpdate",
this.txtCustomerID.Text, this.txtContactName.Text,
this.txtAddress.Text, this.txtCity.Text,
this.txtRegion.Text, this.txtPostalCode.Text);
FillDetails();
}
Figure 9. The SqlHelper's ExecuteNonQuery method (similar to many other SqlHelper methods) contains several different overloads that allow stored procedure parameter values to be passed.
Other API Features
If an array of parameters being passed to a stored procedure is expected to be accessed frequently or you'd like more control over defining parameter types and sizes, you can make the operation more efficient by using the application block's SqlHelperParameterCache class. Although this generally requires typing more code, it gives you more control over the different parameters you use, including output and return parameters.
The SqlHelperParameter class is designed to cache/store parameters based on a connection string and stored procedure name. SqlHelperParameterCache has only three methods: CacheParameterSet, GetCachedParameterSet, and GetSpParameterSet.
Take a look at how the CacheParameterSet and GetSpParameterSet methods can be used to cache the parameters used by the CustUpdate stored procedure (which was shown earlier in Figure 8) (see Figure 10). The code first checks to see if the parameters are already cached or not by calling the GetSpParameterSet method. If no parameters are found, a null is returned and new SqlParameter objects are created and cached by calling the CacheParameterSet method. The different SqlHelper methods can be called when the SqlParameter array is available. Several of the methods have overloads that accept a SqlParameter array.
private void btnModifySubmit_Click(object sender,
System.EventArgs e) {
ShowHide(pnl2,false);
SqlParameter[] sqlParams =
SqlHelperParameterCache.GetSpParameterSet
(connStr, "CustUpdate");
if (sqlParams == null) {
SqlParameter CustomerID = new SqlParameter
("@CustomerID", SqlDbType.NChar,5);
SqlParameter ContactName = new SqlParameter
("@ContactName", SqlDbType.NVarChar,30);
SqlParameter Address = new SqlParameter
("@Address", SqlDbType.NVarChar,60);
SqlParameter City = new SqlParameter
("@Address", SqlDbType.NVarChar,15);
SqlParameter Region = new SqlParameter
("@Address", SqlDbType.NVarChar,15);
SqlParameter PostalCode = new SqlParameter
("@Address", SqlDbType.NVarChar,10);
//Cache params using SqlHelperParameterCache
sqlParams = new SqlParameter[]{CustomerID,ContactName,
Address, City, Region, PostalCode};
SqlHelperParameterCache.CacheParameterSet(connStr,
"CustUpdate",sqlParams);
}
// Assign param values. We could also do a conditional
// statement to assign values by param name instead of
// position.
sqlParams[0].Value = this.txtCustomerID.Text;
sqlParams[1].Value = this.txtContactName.Text;
sqlParams[2].Value = this.txtAddress.Text;
sqlParams[3].Value = this.txtCity.Text;
sqlParams[4].Value = this.txtRegion.Text;
sqlParams[5].Value = this.txtPostalCode.Text;
SqlHelper.ExecuteNonQuery
(connStr,"CustUpdate",sqlParams);
FillDetails();
}
Figure 10. The SqlHelperParameterCache class is used to cache and retrieve SqlParameter objects based on the connection string and stored procedure name. This code demonstrates how to use the CacheParameterSet and GetSpParameterSet methods.
Although it's not shown in Figure 10, the GetSpParameterSet method also allows return parameters to be included automatically in the SqlParameter array through passing true or false to its third parameter found in the following overload:
public static SqlParameter[] GetSpParameterSet(
string connectionString, string spName,
bool includeReturnValueParameter)
The name of the return parameter will always be @Return_Value and it will be placed at the very first position in the SqlParameter array.
In addition to providing support for parameter caching, the Data Access Application Block API also supports wrapping transactions around different operations so that you can commit or roll back changes. Although I won't cover this feature here, you can read more about it in Protect Your Info and also reference the application block help file.
In this article you've seen a few of the features found in Microsoft's Data Access Application block (the sample application available with this article's downloadable code includes additional samples). Although it's good to understand the inner-workings of SQL Server specific classes in the .NET Framework, once you've mastered them, you can minimize the amount of data access code you write by using application block APIs. In addition to providing productivity boosts and good design architecture, the API keeps coding consistent across development teams, which makes debugging and maintenance easier. As an added benefit, all the application block source code is available to tweak in cases where you'd like to enhance the existing API.
The sample code in this article is available for download.
Dan Wahlin (Microsoft Most Valuable Professional for ASP.NET and XML Web Services) is the president of Wahlin Consulting and founded the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), which focuses on using XML and Web services in Microsoft's .NET platform. He's also a corporate trainer and speaker, and teaches XML and .NET training courses around the United States. Dan coauthored Professional Windows DNA (Wrox, 2000) and ASP.NET: Tips, Tutorials and Code (Sams, 2001), and authored XML for ASP.NET Developers (Sams, 2001).
