Minimize Data Access Code

XtremeData

LANGUAGES: C#

ASP.NET VERSIONS: 1.0 |1.1

Minimize Data Access Code

Use the Data Access Application Block to enhanceproductivity and improve the quality of your code.

By Dan Wahlin

ASP.NET developers spend a largeamount of time writing database-specific code to integrate relational data intoWeb applications. This work typically involves creating tables and storedprocedures as well as instantiating classes such as SqlConnection andSqlCommand that tie the application to the database. After creating connectionobject after connection object, developers quickly realize that wrapper classescan be created to minimize the redundancy of code and the number of lineswritten per application to access the database. Unfortunately, code written tosimplify this process often differs from individual to individual - and withoutan agreed upon standard, it may not fit well into a team environment. It canalso, of course, cause debugging and maintenance issues.

What is needed is a standard way toaccess databases that provides consistency for a team and also minimizes theamount of development time. The solution should demonstrate good codingpractices and design patterns without adding additional overhead. Althoughthere are many skilled developers that are up to the task of creating such adata access architecture (and many architectures have been created), Microsofthas released a coding solution that can be used immediately called the DataAccess Application Block (download it from http://msdn.microsoft.com). Inthis article I'll introduce you to this application block and demonstrate howit can be used to enhance productivity and increase the quality of your code.

A Simple API

The Data Access Application Block providesan extremely simple API that can be accessed through two classes. The firstclass, named SqlHelper, provides the bulk of the functionality found in theAPI. SqlHelper has several different methods, such as ExecuteDataSet,ExecuteReader, and ExecuteNonQuery, that make it easy (and unnecessary in somecases) to create connections, commands, and data adapters. The second class,named SqlHelperParameterCache, can be used to cache SqlParameter objects basedon a given connection string and command text string.

Before I show you a simpleapplication that leverages a few aspects of the Data Access Application Block,let's take a look at how the application block code can expedite developmenttime by reducing the number of lines of code you need to write. Take a look atthe C# code used to connect with a SQL Server database, create a data adapterthat calls a stored procedure, and then fill a DataSet (see Figure 1). Thistype of code is quite frequent in applications and tends to be redundant. Morelines of code would be necessary if the stored procedure expected parameters tobe passed to, or needed to be wrapped in, a transaction.

string connStr =

    ConfigurationSettings.AppSettings["connStr"];

SqlConnection conn = newSqlConnection(connStr);

SqlCommand cmd = newSqlCommand("StoredProcName", conn);

cmd.CommandType =CommandType.StoredProcedure;

SqlDataAdapter da = newSqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

Figure 1. The C#code shown here is typical for applications that need to fill datasets. Uponestablishing a database connection and creating a SqlDataAdapter, a DataSet isfilled with data.

The code in Figure 2 performs thesame task shown in Figure 1 by using the static ExecuteDataSet method of theSqlHelper 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 ofdevelopers is arguably even better because it is much easier to debug andmaintain the application.

string connStr =

    ConfigurationSettings.AppSettings["connStr"];

DataSet ds = SqlHelper.ExecuteDataSet(connStr,

  CommandType.StoredProcedure, "StoredProcName");

Figure 2. By usingMicrosoft's Data Access Application Block code, the amount of code you write isgreatly minimized, which makes applications easier to debug and maintain.

Put the API to Work

The next code listings I'veincluded will show you how the Data Access Application Block can be used tocreate an ASP.NET application that selects and updates data from multipletables in Microsoft's sample Northwind database. The application first allows auser to enter her customer ID into a textbox. If the ID is found, thecustomer's order history is displayed along with customer profile information(see Figure 3).

Figure 3. Several different ASP.NETLabel and Hyperlink controls are used to display data from the Northwinddatabase. The controls receive their data from the ExecuteReader method of theSqlHelper class.

All the customer data shown inFigure 3 is retrieved by calling a single stored procedure named CustDetails,which handles selecting customer data and order history data in one databasecall. See Figure 4 for the CustDetails stored procedure code.

CREATE PROCEDURE CustDetails

     (

        @CustomerIDnchar(5)

    )

AS

    BEGIN

        SELECT CustomerID,ContactName,

        Address, City,Region, PostalCode

        FROM Customers

        WHERE CustomerID =@CustomerID

        EXEC CustOrdersOrders @CustomerID

    END

Figure 4. TheCustDetails stored procedure minimizes database roundtrips by querying theCustomers and Orders tables in the Northwind database.

Notice that CustDetails accepts asingle input parameter named @CustomerID. Typically parameters are passed fromASP.NET Web forms to the database by creating one or more instances of theSqlParameter class (located in the System.Data.SqlClient namespace). Becausethe SqlHelper's ExecuteReader method allows an array of parameter values to bepassed in, however, retrieving the necessary data can be done with one or twolines of code.

Theexample in Figure 5 shows this code, as well as how the two resultsets returnedfrom the CustDetails stored procedure can be accessed using the SqlDataReader'sNextResult 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 moreresults to read through for orders

  if (reader.NextResult()){

    this.dlOrders.DataSource = reader;

    this.dlOrders.DataBind();

  }

}

Figure 5. Passingone or more parameters to a stored procedure requires much less code when youuse the SqlHelper class. This code demonstrates how to pass the customer IDentered by the end user to the CustDetails stored procedure and how datareturned can be bound to different ASP.NET server controls. For the sake ofbrevity this example doesn't use business and data access layers; however,these layers are still recommended.

Note that SqlParameter objects canstill be created and passed to the ExecuteReader method (as well as otherSqlHelper methods) because it is overloaded:

ExecuteReader(string connectionString,

    CommandTypecommandType,

    string commandText,

    params SqlParameter[]commandParameters)

I'll providemore details on this later in the article when I discuss theSqlHelperParameterCache class.

Once a customer views her profileand order history, she can choose to modify the profile by clicking the Modifyhyperlink server control shown earlier in Figure 3. Doing this hides the labelsand replaces them with textboxes and a submit button. To keep coding at aminimum, the labels and textboxes are defined next to each other within theHTML code (see Figure 6).

Figure 6. Storingthe Label and TextBox controls in the same location within the HTML makes itmuch faster to develop an application and eases maintenance. Figure 7 shows howto dynamically show and hide these controls depending upon if the user is inread-only or edit mode.

Look at ShowHide

Upon clicking the Modify link inthe browser, the btnModify_Click event handler is called on the server, whichin turn, calls a method named ShowHide. The ShowHide method handles showinglabels or textboxes depending on what the user desires. Both methods appear inthis code (see Figure 7). Looking at ShowHide you'll see that it accepts twoparameters as input. The first parameter represents the Panel control thatcontains the controls needing to be shown or hidden while the second Booleanparameter is used to check whether the user would like to modify her data ornot. Each control in the Panel is iterated through, and if the control's Typeis 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 inpnl.Controls) {

    // Don't show Labelwhen 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. Labeland TextBox controls placed together in the HTML can easily be shown or hiddenupon demand by checking their Type using the C# "is" keyword. The ShowHidemethod shown here does this by looping through controls contained within aPanel control.

After the customer makes thenecessary modifications to her profile and clicks the submit button, theSqlHelper's ExecuteNonQuery method is utilized to call an update storedprocedure named CustUpdate. This procedure expects six different parameters tobe passed to it (see Figure 8). Rather than explicitly creating these parametersin the code, however, they can simply be passed to SqlHelper's ExecuteNonQuery(see Figure 9), which allows a param array of stored procedure parameter valuesin one of its overloaded methods. Using this method provides a nice boost inproductivity because the amount of code required is greatly minimized comparedto doing everything by hand using native System.Data.SqlClient classes.

CREATE PROCEDURE CustUpdate

     (

        @CustomerIDnchar(5),

        @ContactNamenvarchar(30),

        @Address nvarchar(60),

        @Citynvarchar(15),

        @Regionnvarchar(15),

        @PostalCodenvarchar(10)

    )

AS

    BEGIN

        UPDATE Customers

        SET ContactName =@ContactName,

        Address =@Address,

        City = @City,

        Region = @Region,

        PostalCode =@PostalCode

        WHERE CustomerID =@CustomerID

    END

Figure 8. TheCustUpdate stored procedure accepts six different parameters that are used toupdate 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. TheSqlHelper's ExecuteNonQuery method (similar to many other SqlHelper methods)contains several different overloads that allow stored procedure parametervalues to be passed.

Other API Features

If an array of parameters beingpassed to a stored procedure is expected to be accessed frequently or you'dlike more control over defining parameter types and sizes, you can make theoperation more efficient by using the application block'sSqlHelperParameterCache class. Although this generally requires typing morecode, it gives you more control over the different parameters you use,including output and return parameters.

The SqlHelperParameter class isdesigned to cache/store parameters based on a connection string and storedprocedure name. SqlHelperParameterCache has only three methods:CacheParameterSet, GetCachedParameterSet, and GetSpParameterSet.

Take a look at how theCacheParameterSet and GetSpParameterSet methods can be used to cache theparameters used by the CustUpdate stored procedure (which was shown earlier inFigure 8) (see Figure 10). The code first checks to see if the parameters arealready cached or not by calling the GetSpParameterSet method. If no parametersare found, a null is returned and new SqlParameter objects are created andcached by calling the CacheParameterSet method. The different SqlHelper methodscan be called when the SqlParameter array is available. Several of the methodshave 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){

    SqlParameterCustomerID = new SqlParameter

         ("@CustomerID", SqlDbType.NChar,5);

    SqlParameterContactName = 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);

    SqlParameterPostalCode = new SqlParameter

         ("@Address", SqlDbType.NVarChar,10);

    //Cache params usingSqlHelperParameterCache

    sqlParams = newSqlParameter[]{CustomerID,ContactName,

      Address, City,Region, PostalCode};

    SqlHelperParameterCache.CacheParameterSet(connStr,

      "CustUpdate",sqlParams);

  }

  // Assign param values.We could also do a conditional  

  // statement to assignvalues 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 SqlHelperParameterCacheclass is used to cache and retrieve SqlParameter objects based on theconnection string and stored procedure name. This code demonstrates how to usethe CacheParameterSet and GetSpParameterSet methods.

Although it's not shown in Figure10, the GetSpParameterSet method also allows return parameters to be includedautomatically in the SqlParameter array through passing true or false to itsthird parameter found in the following overload:

public static SqlParameter[] GetSpParameterSet(

  string connectionString,string spName,

  boolincludeReturnValueParameter)

The name of the return parameterwill always be @Return_Value and it will be placed at the very first positionin the SqlParameter array.

In addition to providing supportfor parameter caching, the Data Access Application Block API also supportswrapping transactions around different operations so that you can commit orroll back changes. Although I won't cover this feature here, you can read moreabout it in ProtectYour Info and also reference the application block help file.

In this article you've seen a fewof the features found in Microsoft's Data Access Application block (the sampleapplication available with this article's downloadable code includes additionalsamples). Although it's good to understand the inner-workings of SQL Serverspecific classes in the .NET Framework, once you've mastered them, you canminimize the amount of data access code you write by using application blockAPIs. In addition to providing productivity boosts and good designarchitecture, the API keeps coding consistent across development teams, whichmakes debugging and maintenance easier. As an added benefit, all theapplication block source code is available to tweak in cases where you'd liketo enhance the existing API.

The sample codein this article is available for download.

DanWahlin (Microsoft Most Valuable Professional for ASP.NET and XML Web Services)is the president of Wahlin Consulting and founded the XML for ASP.NETDevelopers Web site (http://www.XMLforASP.NET),which focuses on using XML and Web services in Microsoft's .NET platform. He'salso a corporate trainer and speaker, and teaches XML and .NET training coursesaround the United States. Dan coauthored Professional Windows DNA (Wrox, 2000) and ASP.NET: Tips, Tutorialsand Code (Sams, 2001), and authored XML for ASP.NET Developers (Sams, 2001).