Enterprise Library : Data Access Application Block

Written by John DeVight on 2011-May-12

rating: 0+x

Overview

The Data Access Application Block provides access to the most frequently used features of ADO.NET while encapsulating the Microsoft recommended and proven practices for .NET application development. However, I seem to struggle with Microsoft's documentation on the Data Access Application Block. I end up searching the internet and piecing together what I need. As a result, I have documented the approach that I have taken to implementing the Data Access Application Block here.

Setting up

Download and Install

Download the Enterprise Library from patterns & practices – Enterprise Library CodePlex website. At the time of writing this wiki page, I am using the "5.0 - April 2010" version of the Enterprise Library. Install the Enterprise Library.

Referencing Required Assemblies

Locate and add references to the assemblies required for the Data Access Application Block. The default location for the assemblies is: "C:\Program Files\Microsoft Enterprise Library 5.0\Bin".

If you have a web application where data access is done in a separate class library, then you will need to reference the assemblies in the class library.

Required Assemblies:

  • Microsoft.Practices.EnterpriseLibrary.Common
  • Microsoft.Practices.EnterpriseLibrary.Data
  • Microsoft.Practices.ServiceLocation

Configuring Database Settings

In the web.config add the dataConfiguration section to /configuration/configSections:

<configSections>
  <section name="dataConfiguration" 
    type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
    requirePermission="true" />
</configSections>

The section name identifies the section in the web.config where the database settings are located. The only setting I use is the default database setting that identifies the default connection string to use if a database is not identified by name in the code.

Here is an example:

<dataConfiguration defaultDatabase="MyDatabase" />

Last, the connection string(s) are specified in the web.config.

Here is an example of specifying 2 SQL Server databases:

<connectionStrings>
  <add name="MyDatabase" 
    connectionString="Data Source=MyServer;Initial Catalog=MyDB;User ID=sa;Password=password" 
    providerName="System.Data.SqlClient" />
  <add name="MyOtherDatabase" 
    connectionString="Data Source=MyServer;Initial Catalog=MyOtherDB;User ID=sa;Password=password" 
    providerName="System.Data.SqlClient" />
</connectionStrings>

Note: All configuration settings shown here appear as child elements of <configuration>

Selecting Information From a Database

To select data from a database, the following needs to happen:

  • Reference the appropriate namespaces.
  • Create an instance of a Database class using the DatabaseFactory.
  • Create a connection to the database.
  • Create a command to execute.
  • Execute a reader to get the data.

Reference the Appropriate Namespaces

The namespaces to reference are:

using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

Additionally, if you are connecting to a SQL Server database you will need the following namespace:

using System.Data.SqlClient;

Create an Instance of a Database class using the DatabaseFactory

The following code will create an instance of a Database using the default connection string:

Database db = DatabaseFactory.CreateDatabase();

To create an instance of a Database using the name of a specific connection string:

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

Create a Connection to the Database

The following code will create a connection to the database:

DbConnection cn = db.CreateConnection();
cn.Open();

Create a Command to Execute

Here is an example of calling a stored procedure and passing in parameters:

DbCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spMyStoredProcedure";
cmd.Parameters.Add(new SqlParameter("@firstParam", "paramValue") { SqlDbType = SqlDbType.VarChar });

Execute a Reader to get the Data

Here is an example of executing the command and getting a SqlDataReader with the data from the database:

IList<Document> documents = new List<Document>();
SqlDataReader dr = cmd.ExecuteReader() as SqlDataReader;
while (dr.Read())
{
    documents.Add(new Document
    {
        Id = dr.GetInt32(0),
        Name = dr.GetString(1)
    });
}

Putting it All Together

Since I broke up the code into blocks to demonstrate the different parts involved in getting the data from the database, I left out the use of the using statement. It is good practice to use the using statement for any class that implements the IDisposable interface to ensure that objects get deallocated in a timely manner.

Here is everything put together:

IList<Document> documents = new List<Document>();
Database db = DatabaseFactory.CreateDatabase();
using (DbConnection cn = db.CreateConnection())
{
    cn.Open();
    using (DbCommand cmd = cn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "spMyStoredProcedure";
        cmd.Parameters.Add(new SqlParameter("@firstParam", "paramValue") { SqlDbType = SqlDbType.VarChar });
        using (SqlDataReader dr = cmd.ExecuteReader() as SqlDataReader)
        {
            while (dr.Read())
            {
                documents.Add(new Document
                {
                    Id = dr.GetInt32(0),
                    Name = dr.GetString(1)
                });
            }
        }
    }
}

Stay tuned for more….

Support ASP.NET Wiki

If you like this page, click on the "Share on" links in the wikidot toolbar at the top of the page to share it with your friends.

Comments

Add a New Comment
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License