David Hayden [MVP C#]

I have mentioned a couple of other Enterprise Library Application Blocks so far:

• Configuration Application Block
• Caching Application Block

Probably the most popular application block is the Data Access Application Block (DAAB).  For detailed information on this block, you can read the documentation.  However, in general, DAAB simplifies the task of reading and writing information to a database by providing a simple API and a library that provides access to the most frequently used features of ADO.NET.  It essentially handles the details of connection management, etc. and allows you to read and write data to the database with fewer lines of code and in a consistent manner.

As with all the application blocks in the Enterprise Library (download), you need to really familiarize yourself with the Enterprise Library Configuration Tool that is part of the download.  It helps create the necessary configuration files that allow each block to run accordingly.  Shown below is a snippet of the configuration tool allowing me to customize the configuration files for my local Sql Server and the Northwind Database.

As I mentioned before, it looks involved and outputs a rather involved configuration file, but it took me all of about 5 minutes to create the necessary configuration files.  Other than the name of the database and server, you can accept most of the defaults which pop-up automatically as soon as you say you want to add the Data Access Application Block.  Aside from the information that needs to go in your app.config or web.config, here is the configuration file for the DAAB:

<?xml version="1.0" encoding="utf-8"?>
<dataConfiguration>
  <xmlSerializerSection type="Microsoft.Practices.EnterpriseLibrary.Data.
            Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data,
            Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
    <enterpriseLibrary.databaseSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" defaultInstance="Northwind"
            xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data">
      <databaseTypes>
        <databaseType name="Sql Server" type="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase,
            Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral,
            PublicKeyToken=null" />
      </databaseTypes>
      <instances>
        <instance name="Northwind" type="Sql Server" connectionString="Northwind" />
      </instances>
      <connectionStrings>
        <connectionString name="Northwind">
          <parameters>
            <parameter name="database" value="Northwind" isSensitive="false" />
            <parameter name="Integrated Security" value="True" isSensitive="false" />
            <parameter name="server" value="local" isSensitive="false" />
          </parameters>
        </connectionString>
      </connectionStrings>
    </enterpriseLibrary.databaseSettings>
  </xmlSerializerSection>
</dataConfiguration>

The file basically just specifies that I am using Sql Server and the connection string to get to the server and the Northwind Database.

So that is really not that interesting.  The key to the block is simplifying the use of ADO.NET.  Let's grab a customer from the Customer Table in Northwind.  I will use the DataReader, but you could certainly pull this off with a DataSet as well.  I will also use a querystring, but you could also use a Stored Procedure as well.  To each his own on how you prefer to access data from the database.  Shown below is a rough outline of the specific commands to pull this off using the DAAB:

Database db = DatabaseFactory.CreateDatabase();

string query = "Select * from Customers Where CustomerID = @CustomerID";

DBCommandWrapper command = db.GetSqlStringCommandWrapper(query);

command.AddInParameter("@CustomerID", DbType.String, id);

using (IDataReader reader = db.ExecuteReader(command))
{
    // Do Something...
}

Everything starts out with DatabaseFactory.CreateDatabase().  If you don't specify a named instance of a database (as I did not), it just grabs the default database in the configuration file.  The DAAB comes with this DBCommandWrapper class that is essentially a wrapper for SqlCommand for Sql Server.  I pass it my querystring as well as add the value of the @CustomerID parameter.

I wanted to use a DataReader, so I call ExecuteReader on the database (db), which passes back an IDataReader.  At this point, I do whatever I want with the reader.  Realize at this point the connection to the database is open, so you want to do your business and get the connection closed.  In my case, I wrapped the reader in a using block, which essentially mimics a try/finally block and calls dispose on the reader in finally.  Calling dispose on the reader will close the reader which causes the open connection to close due to its use of the CommandBehavior.CloseConnection.

That is it.  You don't have to open the connections or create the command objects and manage it all.  DAAB does it for you.  Let's complete the code to grab a particular customer from the Customers Table.

We don't have to do this, but let's create a Customer Class.  Rather than passing the DataReader through another layer, we are going to pass up a Customer Object to the "UI" layer.  Here is a bare bones Customer Class with only a few pieces of information:

public class Customer
{
    private readonly string _id;
    private string _name;
    private string _title;

    public string ID
    {
        get { return _id; }
    }

    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }

    public string Title
    {
        get { return _title; }
        set { _title = value; }
    }

    public Customer (string id, string name, string title)
    {
        _id = id;
        _name = name;
        _title = title;
    }

    public override string ToString()
    {
        return String.Format("{0} {1}, {2}", _id, _name, _title);
    }
}

Now we need to create a class, called CustomerData, that will essentially read in our customer using DAAB and simply fill and return the Customer Class.  Here is something quick:

public class CustomerData
{
    public CustomerData() {}

    public Customer FetchByID(string id)
    {
        Database db = DatabaseFactory.CreateDatabase();

        string query = "Select * from Customers Where CustomerID = @CustomerID";

        DBCommandWrapper command = db.GetSqlStringCommandWrapper(query);

        command.AddInParameter("@CustomerID", DbType.String, id);
        
        using (IDataReader reader = db.ExecuteReader(command))
        {
            if (reader.Read())
            {
                return new Customer(reader.GetString(0), reader.GetString(2), reader.GetString(3));
            }
            else
                return null;
        }
    }
}

And here is the code that tests it out:

public class TestClass
{
    public static void Main()
    {
        CustomerData customerData = new CustomerData();
        Customer customer = customerData.FetchByID("ALFKI");
        
        if (customer != null)
        {
            Console.WriteLine(customer);
            Console.ReadLine();
        }
    }
}

If you don't like the idea of using a DataReader or a custom class, you can certainly pass a DataSet.  I created this just for the sake of showing something a bit different from the examples provided as part of the Enterprise Library.  The key here is the little amount of code you have to write as well as how there is nothing specific to Sql Server.

For more information on the DAAB, click here.