Using the Enterprise Library Data Access Block for .NET 2.0

ADO .NET provides many rich features that can be used to retrieve and display data in a number of ways, but even given such flexibility, sometimes developers find themselves repeating the same code again and again. For example, every data-driven application requires access to a database. You need to write code to connect to the database, open a connection, execute dynamic SQL or stored procedures, retrieve the results, and close the connection. In other words, this is "plumbing" code that you will be forced to write in almost any application you develop, even though it doesn't add any value to the core business users.

Typically, the only things in this code that might differ between applications are the SQL statements or stored procedure names, the command parameters, and the connection string. As long as you can parameterize these variables, you can abstract most of the plumbing code into reusable classes that can be leveraged across multiple applications. That's a worthwhile effort—but it's no longer worth doing yourself, because that's exactly what Microsoft (with community support) has done in its set of Enterprise Library (known as EntLib) Application Blocks. This article introduces the EntLib Data Access Block by showing examples of how to use it to write robust data access code.

The Enterprise Data Access Block
The Data Access Block addresses the most common tasks developers face when developing database applications. By providing a set of encapsulated methods, the Data Access Block greatly simplifies the most common methods of accessing a database. Each method contains the logic required to retrieve the appropriate data, and also manage the database connection. In addition, the Data Access Block is database-agnostic; it supplements the code in ADO.NET 2.0 by letting you write data access code that works across different relational databases—without rewriting code. The classes contain code that provides database-specific implementations for features such as parameter handling and cursors.

Moreover the Data Access Block also provides specific derived classes for SQL Server and Oracle databases. In addition, the GenericDatabase class allows you to use the application block with any configured ADO.NET 2.0 DbProviderFactory object. Finally, you can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database.

Microsoft has redesigned version 2.0 of the Data Access Block to take advantage of new ADO.NET 2.0 features. You can download the EntLib Data Access Block from MSDN.

Using the Data Access Block
To use the Data Access Block successfully, you will need to go through the steps listed below:

1. Add a reference to the Microsoft.Practices.EnterpriseLibrary.Common.dll and Microsoft.Practices.EnterpriseLibrary.Data.dll assemblies from your solution. You can do this by using the "Add Reference" option and navigating to the <Drive Name>:\Program Files\Microsoft Enterprise Library January 2006\bin folder.
2. Add the necessary configuration entries to the web.config or app.config file or a custom configuration file. To this end, you add the below <configSections> element under the root <configuration> element.
   

      
    1 <configSections>
    2             <section    
    3               name="dataConfiguration"             
    4               type="Microsoft.Practices.           
    5               EnterpriseLibrary.Data.
    6               Configuration.
    7               DatabaseSettings,  
    8               Microsoft.Practices.
    9               EnterpriseLibrary.Data" />
   10           </configSections>
   11 

   Then you also add the <dataConfiguration><configuration> element as shown below:
   

            
   1 <dataConfiguration          
   2             defaultDatabase=
   3             "AdventureWorksDB"/>

   In this example, I have marked AdventureWorks as the default database, declared separately under the <connectionStrings> element.

             
    1 <connectionStrings>
    2             <add
    3              name="AdventureWorksDB" 
    4               providerName=
    5               "System.Data.SqlClient"   
    6               connectionString=
    7               "server=localhost;
    8               database=AdventureWorks;
    9               UID=user;PWD=word;" />
   10 </connectionStrings>

3. Import the core Microsoft.Practices.EnterpriseLibrary.Data namespace for the Data Access Block.
4. Start writing code against the classes in the preceding namespace.

Using the Database Object
Whenever you work with the Data Access Block, you'll have to deal with the Database class first. The Database class represents the database itself, and provides methods (see Table 1) that you can use to perform CRUD (Create, Read, Update and Delete) operations against the database.

Table 1: The table lists Database class methods that you use to perform CRUD operations against the database.

Method	Description
ExecuteDataSet	Executes a SQL query or stored procedure and returns the results in the form of a DataSet object.
ExecuteNonQuery	Executes a SQL query or stored procedure and returns the number of records affected.
ExecuteReader	Executes a SQL query or stored procedure and returns the results in the form of an IDataReader object.
ExecuteScalar	Executes a SQL query or stored procedure and returns the first column of the first row in the result set.
LoadDataSet	Executes a command and adds a new DataTable to the existing DataSet.
UpdateDataSet	Synchronizes the DataSet contents with the database by executing appropriate INSERT, UPDATE, and DELETE statements.

Now that you have a basic overview of the methods of the Database class, the next few sections provide examples that demonstrate how you use the class.

Execute a Query Returning a DbDataReader
I'll start with a simple example. You'll execute a simple SQL statement against the AdventureWorks database and retrieve the results in the form of a DbDataReader object, which you'll then bind to a GridView control in an ASP.NET page. Listing 1 contains the full page code. Here's the Page_Load script:

 1    <script runat="server">
 2      void Page_Load(object sender, EventArgs e)
 3      {
 4        Database db = DatabaseFactory.CreateDatabase();
 5        string sqlCommand = "Select " + "EmployeeID, 
 6          NationalIDNumber," + "LoginID, Title from " +
 7          "HumanResources.Employee ";
 8        DbCommand dbCommand = db.GetSqlStringCommand
 9          (sqlCommand);
10        using (IDataReader reader = db.ExecuteReader(
11          dbCommand))
12        {
13          gridEmployees.DataSource = reader;
14          gridEmployees.DataBind();
15        }                
16      }
17    </script>

Figure 1. Binding a DbDataReader: The page shows the result of binding the DbDataReader object returned by the ExecuteReader() method to a GridView control.

The preceding code first invokes the CreateDatabase() method of the DatabaseFactory class to obtain an instance of the Database object. As the name suggests, the DatabaseFactory is a class containing factory methods that create Database objects. The CreateDatabase() method is overloaded and returns a default database when you invoke the parameter-less version of the method as shown above. To set the default database, assign the defaultDatabase attribute value to the appropriate database configuration key in the Web.config file as shown below.

   
1 <dataConfiguration 
2      defaultDatabase="AdventureWorksDB"/>
3 

After obtaining an instance of the Database object, you then invoke the GetSqlStringCommand() method to construct an instance of the ADO.NET 2.0 DbCommand object. After that, you execute the SQL through the Database.ExecuteReader() method passing in the DbCommand as an argument. Navigate to the page using a browser. Because the method binds the returned DbDataReader to a GridView control, you'll see output similar to Figure 1.

Execute a SQL Query Returning a DataSet
The Database class provides a database-agnostic approach, meaning that you can leverage the class across a wide range of relational databases such as SQL Server, Oracle and others. However, it also contains database-specific classes, such as SqlDatabase (derived from Database), which represents a SQL Server database, and OracleDatabase, which lets you program against the Oracle database. You should use these database-specific classes when you're programming solely for one of the supported databases.

Note that neither the SqlDatabase nor the OracleDatabase classes provide static methods; you must create an instance to work with them. The following page code shows how to execute a SQL query against the AdventureWorks database using the SqlDatabase class.

 1 <%@ Page Language="C#" %>
 2    <%@ Import Namespace="System.Data" %>
 3    <%@ Import Namespace="System.Data.Common" %>
 4    <%@ Import Namespace= "Microsoft.Practices.
 5      EnterpriseLibrary.Data" %>
 6    <%@ Import Namespace= "Microsoft.Practices.
 7      EnterpriseLibrary.Data.Sql" %>
 8    
 9    <script runat="server">
10      void Page_Load(object sender, EventArgs e)
11      {
12        string connString = System.Web.Configuration.            
13          WebConfigurationManager.ConnectionStrings
14          ["AdventureWorksDB"].ConnectionString;
15        SqlDatabase db = new SqlDatabase(connString);    
16        string   sqlCommand = "Select " + "EmployeeID, 
17          NationalIDNumber," + "LoginID, Title from " +
18          " HumanResources.Employee ";
19        DbCommand dbCommand = db.GetSqlStringCommand
20          (sqlCommand);
21        DataSet dst = db.ExecuteDataSet(dbCommand);
22        gridEmployees.DataSource = 
23          dst.Tables[0].DefaultView;
24        gridEmployees.DataBind();        
25      }
26    </script>
27    <html xmlns="http://www.w3.org/1999/xhtml" >
28    <head runat="server">
29      <title>Executing a SQL Select Statement to retrieve 
30        a DataSet</title></head>
31    <body>
32      <form id="form1" runat="server">
33        <div><asp:GridView runat="server"
34            ID="gridEmployees" /></div>
35      </form>
36    </body>
37    </html>

Author's Note: The namespaces you import (Imports) and the page HTML code for the rest of the examples in this article are similar to the preceding example, so I'll omit those in the remaining examples; however, you can download the sample code to get complete sample pages that you can experiment with.

You start by retrieving the connection string from the Web.config file using the WebConfigurationManager class, then you supply the connection string as an argument to the SqlDatabase class constructor. After you have an instance of the SqlDatabase class, you work with it in the same manner as the generic Database class, because SqlDatabase inherits from Database. For example, the SqlDatabase class also exposes methods such as GetSqlStringCommand(), and ExecuteDataSet() through which you can retrieve a DbCommand object instance, execute a SQL query, and return the results in the form of a DataSet object. Although the preceding example uses a SELECT SQL statement, you can easily perform the same operation using a stored procedure.

Executing a Stored Procedure to Retrieve a Single Item
Sometimes, you might want to retrieve only one item from a stored procedure or SQL query. For example, after inserting a new row into the database, you might want to return the newly inserted identity value to the consumers.

For this purpose, the Database class provides a method named ExecuteScalar() that accepts a DbCommand object as an input argument. To showcase this, I will leverage a stored procedure named GetEmployeeNationalID that takes an EmployeeID as an argument and returns the NationalID value for that employee. The stored procedure is declared as follows:

1 Create Procedure GetEmployeeNationalID
2      @EmployeeID int
3    AS
4      Select NationalIDNumber
5        from HumanResources.Employee
6        Where EmployeeID = @EmployeeID

The following ASP.NET page excerpt shows the code required to invoke the GetEmployeeNationalID stored procedure.

   
 1 <script runat="server">
 2      void Page_Load(object sender, 
 3        EventArgs e)
 4      {
 5        int employeeID = 1;
 6        Database db = 
 7          DatabaseFactory.CreateDatabase();
 8        string sqlCommand = 
 9          "GetEmployeeNationalID";
10        DbCommand dbCommand =       
11          db.GetSqlStringCommand
12          (sqlCommand);
13        dbCommand.CommandType = 
14          CommandType.StoredProcedure;
15        db.AddInParameter(dbCommand, 
16          "EmployeeID", DbType.Int32, 1);
17        string nationalID = (string) 
18          db.ExecuteScalar(dbCommand);
19        Response.Write("NationalID : " + 
20          nationalID + "<br>");        
21       }
22    </script>

First, the code adds the EmployeeID input parameter to the DbCommand object using the AddInParameter() method. Note that the EmployeeID parameter is not prefixed with an ampersand (@), which is how you would normally indicate a stored procedure parameter for SQL Server—and how the previous version of the Data Access Block worked. In this new version of the Data Access Block, the Database class is generic, obviating the need to specify database-specific prefixes such as ampersands and question marks. The ExecuteScalar() method executes the stored procedure and returns the NationalID, which the code displays on the client using the Response.Write() statement at the end of the script.

Executing a Stored Procedure to Retrieve a Single Row
Sometimes you need to retrieve a single row instead of a group of rows. Instead of returning this single row as a DataSet or a DataReader, you can have the stored procedure return the single row as a set of output parameters. To do this, you use the ExecuteNonQuery() method of the Database class. As part of the preparation of the DbCommand, you need to add the output parameters to the DbCommand object. Here's the stored procedure declaration.

 1 Create Procedure GetEmployeeDetails
 2      @EmployeeID int,
 3      @NationalIDNumber nvarchar(15) 
 4        OUTPUT,
 5      @LoginID nvarchar(256) OUTPUT,
 6      @Title nvarchar(50) OUTPUT
 7    AS
 8      Select @NationalIDNumber = 
 9        NationalIDNumber,
