Microsoft patterns & practices: The Enterprise Library

What are Microsoft patterns & practices?

Microsoft patterns & practices provide scenario-specific recommendations illustrating how to design, develop, deploy, and operate architecturally sound applications for the Microsoft .NET platform. They offer deep technical guidance based on real-world experience that goes far beyond typical white papers and sample applications to help you quickly deliver sound solutions. Patterns & Practices provide proven architectures, production quality code, and recommended engineering practices. The technical guidance is created, reviewed, and approved by Microsoft architects, engineering teams, consultants, product support engineers, and by Microsoft partners and customers. The result is a thoroughly engineered and tested set of recommendations that can be followed with confidence when building your applications.

Microsoft patterns & practices are proven practices to help you generate predictable results.

Refer to the Following Link for a brief on the Enterprise Library

http://www.c-sharpcorner.com/Code/2005/April/EnterpriseLibrary.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

NOTE: The Attached Demo contains the Application Code and the Database backup (Entlibdb) file.

The following article covers details on the Data Access Application Block:

Enterprise Library Data Access Application Block

Introduction to the Enterprise Library Data Access Application Block

Most applications and services must access a data store at some point during a business process. When working with data, you need to determine: the data store you are using, the design of the components used to access the data store, the format of the data passed between components, and the programming model the data requires.

The Data Access application block provides

• A simple and efficient way of working with commonly used databases
• Transparency when developing for multiple types of databases
• A way to place an indirection between a logical database instance and a physical database instance
• An easy way to adjust and validate the database configuration settings

NOTE: The latest release of Data Access Application Block is included as a part of the patterns & practices Enterprise Library, June 2005.

What the Data Access Application Block is?

• A component of Enterprise Library which reduces the amount of custom code that you need to create, tests, and maintain when building data access layers in .NET applications.
  
• Simplifies development tasks that implement common data access functionality i.e. 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.
  
• A block that applications can use to
  
  • Read data (for display purposes, process data etc.),
  • Transfer Data through business logic layers and
  • Save updated changes to the database.
  
  
• A configurable component that enables developers to use their application with different database configurations without having to recompile their code. The developers can specify the Real database name and location in a Configuration file tied to the application.
  
• A block that includes support for both stored procedures and in-line SQL, and common housekeeping tasks such as managing connections and creating and caching parameters are encapsulated in the application block's methods.
  
• Provides a kind of "wrapper" around ADO.NET thereby exposing access to the most often used features thereby abstracting the logic from the developer.
  
• Facilitates development when porting applications to different types of databases. Support for MS SQL Server, Oracle and DB2.

Scenarios where the Data Access Application Block can be used

The Data Access Application Block can be used for the following scenarios:

• In order to retrieve multiple rows of data ,a DataReader or a DataSet can be used
• Update data contained within a DataSet to the database.
• To retrieve output parameters or a scalar value, a Command object can be executed.
• Execute multiple operations encapsulated within a Transaction.
• Retrieve XML based information from SQL Server.

DataBase Transparency

The abstract base Database class

The application block facilitates the code to remain uniform across multiple database servers, including Microsoft SQL Server, Oracle, and DB2 by using the Abstract Base Class - Database. The pattern implemented here is along the lines of a Factory Design Pattern. The database-specific object is created with a factory, which uses configuration information to determine the type of object to create.

This abstract class defines a common interface and provides much of the implementation for the data access methods. Applications written for one type of database-such as SQL Server-look the same as applications written for another type of database, such as Oracle.

A abstract base class DbCommandWrapper provides support to the Database object to accept a common type with specific database types provide their own implementations

Data Access Application Block Dependencies

The application blocks that are provided with the Enterprise Library are designed to be used in conjunction with each other. Sometimes, the application blocks have dependencies on other application blocks and code that is included with the Enterprise Library. The Data Access Application Block has the following dependencies:

The Configuration Application Block. The Data Access Application Block uses the Configuration Application Block to read its configuration information.

Common library functionality, such as instrumentation. It provides various functions for exposing events and data used for system management.

By default, the application block uses XML files to store configuration information. The recommended way to modify this information is to use the Enterprise Library Configuration Console.

You can use the Enterprise Library Configuration Console to encrypt and protect the database configuration information containing connection strings. Connection strings may contain passwords, network addresses, and other sensitive information. To learn more about encrypting configuration settings, see the documentation for the Enterprise Library Configuration Application Block.

System Requirements

To develop applications using the Data Access Application Block, you need the following:

• Microsoft Windows 2000, Windows XP Professional, or Windows Server 2003 operating system
• Microsoft .NET Framework 1.1
• Microsoft Visual Studio .NET 2003
• A database server running SQL Server 7.0 or later, Oracle 9i, or DB2 (If you are using a DB2 database, you also need the IBM UDB 8.1.2 data provider)

Configuring the Data Access Application block

Enterprise Library Configuration Manager

The Database configurations can be set using the Enterprise Library Configuration Manager. The first step involves creating a new application via the Enterprise Library Manager. The Data Access Application Block node can be added by clicking the "New -> Data Access Application Block" on the Application Node Name.

The following 3 sub nodes are created when a DAAB is added.

• Connection Strings
  
  • Represents the attributes that make up the connection string
• Database Types
  
  • Represents database type - i.e. the vendor
  • Support for MS SQL Server, Oracle and DB2,
• Database Instances
  
  • Represents the database instance names. These names will be used in the application to reference the database to use.
  • A Database instance represents the Connection String and Database Type combination.

Configuring the Connection String

• Add new Connection String in the Connection String node - this creates by default 3 parameters
  
  • Database -< Represents the database name
  • Integrated security -< Represents whether to use SSPI or not>
    Note: if SSPI is set to False the following two parameters need to be set
    • user -< SQL user identity name>
    • password -< SQL user password>
  • Server -< Represents the database server>.

Note: Additional parameters that are added get concatenated to the connection string

Database Types Configuration

On Right Clicking the Database Type configuration node, one can add New Database. The Type name attribute of the New Database Type needs to be set to the appropriate assembly.

Note: The following database types are supported:

• Oracle
• SQL Server and
• DB2

Configuring the Database Instances

An instance in this node must specify the combination of Database Type and Connection String. Invocation to this Instance name will result in the correct database object being retrieved.

Location of Configuration Files

The Configuration Console creates a configuration file for each application block. The application configuration file (typically named either App.config or Web.config) contains information about which application blocks the application is configured to use and the path of those application blocks' configuration files. By default, the path of each application block's configuration file is the current directory of the application configuration file.
When you use the Configuration Console to open the App.config file and change configuration settings, the modified application block configuration files are saved to the same directory as the App.config file. However, each time a project successfully builds, Visual Studio automatically copies the App.config file to the target directory and renames it to executable .exe.config. This means you must also copy the relevant application block configuration files to the target directory.

You can copy the application block configuration files to the target directory either manually or use the Visual Studio Build Events property to have them be copied each time the project is built. To make sure all block configuration files are copied to the proper directory, use the following command as the post-build event command line:

copy "$(ProjectDir)*.config" "$(TargetDir)"

Building the Application

The Data Access Layer:
This Layer is primarily responsible for interaction with the database for accessing or persistence of data. It represents that gateway to the database. The methods in this layer are meant to abstract the details of data access logic and provide an interface that will be used by the consumer of this layer. Following are the steps to build the Data Access Layer.

NOTE: The Database configurations settings must be performed prior to the following steps.

• Step 1: Adding References (Project level)
  
  References to the following assemblies need to be added to the project that houses the Data Access Layer.
  
  • .Microsoft.Practices.EnterpriseLibrary.Data
  • .Microsoft.Practices.EnterpriseLibrary.Configuration
  • .System.Data;
    
    Note: The "Enterprise Library" default installation path is C:\Program Files\Microsoft Enterprise Library\bin. The assemblies will need to be referenced from this location.
    
• Step 2: Namespaces to include (Class Level)
  
  The following namespaces need to be included after the references have been set
  
  using Microsoft.Practices.EnterpriseLibrary.Data;
  
• Step 3: Building the Application Code
  
  Creating the Database object: The Abstract Database factory class provides a static method for the creation of the Database object.
  
  //Use the NorthWindDB Database Instance (as configured via the Enterprise Library Configuration Manager).
  Database db = DatabaseFactory.CreateDatabase("NorthwindDB_SQL");
  //"NorthwindDB_SQL" represents the Database instance to connect to.
  
  //NOTE: To Use the default Database Instance
  //Database db = DatabaseFactory.CreateDatabase();
  
  //NOTE: To Use a specific Database object i.e. MS SQL Server,
  //SqlDatabase db = DatabaseFactory.CreateDatabase("NorthWindDB") as SqlDatabase;
  
• Preparing the Command Wrapper: This wrapper supports two types
  
  
  • SQL Queries
  • Stored Procedures
  
  
  //Preparing the SQL query.
  string sqlCommand = "Select EmployeeID, LastName + FirstName Name From Employees";
  DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
  
  //Preparing the Stored procedure.
  string sqlCommand = "CustOrderHist";
  DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
  
  TIP: Database Connections are expensive resources. They should always be acquired late and released early.
  
  Note: In the code, the developer writes references the database by name. The actual database type and connection string are stored in the configuration.
  
• Query Execution and Results:
  
  Following are the operations provided through abstract base Database class. There are multiple overloads provided for each of them
   

  ExecuteReader()	Executes the command and returns an IDataReader through which the result can be read. It is the responsibility of the caller to close the connection and reader when finished.
  ExecuteDataSet()	Execute a command and returns the results in a new DataSet.
  LoadDataSet()	Execute the command and add a new DataTable to the existing DataSet.
  ExecuteNonQuery()	Executes the command and returns the number of rows affected.
  ExecuteScalar()	Executes the command and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
  UpdateDataSet()	Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the DataSet.

THE ExecuteReader function (Code Snippet)

// Use the NorthWindDB Database Instance
Database db = DatabaseFactory.CreateDatabase("NorthwindDB_SQL");
//Prepare the SQL query.
string sqlCommand = "Select EmployeeID, LastName + FirstName Name From Employees";
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
IDataReader dataReader=null;
//NOTE: The EmployeeDE represents a Data Entity class and the EmployeeDECollection class represents the collection for instances of the EmployeeDE class.
EmployeeDECollection employeeCollection = new EmployeeDECollection(); //
try
{
dataReader = db.ExecuteReader(dbCommandWrapper);
while (dataReader.Read())
{
EmployeeDE employeeDE= new EmployeeDE();
employeeDE.EmployeeID = Convert.ToString(dataReader["EmployeeID"]);
employeeDE.Name= Convert.ToString(dataReader["Name"]);
employeeCollection.Add(employeeDE);
}
}
finally
{
// Close the reader, which will cause the connection to be closed as well
if (dataReader != null) dataReader.Close();
}
return employeeCollection;

THE ExecuteDataSet function (Code Snippet)

THE ExecuteDataSet and LoadDataSet function (Code Snippet)

// Use the NorthWindDB Database Instance
Database db = DatabaseFactory.CreateDatabase("NorthwindDB_SQL");
//Set the SQL Stored proc.
string sqlCommand = "CustOrderHist";
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
dbCommandWrapper.AddInParameter("@CustomerID",DbType.String,"ALFKI");
DataSet ds = db.ExecuteDataSet(dbCommandWrapper);
//Prepare multiple SQL queries.
sqlCommand = "select * from products;select * from region;select * from categories;";
dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
string[] strTableNames= new string[3];
strTableNames[0] = "Products";
strTableNames[1] = "Regions";
strTableNames[2] = "Categories";
db.LoadDataSet(dbCommandWrapper,ds,strTableNames);

THE ExecuteNonQuery function (Code Snippet)

// Use the NorthWindDB Database Instance
Database db = DatabaseFactory.CreateDatabase("NorthwindDB_SQL");
//Set the SQL Query.
string sqlCommand = "Select OrderID, ProductID From OrderDetails where productID>100";
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
db.ExecuteNonQuery(dbCommandWrapper);

THE ExecuteScalar function (Code Snippet)

THE UpdateDataSet function (Code Snippet)

//prepare the Dataset
DataSet ds = new DataSet();
DataTable empTable = new DataTable("Employees");
empTable.Columns.Add("FirstName",typeof(String));
empTable.Columns.Add("LastName",typeof(String));
for(int i=0;i<10;i++)
{
// Add a new product to existing DataSet
DataRow addedRow = empTable.Rows.Add(new object[] {"FirstName" +i, "LastName" +i});
}
ds.Tables.Add(empTable);
.....
// Use the EntlibDB Database Instance
Database db = DatabaseFactory.CreateDatabase(DATABASE_NAME);
// Establish the Insert, Delete, and Update commands
DBCommandWrapper insertCommandWrapper = db.GetStoredProcCommandWrapper("usp_InsertEmployee");
insertCommandWrapper.AddInParameter("@lastName", DbType.String, "lastname", DataRowVersion.Current);
insertCommandWrapper.AddInParameter("@firstname", DbType.String, "firstname", DataRowVersion.Current);
// Submit the DataSet, capturing the number of rows that were affected
int rowsAffected = db.UpdateDataSet(employeesDataSet, "Employees", insertCommandWrapper, null, null, UpdateBehavior.Standard);

Note: The database object supports a style of overloads. When using this style, all data access functionality is exposed through two overloads, one to use when executing commands outside of a transaction and another to use when executing commands inside of a transaction.

Additional Information

Parameter Handling in Stored Procedures
Two Types of Parameter handling exists

• Explicit
• Dynamic Discovery

AddParameter (Code Snippet)

AddOutParameter (Code Snippet)

string sqlCommand = "CustOrderHist";
DbCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
dbCommandWrapper.AddOutParameter("@CustomerID",DbType.String,"ALFKI")

GetParameterValue and SetParameterValue (Code Snippet)

Dynamic discovery of parameter:

The Data Access Application Block allows developers to specify the values to be used for parameters without requiring any other information about those parameters. When you use parameter discovery, you should specify all the parameters and set all output parameters to NULL.

The following code demonstrates how to use GetStoredProcCommandWrapper with only the parameter values specified and none of the attributes.

Code Snippet

The information about each specific parameter (for example, its data type) is still required for the underlying ADO.NET method call. To supply this information, the Data Access Application Block uses the ADO.NET method DeriveParameters to look up the parameter information.

Because the DeriveParameters call requires a roundtrip to the back-end database, the application block also provides parameter information caching. After the first call to a particular stored procedure that requires parameter discovery, the information about each parameter is saved in the parameter cache. This means that subsequent calls to the same stored procedure will not require a roundtrip.

When you use parameter discovery, it is a best practice to specify all output parameters as NULL.

Performing Multiple Updates Within a Transaction

A common requirement, when an application executes multiple operations against a database, is that all of the operations must succeed or the database must roll back to its original state (that is, its state before the operations began).

There are several ways to perform database methods within a transaction

• By using manual transactions in T-SQL :
  Transactional operations can be performed with a single stored procedure that uses Transact-SQL statements such as BEGIN TRANSACTION, END TRANSACTION, and ROLLBACK TRANSACTION
  
• By using COM+ controlled automatic transactions
  Declarative attributes are added to your .NET classes that specify your objects' transactional requirements Particularly suited to transactions that must span multiple remote databases.
  
• By using ADO.NET transaction support
  Provided through the Transaction object. Best suited to control transactions against one or more databases on the same physical database server

Suggestions for Creating Portable Database Applications

Here are some suggestions for creating portable database applications:

• Consider the case sensitivity of the back-end relational database management system (RDBMS). For example, string comparison in SQL Server 2000 is not case sensitive, but it is in both Oracle 8i and DB2. To develop a portable application, you should write your compare logic either to be case insensitive or to force the application to store only one case for columns used in a compare operation.
• Avoid using RDBMS-specific data types, such as OracleBlob.
• Avoid using return values when executing your stored procedure. Instead, use output parameters.
• When adding parameters to the parameter collection, make sure that the order in your application code matches the order in the database. The OLE DB provider executes the stored procedure by order instead of by names, so it is important that you add the collections in the correct order.
• If you must use in-line SQL in your application code, make sure your SQL syntax is valid across all the database types that your application will run against.
• Avoid passing null values to the stored procedure's parameter of value type. Doing so may not work if you need a portable interface using DB2 with SQLJ stored procedures.

Instrumenting the Data Access Application Block

The Data Access Application block also incorporates the following instrumentation:

1) Data Access Application Block Performance Counters:
The Data Access Application Block records key metrics by writing to the Microsoft Windows operating system performance counters.

The Data Access Application Block includes the following performance counters:

• # of Command Failures/Sec. This counter displays the number of database commands that failed to execute by the Data Access Application Block.
• # of Commands Executed/Sec. This counter displays the number of database commands executed by the Data Access Application Block per second.
• # of Connection Failures/Sec. This counter displays the number of database connections that failed to be opened by the Data Access Application Block per second.
• # of Connections Opened/Sec. This counter displays the number of database connections opened by the Data Access Application Block per second.
• # of Transaction Failures/Sec. This counter displays the number of transactions that failed to be opened by the Data Access Application Block per second.
• # of Transactions Aborted/Sec. This counter displays the number of database transactions rolled back by the Data Access Application Block per second.
• # of Transactions Committed/Sec. This counter displays the number of database transactions committed by the Data Access Application Block per second.
• # of Transactions Opened/Sec. This counter displays the number of database transactions opened by the Data Access Application Block per second.
• Avg. Command Execution Time. This counter displays the average round-trip time, in seconds, for executing a database command.

2) Windows Management Instrumentation (WMI) Events: The Data Access Application Block reports significant events within the blocks by publishing WMI events.

The Data Access Application Block includes the DataServiceEvent, which is a base class for all the events related to data access during run time. The DataServiceEvent has a string property called Message that contains the message for the event.

The following events inherit from the DataServiceEvent base class:

• DataCommandFailedEvent. This event signifies that the Data Access Application Block has failed to execute a command. Includes the string property Command, which contains the failed data command if the information is available.
• DataConnectionFailedEvent. This event signifies that the Data Access Application Block has failed to open a database connection.
• DataConnectionOpenedEvent. This event signifies that the Data Access Application Block has successfully opened a database connection. Includes the string property ConfigurationFilePath, which contains the path of the main configuration file.
• DataTransactionFailedEvent. This event signifies that the Data Access Application Block has failed to open a database transaction.
• DataTransactionRolledBackEvent. This event signifies that a database transaction has been rolled back by the Data Access Application Block.
• DataServiceFailureEvent. This class is a base class for data access failure events and includes the following properties:
  

  • ExceptionStackTrace (string). This property contains the stack trace of the reported exception.
    
  • ExceptionMessage (string). This property contains the detailed message of the exception and the exception stack trace if this failure is a result of an exception.