Using SQL Server Data Access Application Block (DAAB) Part 1 of 2

By: Arshad Ali   |   Comments   |   Related: > Application Development

Problem

Microsoft Enterprise Library provides several reusable software components (application blocks) which help developers simplify common development tasks, minimize development efforts and help address common problems related to performance/optimization/reusability release over release. These components are provided along with source code which developers can use "as-is" or customize per their projects need. Data Access Application Block (DAAB) is one of these components which simplifies the data access mechanism. In this tip series I am going to explain in detail what DAAB is, how it works and what benefits it provides.

Solution

Data Access Application Block (DAAB) provides a consistent data access mechanism to execute both Stored Procedures and inline SQL statements across projects/enterprises. It provides simplified classes to use almost all the features of ADO.NET and hence this simplification means improved developer productivity. These classes allow you to access a database, modify database, etc. Most of the these classes are abstract in nature, meaning you can change your data provider from one to another without actually doing any changes in the code or at least minimize the amount of code the developers might need to modify if they port their application across database providers.

Before writing code using DAAB, let me show you the "Enterprise Library Configuration" tool to create configuration settings to be used with this application block. Once Enterprise Library is installed , go to Start -> Programs -> Microsoft Patterns and Practices -> Enterprise Library 4.1 - October 2008 and click on Enterprise Library Configuration.

A screen as shown below will appear, right click on "Enterprise Library Configuration" node in the left tree view and click on "New Application".

Once an application is created, right click on the "Application Configuration" node in left tree view, then click on New -> Data Access Application Block.

Then beneath Data Access Application Block you will notice one connection string has been created for you whose property values you can modify per your need.  For example you can see I have modified these properties for my specific environment. If required you can create another connection string (if you want to connect to multiple servers/databases) by right clicking on the Connection Strings node and clicking on New.

Once you are done with defining the values for all these properties, you can save these configuration into a file (app.config or web.config) and include it in your project. The steps we followed above would create a configuration XML similar to this.

<configuration>
   <configSections>
   <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
   </configSections>
   <dataConfiguration defaultDatabase="AdventureWorksConnectionString" />
   <connectionStrings>
       <add name="AdventureWorksConnectionString" connectionString="Server=ARSHADALI-LAP\ARSHADALI; Initial Catalog=AdventureWorks; Integrated Security=SSPI" providerName="System.Data.SqlClient" />
   </connectionStrings>
</configuration>

Now lets move forward and learn how to write code utilizing DAAB. Before you actually use the DAAB classes you need to make reference of DAAB assemblies in your project, as you can see here I have made reference to two of these assemblies although there are a few others which may be needed for scenarios.

Microsoft.Practices.EnterpriseLibrary.Data namespace contains the DatabaseFactory class which exposes a static CreateDababase method to create an instance of a Database class. There are two overloaded version of this method; the first one takes no argument and uses the default database configuration from the above created configuration file whereas the second one takes a configuration key as a string to locate the connection string from the configuration file. Database class is an abstract class which has some concrete implementations like GenericDatabase, SqlDatabase, OracleDatabase etc. The type of object created by the CreateDatabase method, depends on the provider name passed to it.

Database class has several instance methods (starts with Execute*) to execute a command and return the data, if any, in the specific format. For example, the below ExecuteReader method executes the command on the database and returns the result set in the data reader.  Likewise there is another method, ExecuteDataSet to execute a command and return the result set in the dataset.

--C# Code Block 1 - DAAB example with DataReader

/*DatabaseFactory.CreateDatabase creates an instance of the database; 
 *first of the two overloaded methods takes no parameter and creates 
 *database instance taking default settings from the configuration whereas 
 *second one takes configuration key and creates instance on that basis */ 
Database _database = DatabaseFactory.CreateDatabase("AdventureWorksConnectionString"); 
string sqlCommand = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee"; 
DbCommand dbCommand = _database.GetSqlStringCommand(sqlCommand); 

using (IDataReader _dataReader = _database.ExecuteReader(dbCommand)) 
{ 
    /* Iterate through all the records of datareader */ 
    while (_dataReader.Read()) 
    { 
        Console.WriteLine(string.Format("{0}, {1}, {2}", _dataReader["FirstName"], 
                _dataReader["LastName"], _dataReader["JobTitle"])); 
    } 

    if (_dataReader != null) 
        _dataReader.Close(); 
} 

You can notice in the code above, none of the classes are specific to any database provider and hence you can switch your database from one provider to another without doing any change in the code itself (as long as you are not using provider specific features), you just need to make the required changes in the configuration file and this piece of code will work fine.

The complete code listing for the above example is provided below.

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Common; using Microsoft.Practices.EnterpriseLibrary.Data; namespace DAL_UsingDAAB { class Program { static void Main(string[] args) { try { /*DatabaseFactory.CreateDatabase creates an instance of the database; *first of the two overloaded methods takes no parameter and creates *database instance taking default settings from the confifuration whereas *second one takes configuration key and creates instance on that basis */ Database _database = DatabaseFactory.CreateDatabase("AdventureWorksConnectionString"); string sqlCommand = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee"; DbCommand dbCommand = _database.GetSqlStringCommand(sqlCommand); using (IDataReader _dataReader = _database.ExecuteReader(dbCommand)) { /* Iterate through all the records of datareader */ while (_dataReader.Read()) { Console.WriteLine(String.Format("{0}, {1}, {2}", _dataReader["FirstName"], _dataReader["LastName"], _dataReader["JobTitle"])); } if (_dataReader != null) _dataReader.Close(); } } catch (Exception exp) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(exp.Message); } } } }

• All examples and demonstrations shown above use Enterprise Library 4.1 which you can download from here. Recently Microsoft released Enterprise Library 5.0 which has several new features and bug fixes, you can download it from here and can find its documentation on MSDN here or you can download the documentation from here.
• DAAB sits on top of ADO.NET and uses its functionalities, so it is not a replacement for ADO.NET.

Next Steps

• Review my last tip on Writing a Data Access Layer (DAL) for SQL Server
• Stay tuned for my next article in this series to learn more about working with a dataset, calling stored procedures and managing database transactions.
• Review Enterprise Library on MSDN.
• Download Enterprise Library 4.1 from here.
• Review my all previous tips.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips