Microsoft Enterprise Library DAAB

Introduction

Microsoft Enterprise Library Data Access Application Block (DAAB) is an assembly that encapsulates the logic used to perform common enterprise-level data access tasks.

Using the DAAB eliminates the need to keep writing the same data access tasks for each new or revised application, so you can spend your time more productively.

The DAAB solves the problem of having to repeatedly write code for common data access tasks, and adapts the application code to be interoperable across different data sources.

This article explains why using DataDirect ADO.NET data providers with DAAB makes this technology even better and includes the information and sample code you need to install and configure DAAB to work with DataDirect Connect for ADO.NET and your application, including SSL and Kerberos authentication enterprise security features.

Back to top

DataDirect ADO.NET Makes Microsoft DAAB Better

The DataDirect Connect for ADO.NET providers include a combination of interoperable features such as SQL Leveling that allow DAAB to be a "true" block where applications do not have to compensate with different blocks for different databases.

The DAAB provides an API driven interoperability layer on top of ADO.NET, which in a programmatic sense offers an excellent basis to abstract your applications away from some of the database specifics that may be exposed by ADO.NET.

Also consider DataDirect's SQL Leveling features as a critical aspect in building a truly robust data access block to your applications, so applications may program to a single data access block and not have to take account of database SQL differentials.

Back to top

Required Components

  • Download the Microsoft Enterprise Library 3.1 (May 2007)
  • 3.1 SP 1or higher for ADO.NET Download DataDirect Connect
  • Microsoft .NET Framework 2.0, 3.0 or 3.5
  • Visual Studio 2005 SP1
Back to top

Install DataDirect Connect for ADO.NET 3.1 SP1 or Higher

  1. Install the DataDirect Connect for ADO.NET drivers using the instructions in the DataDirect Connect for ADO.NET Installation Guide.
  2. Verify connectivity to your database server using the steps detailed in the Quick Start Guides.
Back to top

Install Microsoft Data Access Application Blocks (DAAB)

  1. Install the DataDirect Connect for ADO.NET drivers using the instructions in the DataDirect Connect for ADO.NET Installation Guide.
  2. Verify connectivity to your database server using the steps detailed in the Quick Start Guides.
Back to top

Configure DAAB

Add a New DAAB Entry
  1. Launch the Enterprise Library Configuration Tool:
    Start > Program Files > Microsoft Patterns & Practices >
    Enterprise Library 3.1 May 2007 > Enterprise Library Configuration
  2. Right-click Enterprise Library Configuration and select New Application.
  3. Right-click Application Configuration,then select
    New / Data Access Application Block. The Enterprise Library Configuration window appears.


    Click Graphic for a full-sized image
  4. In the Name field, enter a name for the DAAB, for example, MyOracle.
  5. In the ConnectionString field, enter a connection string.
    Refer to the Quick Start Guide to get a working connection string.
  6. In the ProviderName field, identify the DataDirect data provider:
    • For DB2, enter DDTek.DB2
    • For Oracle, enter DDTek.Oracle
    • For SQL Server, enter DDTek.SQLServer
    • For Sybase, enter DDTek.Sybase
  7. Right-click Customer Provider Mappings and select New / Provider Mappings:


    Click Graphic for a full-sized image
  8. In the Name field, type the DAAB name entered in Step 3 (MyOracle).
  9. In the TypeName field, choose the browse (...) button. Click “Load an Assembly”
    and navigate to install_dir\Enterprise Libraries\Src\CS\ProviderName\bin\Debug.
  10. Select the file name, for example, DDTek.EnterpriseLibrary.Data.Oracle.dll, and then click Open.

    Click Graphic for a full-sized image
  11. Leave the Enterprise Library Configuration window open for now.
    Do not save this configuration until you complete the following section.
Back to top

Add the DAAB to Your Application

  1. Open your project with Visual Studio 2005 SP1.
  2. In solution explorer, right click the project name and choose Add Reference.
  3. In the Add Reference dialog, select Enterprise Library Shared Library and click OK.
  4. Right-click the project again and select Add Reference.
  5. In the Add Reference dialog, select Enterprise Library Data Access Application Block, and click OK.
  6. Add the following directive to your C# source code:

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

  7. Rebuild the solution to ensure that the new dependencies are functional.
  8. Determine the output Debug or Release path location of your current solution, and switch back to the Enterprise Library Configuration window (see "Add a New DAAB Entry").
  9. Right-click the connection string under the Application Configuration node and select Save Application.
  10. Navigate to the Debug or Release output directory of your current solution, and locate the .exe file of the current solution, for example, MyApp.exe.
  11. Click the file name once, and add .config to the name, for example, MyApp.exe.config.
  12. Select Save Application As from the file menu. Ensure that Save as Type 'All Files' is selected and choose the file specified in the previous step. Then select Save.
  13. Using File Explorer, copy the DDTek.EnterpriseLibrary.Data.XXX.dll from the DataDirect DAAB directories (where XXX indicates the data source).
  14. Place a copy of this DLL into either the Debug or Release output directory of your current solution.
Back to top

DataDirect Providers Extend Enterprise Security to DAAB

Configure SSL-enabled DAABs
  1. Launch the Enterprise Library Configuration Tool:
    Start > Program Files > Microsoft Patterns & Practices >
    Enterprise Library 3.1 May 2007 > Enterprise Library Configuration
  2. Right-click Enterprise Library Configuration and select New Application.
  3. Right-click Application Configuration, then select New / Data Access Application Block. The Enterprise Library Configuration window appears.
  4. Modify the Connect String to enable SSL as documented in the DataDirect Connect for ADO.NET User's Guide. A sample Oracle connection string may look as follows:

    Host=ntsl2003a;Port=1521;SID=ORCL1252;
    User ID=SCOTT;Password=TIGER;Encryption Method=SSL

Configure DAABs for Kerberos Authentication
  1. Launch the Enterprise Library Configuration Tool:
    Start > Program Files > Microsoft Patterns & Practices >
    Enterprise Library 3.1 May 2007 > Enterprise Library Configuration
  2. Right-click Enterprise Library Configuration and select New Application.
  3. Right-click Application Configuration, then select New / Data Access Application Block.
    The Enterprise Library Configuration window appears.
  4. Modify the Connect String to enable SSL as documented in the DataDirect Connect for ADO.NET User's Guide.
    A sample Oracle connection string may look as follows:

    Host=ntsl2003a;Port=1521;SID=ORCL1252;Encryption
    Method=Kerberos

Back to top

Use the DAAB in Application Code

Now that you have configured the DAAB, you can build applications on top of this DAAB.

Establish the Database Connection

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Globalization;
namespace DAAB_Test_App_1
{
class Program
{
static void Main(string[] args)
{
Database database = DatabaseFactory.CreateDatabase
     ("MyOracle");
/*
* At this point, we have established the database connection
* configured above, and we can start making
     calls into the DAAB
* using the following code samples adapted from Data Access
* Quick Start installed with Enterprise Library.
*/
}
}
}


Use a DataSet to Retrieve Multiple Rows

/*
* Stored Procedure DDL for sample below:
*
* CREATE Procedure GetProductsByCategory
* (
* @CategoryID int
* )
* AS
* SELECT ProductID, ProductName, CategoryID, UnitPrice,
* LastUpdate
* FROM Products
* WHERE CategoryID = @CategoryID
*
*/
string sqlCommand = "GetProductsByCategory";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
// DataSet that will hold the returned results.
DataSet productDataSet = null;
productDataSet = db.ExecuteDataSet(dbCommand);

Use a DbDataReader to Retrieve Multiple Rows of Data

string sqlCommand = "Select CustomerID, Name, Address, City,
Country, PostalCode " + "From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
using (IDataReader dataReader =db.ExecuteReader(dbCommand))
{ StringBuilder readerData = new StringBuilder();
while (dataReader.Read())
     {
     // Get the value of Name column in the DbDataReader.  
     readerData.Append(dataReader["Name"]);
     readerData.Append(Environment.NewLine);
     }
     // Close the reader, which will cause the
     connection to be closed as well.
     if (dataReader != null)   
     dataReader.Close();
}


Execute a Command and Access Output Parameters

/*
* Stored Procedure DDL for sample below:
*
* CREATE PROCEDURE GetProductDetails
* @ProductID int,
* @ProductName nvarchar(40) OUTPUT,
* @UnitPrice money OUTPUT,
* AS
* SELECT @ProductName = ProductName,
* @UnitPrice = UnitPrice,
* FROM Products
* WHERE ProductID = @ProductID
*
*/
int productID = 1;
string sqlCommand = "GetProductDetails";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID);
// Output parameters specify the size of the return data.
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);
db.ExecuteNonQuery(dbCommand);
string results = string.Format(CultureInfo.CurrentCulture,
"{0}, {1}, {2:C} ",
db.GetParameterValue(dbCommand, "ProductID"),
db.GetParameterValue(dbCommand, "ProductName"),
db.GetParameterValue(dbCommand, "UnitPrice"));


Execute a Command and Access a Single-Item Result

/*
* Stored Procedure DDL for sample below:
*
* CREATE PROCEDURE GetProductName
* @ProductID int
* AS
* SELECT ProductName
* FROM Products
* WHERE ProductID = @ProductID
*
*/
int productID = 1;
string sqlCommand = "GetProductName";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, productID);
string productName = (string)db.ExecuteScalar(dbCommand);


Perform Multiple Updates within a Transaction

/*
* Stored Procedure DDL for sample below:
*
* CREATE PROCEDURE credit
* @AccountNo CHAR(20),
* @Amount SMALLMONEY
* AS
* INSERT Credits
* VALUES
* (@AccountNo, @Amount)
* GO
* 
* CREATE PROCEDURE debit
* @AccountNo CHAR(20),
* @Amount SMALLMONEY
* AS
* INSERT Debits
* VALUES
* (@AccountNo, @Amount)
* GO
*/
int sourceAccount = 0073735963;
int destinationAccount = 15092;
int transactionAmount = 485;
DbCommand creditCommand = db.GetStoredProcCommand("CreditAccount");
db.AddInParameter(creditCommand, "AccountID", DbType.Int32,
sourceAccount);
db.AddInParameter(creditCommand, "Amount", DbType.Int32,
transactionAmount);
DbCommand debitCommand = db.GetStoredProcCommand("DebitAccount");
db.AddInParameter(debitCommand, "AccountID", DbType.Int32,
destinationAccount);
db.AddInParameter(debitCommand, "Amount", DbType.Int32,
transactionAmount);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
// Credit the first account.
db.ExecuteNonQuery(creditCommand, transaction);
// Debit the second account.
db.ExecuteNonQuery(debitCommand, transaction);
// Commit the transaction.
transaction.Commit();
}
catch
{
// Roll back the transaction.
transaction.Rollback();
}
connection.Close();
//Enter code to indicate transaction succeeded
}


Update a Database using a Dataset Fetched with Interoperable SQL Leveling Syntax from DataDirect

/*
* CREATE PROCEDURE AddProduct
* (
* @ProductName nvarchar(50),
* @CategoryID int,
* @UnitPrice money
* )
* AS
* INSERT INTO Products (ProductName, CategoryID, UnitPrice)
* VALUES (@ProductName, @CategoryID, @UnitPrice)
*
* SELECT ProductID, ProductName, CategoryID, UnitPrice
* FROM Products
* WHERE ProductID = SCOPE_IDENTITY()
* GO
*
* CREATE PROCEDURE DeleteProduct
* (
* @ProductID int
* )
* AS
*
* DELETE Products
* WHERE ProductID = @ProductID
* GO
*
* CREATE PROCEDURE UpdateProduct
* (
* @ProductID int,
* @ProductName nvarchar(50),
* @LastUpdate datetime
* )
* AS
*
* UPDATE Products
* SET ProductName = @ProductName
* WHERE ProductID = @ProductID AND LastUpdate = @LastUpdate
*
* IF @@ROWCOUNT > 0
* -- This statement is used to update the DataSet if
changes are done on the updated record
(identities, timestamps or triggers )
* SELECT ProductID, ProductName, CategoryID, UnitPrice
* FROM Products
* WHERE ProductID = @ProductID
* GO
*
*/
DataSet productsDataSet = new DataSet();
//Fetch records where LastUpdate is newer than March 3, 2008
using interoperable SQL Leveling Syntax that works with
DAABs across multiple databases.
string sqlCommand = "Select ProductID, ProductName, CategoryID,
UnitPrice, LastUpdate " +"From Products where
LastUpdate > {d' 2008-03-03'}";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
string productsTable = "Products";
// Retrieve the initial data.
db.LoadDataSet(dbCommand, productsDataSet, productsTable);
// Get the table that will be modified.
DataTable table = productsDataSet.Tables[productsTable];
// Do some work on the DataSet. In this example,
we'll add a new product to existing
DataSet and then update the first fetched row.
DataRow addedRow = table.Rows.Add(new object[]
{ DBNull.Value, "New product", 11, 25 });
table.Rows[0]["ProductName"] = "Modified product";
DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");
db.AddInParameter(insertCommand, "ProductName", DbType.String,
"ProductName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32,
"CategoryID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency,
"UnitPrice", DataRowVersion.Current);
DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct");
db.AddInParameter(deleteCommand, "ProductID", DbType.Int32,
"ProductID", DataRowVersion.Current);
DbCommand updateCommand = db.GetStoredProcCommand("UpdateProduct");
db.AddInParameter(updateCommand, "ProductID", DbType.Int32,
"ProductID", DataRowVersion.Current);
db.AddInParameter(updateCommand, "ProductName", DbType.String,
"ProductName", DataRowVersion.Current);
db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime,
"LastUpdate", DataRowVersion.Current);
//Persist modified DataSet to table
int rowsAffected = db.UpdateDataSet(productsDataSet, "Products",
insertCommand, updateCommand, deleteCommand,
Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);
Back to top

Useful Links

Back to top
ADO.NET TUTORIAL
Microsoft Enterprise Library DAAB

View all Tutorials for:

ADO.NET

Connect any application to any data source anywhere

Explore all DataDirect Connectors

Need additional help with your product?

Get Customer Support