10        @LoginID = LoginID,
11        @Title = Title from 
12        HumanResources.Employee
13      Where EmployeeID = @EmployeeID
14    GO

The following Page_Load code executes the GetEmployeeDetails stored procedure and returns the NationalIDNumber, LoginID, and Title column values as output parameters.

 1 <script runat="server">
 2      void Page_Load(object sender, EventArgs e)
 3      {
 4        int employeeID = 1;
 5        Database db = DatabaseFactory.CreateDatabase();
 6        string sqlCommand ="GetEmployeeDetails";
 7        DbCommand dbCommand = db.GetSqlStringCommand
 8          (sqlCommand);
 9        dbCommand.CommandType = 
10          CommandType.StoredProcedure;
11        db.AddInParameter(dbCommand, 
12          "EmployeeID", DbType.Int32, 1);
13        db.AddOutParameter(dbCommand, "NationalIDNumber", 
14           DbType.String, 15);
15        db.AddOutParameter(dbCommand, "LoginID", 
16           DbType.String, 256);
17        db.AddOutParameter(dbCommand, "Title", 
18           DbType.String, 50);
19        db.ExecuteNonQuery(dbCommand);
20        Response.Write("NationalID : " + 
21          db.GetParameterValue(dbCommand, 
22          "NationalIDNumber") + "<br>");
23        Response.Write("Login ID : " + 
24          db.GetParameterValue(dbCommand, 
25          "LoginID") + "<br>");
26        Response.Write("Title : " + db.GetParameterValue(
27          dbCommand, "Title") + "<br>");        
28      }
29    </script>
   

In the preceding code, you add the output parameters using the AddOutParameter() method of the DbCommand object. After executing the stored procedure, you simply retrieve the output parameter values using the GetParameterValue() method of the DbCommand object.

Transactional Code Block for Performing Multiple Updates
There are times where you may want to execute multiple operations against a database, but perform them all within the scope of a single transaction. The EntLib Data Access Block enables this scenario by providing the Database.CreateConnection() method that allow you to get a reference to an ADO.NET 2.0 DbConnection object. Using the DbConnection object, you can obtain a reference to a DbTransaction object by calling the BeginTransaction() method, assigning the return value to a DbTransaction variable. Subsequently, you can then easily control the transaction behavior by invoking either the Commit() or Rollback() methods when the execution succeeds or fails, respectively. The following pseudo code shows how to execute multiple stored procedures within the scope of a single transaction.

 1 Database db = 
 2      DatabaseFactory.CreateDatabase();
 3    //Two operations, one to add the order //and another to add order details 
 4    string sqlCommand = "InsertOrder";
 5    DbCommand orderCommand = 
 6      db.GetStoredProcCommand(sqlCommand);
 7    //Add InsertOrder parameters
 8    sqlCommand = "InsertOrderDetails";
 9    DbCommand orderDetailsCommand = 
10    db.GetStoredProcCommand(sqlCommand);
11    //Add InsertOrderDetails parameters
12    using (DbConnection connection = 
13      db.CreateConnection())
14    {
15      connection.Open();
16      DbTransaction transaction = 
17        connection.BeginTransaction();
18      try
19      {
20        //Execute the InsertOrder
21        db.ExecuteNonQuery(orderCommand, 
22          transaction);
23        //Execute the InsertOrderDetails    
24        db.ExecuteNonQuery(
25          orderDetailsCommand, 
26          transaction);
27        //Commit the transaction
28        transaction.Commit();                    
29      }
30      catch
31      {
32        //Roll back the transaction. 
33        transaction.Rollback();
34      }
35    }  

The majority of the work happens inside the using block. That block starts the transaction using the BeginTransaction() method and also commits or rolls back the transaction using either Commit() or Rollback().

Keep It Short
As you have seen from this article, the EntLib Data Access Block obviates the need to write the repetitive data access plumbing code by providing a set of highly reusable classes for performing operations against the database. Through the use of these classes, you can eliminate errors, bugs, and typos in your application and focus on the core business logic of the application thereby gain productivity.

Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He specializes in architecting, designing, and developing distributed enterprise class applications using .NET related technologies. He has coauthored a number of books in .NET related technologies and is a frequent contributor to leading technology related online publications.