Data Access Application Block Streamlines Your Data-Access Layer

The main purpose of an application's data-access layer is to transport required information back and forth between the database and the application. Because of the focused nature of the data-access code, the code in this layer can quickly become repetitive. For example, to interact with the database, you need to open a connection to the database, initialize the SQL command or stored procedure, and close the connection. Except for the name of the SQL command or stored procedure and the parameters passed to them, this is a common sequence you would have to follow from any data-access layer code.

Instead of creating this code in every project, wouldn't it be great to encapsulate it in a reusable block and reuse it in different projects? That is exactly what Microsoft's Enterprise Library (known as EntLib) Data Access Application Block provides. This article introduces the EntLib Data Access Application Block and shows how it makes data-access layer coding a breeze.

Encapsulation

Anytime you find yourself writing very similar code blocks in a project, consider encapsulation: grouping the related generic common functionality into a general black-box component and then reuse it from your application. Almost all applications need access to data, and they each need a data-access layer for this purpose. The data-access layer plumbing code is an example of the common code that you would write for all kinds of applications. By encapsulating this code, you can drastically reduce the amount of code in your data-access layer, thereby making the code more readable, maintainable, and updateable. Additionally, by reusing proven and tested code, you also reduce the chance for types, which results in increased overall application quality.

Installation

Before using the Data Access Application Block, take the following steps to install and configure it in your local machine:

  1. Download the EntLib from MSDN.
  2. From the menu, navigate to Start->All Programs->Microsoft patterns and practices->Enterprise Library->Build Enterprise Library. This will build all the EntLib blocks, including the Data Access Application Block.
  3. Once the assembly has been built, you can add references to the required Data Access Application Block assemblies (Microsoft.Practices.EnterpriseLibrary.Data.dll and Microsoft.Practices.EnterpriseLibrary.Configuration.dll) from <DriveName>:\Program Files\Microsoft Enterprise Library\bin. Note that you need to add a reference to the configuration block assembly as well since the Data Access Application Block has a dependency on the configuration block for reading connection-string-related information from the configuration file.
  4. Create the necessary configuration files using the Enterprise Library Configuration tool.
  5. Once the assemblies are referenced, the next step is to import the required namespaces. To leverage the Data Access Application Block, add the following import statements to your code:
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
    

Once you've installed the Data Access Application Block, create the configuration file through the configuration utility.

Creating Configuration Files

The enterprise library configuration tool helps create the necessary configuration files so that the Data Access Application Block can retrieve the configuration settings. Figure 1 shows the configuration settings used by the examples in this article.

Figure 1. The EntLib Configuration Settings

When you save the settings shown in Figure 1, you create two files: one with the name that you entered in the file 'save as' prompt and the other with the name dataConfiguration.config. The dataConfiguration.config file is the one that contains the actual connection string.

Now that you have a general understanding of the Data Access Application Block, take a look at some examples.

Executing a Dynamic SQL Query

To illustrate the advantage of using the Data Access Application Block, take a look at sample code that creates a DataSet object and binds it to a DataGrid using the Data Access Application Block. In general, returning a DataSet involves establishing a connection, creating a SqlCommand, and executing the command against the database. The resulting DataSet object can then be bound to a DataGrid:

private void btnInvokeDynamicSql_Click(object sender,
                                       System.EventArgs e)
{
   Database db = DatabaseFactory.CreateDatabase("Northwind");
   DataSet categoriesDataSet = db.ExecuteDataSet(CommandType.Text,
   "SELECT * FROM Categories");
   gridOutput.DataSource = categoriesDataSet.Tables[0].DefaultView;
   gridOutput.DataBind();
}

Everything starts out with DatabaseFactory.CreateDatabase().

Database db = DatabaseFactory.CreateDatabase("Northwind");

The above code specifies the database instance name as an argument to the CreateDatabase() method. If you don't specify a named instance of a database, it just grabs the default database in the configuration file. Then you simply execute the query using the ExecuteDataSet() method of the Database object:

DataSet categoriesDataSet = db.ExecuteDataSet(CommandType.Text,
"SELECT * FROM Categories"); 

Finally, you simply bind the DataSet onto a DataGrid control for display.

If you navigate to the page with your browser and click on the button, you should see the output shown in Figure 2.

Figure 2. Result of DataSet Object Bound to a DataGrid

Executing a Stored Procedure with Input Parameters

To demonstrate the steps involved in executing a stored procedure with input parameters, the following stored procedure returns all the products based on the supplied category ID:

Create PROCEDURE dbo.GetProductsByCategoryID
(
   @CategoryID int
)

AS
   SET NOCOUNT ON

Select ProductID, ProductName, QuantityPerUnit, UnitPrice
from Products Where CategoryID = @CategoryID

The Data Access Application Block comes with a DBCommandWrapper class that is essentially a SQL Server wrapper for SqlCommand. The DBCommandWrapper is obtained through the call to the GetStoredProcCommandWrapper() method, which passes in the stored procedure name as an argument. You can use the AddInParameter() method of the DBCommandWrapper object to pass parameters to the stored procedure:

private void btnExecuteStoredProcedure_Click(object sender,
System.EventArgs e)
{
   Database db = DatabaseFactory.CreateDatabase("Northwind");
   DBCommandWrapper dbc = 
   db.GetStoredProcCommandWrapper("GetProductsByCategoryID");
   dbc.AddInParameter("@CategoryID", DbType.Int32, 1);
   DataSet categoriesDataSet =  db.ExecuteDataSet(dbc);
   gridOutput.DataSource = categoriesDataSet.Tables[0].DefaultView;
   gridOutput.DataBind();
}

