Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2010-06-30   |   Comments   |   Related Tips: More > 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".

Data Access Application Block (DAAB) is one of these components which simplifies the data access mechanism

 the "Enterprise Library Configuration" tool

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

click on the "Application Configuration" node in left tree view

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.

one connection string has been created for you whose property values you can modify per your need

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.

 lets move forward and learn how to write code utilizing DAAB

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.

Note:
  • 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


Last Updated: 2010-06-30


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools