Enterprise Library 3.0 - Using the Data Access Application Block

The Enterprise Library 3.0 Data Access Application Block is meant to handle a majority of your data access needs.

Compared to the previous versions of Data Access Application block a few important changes are made in Enterprise Library 3.0. I have listed the important changes below

• You can utilize your application configuration file to store connection strings, or you can specify connection strings in your code.
• The API has been simplified to utilize the ADO.NET’s DbCommand class, as opposed to the DbCommandWrapper class of prior versions.
• You can use the Data Access Application Block with any ADO .NET 2.0 data provider via the GenericDatabase class.

This article will focus on how to create a generic data access layer using the Enterprise Library 3.0 Data Access Application Block.

ADO.Net 2.0 Database Provider Base Classes
The new base classes found in the System.Data.Common namespace in ADO.NET 2.0 gives the developer the ability to create data access layers that can perform most of the necessary functions of manipulating or selecting data from any database, without needing to change the SQL command statements, for the most part.
The DbProviderFactory factory class helps us to achieve this functionality.

Using the DbProviderFactory class to create an instance of the Database
In order for the DbProviderFactories class to function properly, it is necessary for the proper configuration data to be inserted into the machine.config or application configuration file (web.config/ app.config). Below are the entries in a sample configuration file.











The above configuration details contain three critical attributes that define the factory specifics:

• The invariant attribute, which can be used by the GetFactory(string) method to return the specific DbProviderFactory concrete implementation. For example: calling the GetFactory method using System.Data.SqlClient would return the instance of SqlClientFactory.
• The support attribute, which is responsible for determining the types that the DbProviderFactory’s Create methods can create.
• The type attribute, which defines the actual DbProviderFactory concrete implementation that should be instantiated.

Now with the use of the connection string settings, database provider base classes, and provider factory classes, it is possible to create a connection object without specifying its type within the code of an application. The following application code can be to create the connection object and the use of the providerName attribute to define the correct factory provider class to instantiate.

Sample Application Configuration file entry











Sample Provider Factory code
public DbConnection GetConnection ()
{
ConnectionStringSettings _settings = ConfigurationManager.ConnectionStrings["AdventureWorks"];
DbProviderFactory factory =
DbProviderFactories.GetFactory(_settings.ProviderName);
DbConnection connection = null;
connection = factory.CreateConnection();
connection.ConnectionString = connSetting.ConnectionString;
return connection;
}

Now it is possible to write code that is generic and be able to use any provider that implements
the database provider base classes. Similarly you can use the DbProviderFactory class to create the necessary DbCommand objects, DbDataAdapter objects, DbDataReader objects, and so on to complete their data access layer implementation.

Executing Commands against the Database

The Database class contains the methods necessary for executing commands against a database.
Like the DbCommand class, Database has ExecuteScalar, ExecuteNonQuery, and ExecuteReader methods. The Database class also adds three more methods—ExecuteDataSet, LoadDataSet, and UpdateDataSet—which are used for retrieving untyped and typed DataSets, as well as updating data from a DataSet to a database.

Specifying Parameters for a Database Command Object

Parameters can be added to the DbCommand object directly via the DbCommand.Parameter.Add method. However, while this may seem like the easiest approach, it actually may not be the best practice. Each database provider has its own way of marking up a parameter name. For instance, preceding each parameter name, SQL Server adds an at sign (@), Oracle adds a colon (:), and OLEDB and ODBC add a question mark (?). The Data Access Application Block Database class handles each prefix for you. This way, it is easier to switch from one database provider to another with very little, if any, code changes.

You can add parameters to the DbCommand object through the AddParameter method, the AddInParameter method, or the AddOutParameter method. Typically, it is easier to use the AddInParameter or AddOutParameter methodthe following code demonstrates adding parameters using the Database class.

Adding database command parameters via Database class.
public void SaveUser(User _user)
{
Database _userDB = DatabaseFactory.CreateDatabase();
DbCommand _command = _userDB.GetStoredProcCommand("SaveUser");
_userDB.AddInParameter(_command, "FirstName", DbType.String);
_userDB.AddInParameter(_command, "LastName", DbType.String);
_userDB.AddInParameter(_command, "Salary", DbType.Decimal);
_userDB.AddInParameter(_command, "dob", DbType.DateTime);

_userDB.SetParameterValue(_command, "FirstName", _user.Firstname);
_userDB.SetParameterValue(_command, "LastName", _user.Lastname);
_userDB.SetParameterValue(_command, "Salary", _user.Salary);
_userDB.SetParameterValue(_command, "dob", _user.Dob);

_userDB.ExecuteNonQuery(_command);
}


Understanding the Database Factory Class
A Database object can be created by using a factory via the DatabaseFactory class or by directly constructing one. The DatabaseFactory class uses configuration information to determine the connection string, ADO.NET data provider, and appropriate Data Access Application Block Database class implementation to instantiate. The DatabaseFactory class uses the static method CreateDatabase to create the specific Database subtype. The CreateDatabase method has two signatures

· CreateDatabase() : Database - Does not take any parameters and creates the default database instance defined in the application’s configuration file
· CreateDatabase(stringname) : Database - Takes a string parameter that allows the developer to determine by name which instance should be returned

The DatabaseFactory class uses the connections defined in the connectionStrings node of the application configuration file. If no provider is specified in the specific named instance of a connection string, a SqlDatabase instance is presumed. By default, the DatabaseFactory class creates Database objects of the following types:

• SqlDatabase for data providers specified as System.Data.SqlClient
• SqlCeDatabase for data providers specified as System.Data.SqlServerCe
• OracleDatabase for data providers specified as System.Data.OracleClient
• GenericDatabase for all other data provider types

An instance of the GenericDatabase class can be used with any .NET managed provider, including the ODBC and OLEDB providers that are included in .NET Framework 2.0 and 3.0.

Configuring the Data Access Application Block.

Configuring the Data Access Application Block can be done either manually through a text editor or via the Configuration Console. The benefit of using the Configuration Console is that it will validate the configuration data entered, thus ensuring that that data is set up properly. Therefore, although it is possible to modify the configuration data manually, it is generally suggested that the Configuration Console be used whenever possible.

In this exercise we will be using the Enterprise Library configuration console for configuring the Data Access Application Block.


1. Creating the Database

/****** Object: Table [dbo].[Users] Script Date: 03/03/2008 16:36:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
DROP TABLE [dbo].[Users]
GO
/****** Object: Table [dbo].[Users] Script Date: 03/03/2008 16:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
[UserID] [int] NOT NULL,
[FirstName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Salary] [money] NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
)
END

-- =============================================
-- Author: Prajeesh Prathap
-- =============================================
CREATE PROCEDURE [dbo].[GetAllUsers]
-- Add the parameters for the stored procedure here
AS
BEGIN

SET NOCOUNT ON;
SELECT * from [Users]
END


-- =============================================
-- Author: Prajeesh Prathap
-- =============================================
CREATE PROCEDURE SaveUser
@FirstName varchar(250),
@LastName varchar(250),
@Salary money,
@dob datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserID int
SELECT @UserID = MAX(UserID) from Users

IF (@UserID is not NULL)
SET @UserID = @UserID + 1
else
SET @UserID = 1
insert into users(userid, firstname, lastname, salary, dob)
values
(@UserID,
@FirstName, @LastName, @Salary, @dob)
END
GO

(I have created a table Users and two Stored Procedures for pulling data and inserting data into the above table)

2. Add references to the following enterprise library assemblies into the data access layer project.

Microsoft.Practices.EnterpriseLibrary.Common;
Microsoft.Practices.EnterpriseLibrary.Data;
Microsoft.Practices.EnterpriseLibrary.ObjectBuilder;

3. Add a new application configuration file to the project (App.Config)

4. Right click the App.config file and select Edit Enterprise Library Configuration


5. In the configuration console Right Click the Connection Strings node under Data Access Application Block and select New--> Connection String






6. In the Properties change the name of the newly added connection string to MyConnection






7. Build the connection string by selecting the eclipse on the ConnectionString property in the properties box.


8. Select the Data Access Application Block and press F4 to display the Data Access Application Block properties.

9. Change the Default Database to MyConnection




10. Save and Close the configuration console.

11. Create the User Business Entity as follows

public class User
{
private int userid;

public int Userid
{
get { return userid; }
set { userid = value; }
}
private string firstname;

public string Firstname
{
get { return firstname; }
set { firstname = value; }
}
private string lastname;

public string Lastname
{
get { return lastname; }
set { lastname = value; }
}
private decimal salary;

public decimal Salary
{
get { return salary; }
set { salary = value; }
}
private DateTime dob;

public DateTime Dob
{
get { return dob; }
set { dob = value; }
}
}

12. Creating the User Data Access Layer

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

public class UserDAL
{
Database _userDB;
public UserDAL()
{
_userDB = DatabaseFactory.CreateDatabase();
}

public List GetAllUsers()
{
List userList = new List();
DbCommand _command = _userDB.GetStoredProcCommand("GetAllUsers");
using (IDataReader _reader = _userDB.ExecuteReader(_command))
{
User user;
while (_reader.Read())
{
user = new User();
user.Userid = (int)_reader["UserID"];
user.Firstname = _reader["FirstName"].ToString();
user.Lastname = _reader["LastName"].ToString();
user.Salary = (decimal)_reader["Salary"];
user.Dob = (DateTime)_reader["DOB"];
userList.Add(user);
}
}
return userList;
}

public void SaveUser(User _user)
{
DbCommand _command = _userDB.GetStoredProcCommand("SaveUser");
_userDB.AddInParameter(_command, "FirstName", DbType.String);
_userDB.AddInParameter(_command, "LastName", DbType.String);
_userDB.AddInParameter(_command, "Salary", DbType.Decimal);
_userDB.AddInParameter(_command, "dob", DbType.DateTime);


_userDB.SetParameterValue(_command, "FirstName", _user.Firstname);
_userDB.SetParameterValue(_command, "LastName", _user.Lastname);
_userDB.SetParameterValue(_command, "Salary", _user.Salary);
_userDB.SetParameterValue(_command, "dob", _user.Dob);

_userDB.ExecuteNonQuery(_command);


}
}

Summary
The Data Access Application Block provides a lot of functionality for making database calls from a data access component. It can save coding time, reduce code, and support database provider independence. It is important to note that while a lot of features are provided in the Data Access Application Block, there may be times when it’s necessary to use only certain features, or possibly none at all. However, for the most part, it should meet most, if not all, of your data access needs.

The next article of this series will focus on the Caching Application Block.