As you can see, the AddInParameter() method accepts the data type of the parameter as well as the parameter value as arguments.

Data Access Application Block Streamlines Your Data-Access Layer

Executing a Stored Procedure with Output Parameters

To understand how to utilize the Data Access Application Block to execute a stored procedure that returns output parameters, consider a stored procedure that returns product details in the form of output parameters:

CREATE PROCEDURE dbo.GetProductDetails
(
   @ProductID int,
   @ProductName nvarchar(40) output,
   @UnitPrice money output
)

AS
   SET NOCOUNT ON
   Select @ProductName = ProductName, @UnitPrice = UnitPrice
   from Products Where ProductID = @ProductID

As you can see, the stored procedure returns the product name and unit price for a specific product as output parameters. The Click event of the btnRetrieveSingleRow button executes the GetProductDetails using the Data Access Application Block:

private void btnRetrieveSingleRow_Click(object sender,
                                        System.EventArgs e)
{
   Database db = DatabaseFactory.CreateDatabase("Northwind");
   string procName = "GetProductDetails";
   DBCommandWrapper dbc = db.GetStoredProcCommandWrapper(procName);
   dbc.AddInParameter("@ProductID", DbType.Int32, 1);
   dbc.AddOutParameter("@ProductName", DbType.String, 50);
   dbc.AddOutParameter("@UnitPrice", DbType.Currency, 8);
   db.ExecuteNonQuery(dbc);
   lblResult.Text = "Product Name: " + 
   dbc.GetParameterValue("@ProductName") + "<br>";
   lblResult.Text += "Unit Price: "
                  + dbc.GetParameterValue("@UnitPrice") 
   + "<br>";
}

As with the previous examples, the preceding code also specifies the input parameters using the AddInParameter() method of the DBCommandWrapper object. To specify the output parameters, you use the AddOutParameter() method. Once all the parameters are added, executing the stored procedure is very simple and straightforward. Just invoke the ExecuteNonQuery() method of the Database object. Finally, you retrieve the output parameter values returned by the stored procedure through the GetParameterValue() method of the DBCommandWrapper object. Figure 3 shows the output produced by the code.

[DataAccess3.jpg]

Figure 3. Stored Procedure with Output Parameters

Retrieving a Single Scalar Value

The Data Access Application Block provides an ExecuteScalar() method to retrieve a scalar value from a SELECT query or stored procedure, but returns a value of type Object. You can simply cast this return type to the appropriate type you want. For this example, create a procedure named GetProductName that returns the name of the product based on the supplied product ID:

Create PROCEDURE dbo.GetProductName
(
   @ProductID int
)

AS
   SET NOCOUNT ON
   Select ProductName from Products 
Where ProductID = @ProductID

The following is the code required to execute this stored procedure:

private void btnRetrieveSingleItem_Click(object sender,
                                         System.EventArgs e)
{
   Database db = DatabaseFactory.CreateDatabase("Northwind");
   string productName =
      (string) db.ExecuteScalar("GetProductName", 1);
   lblResult.Text = "Product Name: " +  productName;
}

Once you have an instance of the Database class, you can then easily execute the stored procedure using the ExecuteScalar() method to pass in the name of the stored procedure and the parameter value to the stored procedure. The ExecuteScalar() method simply returns the product name as an object, which is then typecast into a string for display purposes.

Executing Code Within a Transaction

The Data Access Application Block, by itself, does not provide any functionalities for executing data-access code within the scope of transactions. However, you can use ADO.NET's transaction support to accomplish this. The Database object exposes a method named GetConnection() that allows you to reference the underlying connection object. Once you have reference to the connection object (an object that inherits from the IDbConnection interface), you can easily create a transaction object (that is, of type IDbTransaction) using the BeginTransaction() method of the connection object. After that, you can either commit or rollback the transaction depending on whether the code in the try..catch block is executed successfully or not:

private void TransactionExample()
{
   Database db = DatabaseFactory.CreateDatabase();
   using (IDbConnection connection = db.GetConnection())
   {
      connection.Open();
      IDbTransaction transaction = connection.BeginTransaction();
      try
      {
         int amount = 1000;
         string srcAccount = "AccountOne";
         string dstAccount = "AccountTwo";
         db.ExecuteNonQuery(transaction, "CreditAccount",
         srcAccount, amount);
         db.ExecuteNonQuery(transaction, "DebitAccount",
         dstAccount, amount );
         transaction.Commit();
      }
      catch
      {
         transaction.Rollback();
      }
      connection.Close();
   }
}

The above code example executes two stored procedures named CreditAccount and DebitAccount within the scope of a single transaction. If both the procedures are executed successfully, the transaction is committed; otherwise, it is rolled back.

Freedom from Tedious Data-Access Details

The EntLib Data Access Application Block allows you to considerably speed your application development by encapsulating the common plumbing code required to execute stored procedures or SQL text commands, specify parameters, and return SqlDataReader, DataSet, XmlReader objects. Using the Data Access Application Block frees you from the tedious details of data access and allows you to concentrate on the business logic in your applications. This results in reduced errors, bugs, and typos in your application as well.

Thiru Thangarathinam has many years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. His certifications include MCAD for .NET, MCSD, and MCP. Thiru is an expert in ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.



Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • VMware vCloud® Government Service provided by Carpathia® is an enterprise-class hybrid cloud service that delivers the tried and tested VMware capabilities widely used by government organizations today, with the added security and compliance assurance of FedRAMP authorization. The hybrid cloud is becoming more and more prevalent – in fact, nearly three-fourths of large enterprises expect to have hybrid deployments by 2015, according to a recent Gartner analyst report. Learn about the benefits of …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds