David Hayden [MVP C#]

The Enterprise Library 2.0 Data Access Application Block can help you with a lot of your data access plumbing in your .NET applications as well as provide a database agnostic solution for .NET applications that need to target multiple databases.  Looking at the source code unveils the same two concrete database providers, SqlDatabase and OracleDatabase, that inherit from the abstract Database class.

Retrieving an untyped DataSet of Orders by CustomerID from the Northwind Database in SQL Server is shown below.  You could always substitute a stored procedure for the inline sql.

public DataSet GetOrdersByCustomerID(string customerID)
{
    Database northwind = DatabaseFactory.CreateDatabase();

    DbCommand command = northwind.GetSqlStringCommand
         ("SELECT [OrderID],[OrderDate] FROM [Orders] WHERE
           [CustomerID] = @CustomerID");

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

    DataSet orders = new DataSet();

    northwind.LoadDataSet(command, orders, "orders");

    return orders;
}

The Enterprise Library 2.0 Data Access Application Block is saving you the keystrokes of adding the plumbing yourself.  The abstract Database class provides a number of methods at your disposal that are your normal ADO.NET 2.0 commands:

• ExcuteDataSet
• ExecuteNonQuery
• ExecuteReader
• ExecuteScalar
• GetSqlStringCommand
• GetStoredProcCommand
• LoadDataSet
• UpdateDataSet
• AddInParameter
• AddOutParameter
• etc...

Working With The Database

There are essentially 3 ways to work with a database:

1. Static Factories
2. Instance Provider Factories
3. Objects Directly

Static Factories

The static factory for the Data Access Application Block is the one you used in Enterprise Library 1.0:

Database northwind = DatabaseFactory.CreateDatabase();

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

The static factories use the configuration information in your Web.Config or App.Config to create an instance of the proper Database class:

<configuration>
  <configSections>
    <section name="dataConfiguration"
          type="Microsoft.Practices.EnterpriseLibrary.
                Data.Configuration.DatabaseSettings,
        Microsoft.Practices.EnterpriseLibrary.Data" />
  configSections>
  <connectionStrings>
    <add
        name="Northwind" 
        providerName="System.Data.SqlClient"
        connectionString="Data Source=(local);
                   Initial Catalog=Northwind;
                   Integrated Security=True" />
  connectionStrings>
  <dataConfiguration defaultDatabase="Northwind"/>
configuration>

Instance Provider Factories

This method is a bit more involved, but gives you much more flexibility in terms of where the DatabaseProviderFactory gets its configuration information:

IConfigurationSource source = new SystemConfigurationSource();

DatabaseProviderFactory factory = new DatabaseProviderFactory(source);

Database northwind = factory.Create("Northwind");

The code above essentially says we want to use the App.config or Web.config as the source of our configuration information and we want to create an instance of Northwind.  For more information on understanding IConfigurationSource, see Enterprise Library 2.0 IConfigurationSource.

SqlDatabase Object Directly

To bypass all that configuration mumbo jumbo you can instantiate the SqlDatabase or OracleDatabase Object directly:

SqlDatabase northwind = new SqlDatabase
   (ConfigurationManager.ConnectionStrings["Northwind"]
         .ConnectionString);

Using Enterprise Library 2.0 Data Access Application Block

I dare say that there isn't much to using the Enterprise Library 2.0 Data Access Application Block.  One you choose the way you wish to get an instance of the Database class, you essentially just invoke the methods of the Database to your bidding.  As shown below, I can read the categories from the Northwind Database and load them into a DataTable.

public DataTable GetCategories()
{
    Database northwind = DatabaseFactory.CreateDatabase();

    DbCommand command = northwind.GetSqlStringCommand(
         "SELECT [CategoryID], [CategoryName] FROM
          Categories");

    DataTable categories = new DataTable();

    using (IDataReader dr = northwind.ExecuteReader(command))
    {
        categories.Load(dr);
    }

    return categories;
}

We can load some Orders by CustomerID as mentioned above, simulate a change, and then update the database with those changes:

Database northwind = DatabaseFactory.CreateDatabase();

// Get Orders By CustomerID
DbCommand command = northwind.GetSqlStringCommand(
      "SELECT [OrderID],[OrderDate] FROM Orders
       WHERE CustomerID = @CustomerID");
northwind.AddInParameter(command, "@CustomerID", DbType.String, "QUICK");
DataSet orders = new DataSet();
northwind.LoadDataSet(command, orders, "orders");

// Simulate Change
DateTime orderDate = (DateTime)orders.Tables[0].Rows[0]["OrderDate"];
orders.Tables[0].Rows[0]["OrderDate"] = orderDate;

// Create Update Command
DbCommand updateCommand = northwind.GetSqlStringCommand(
             "Update [Orders] Set OrderDate = @OrderDate
              WHERE OrderID = @OrderID");
northwind.AddInParameter(updateCommand, "@OrderID",
       DbType.String, "OrderID", DataRowVersion.Current);
northwind.AddInParameter(updateCommand, "@OrderDate",
   DbType.DateTime, "OrderDate", DataRowVersion.Current);

// Execute Update
northwind.UpdateDataSet(orders, "orders", null,
           updateCommand, null, UpdateBehavior.Standard);

Conclusion

The Enterprise Library 2.0 Data Access Application Block can help you with a lot of your data access plumbing in your .NET applications as well as provide a database agnostic solution for .NET applications that need to target multiple databases.

Drinking:  Jasmine Pearls Green Tea

Recent ADO.NET 2.0 Related Tutorials

• SqlBulkCopy - Copy Table Data Between SQL Servers at High Speeds
• Batch Updates in ADO.NET 2.0 for Improved Performance
• Typed DataSet and Sorting Filtering and Searching a DataTable in ADO.NET
• New DataTable Features in ADO.NET 2.0