I have been using the Enterprise Library from Microsoft’s Patterns and Practices even before it was officially named in 2006 (version 2.0). I use it in all of my .NET applications including windows, web and web services. It still amazes me that companies and developers have not adopted the use of something that is so simple to use instead of writing your own custom data access provider. The following is going to show you how to use the latest Enterprise Library 5.0 which you can read more about here. The Enterprise Library comes with several other application blocks but this post will only focus on the Data Access application block. This will also assume you have a stored procedure created in your SQL Server or Oracle database – Yes, this code will work for both SQL Server AND Oracle without changing anything except your connection string in the web.config file. How cool is that?
Download and Installation
The first thing you need to do is download and install the Enterprise Library in order to get the .dll files needed for the data application block. As of EntLib 5.0, you will need 5 .dll files:
- Microsoft.Practices.EnterpriseLibrary.Common.dll
- Microsoft.Practices.EnterpriseLibrary.Data.dll
- Microsoft.Practices.ServiceLocation.dll
- Microsoft.Practices.Unity.dll
- Microsoft.Practices.Unity.Interception.dll
Once installed you can find these files in the C:\Program Files (x86)\Microsoft Enterprise Library 5.0\Bin folder if you used the default installation options.
Referencing the Enterprise Library assemblies
Let’s assume you are working on an ASP.NET web applicaiton. I typically create a folder in my project that contains all of the third party libraries and then copy the .dll files that I need to that folder. Instead of referencing the location of the installed dll files, I reference the files that are in my folder. This allows for easy maintenance of third party libraries as well as now I don’t need to have the EntLib installed on other machines. You can use this structure in windows applications as well.
Code Setup
Ok, so we have added the references to our web application and we are ready to create the database connection and do some coding. With EntLib 5.0, there is nothing you have to add to the web.config to make this work for the data application block. The only thing you need to have is a ConnectionString section properly configured to connect to your database. Let’s use the default connection string name of LocalSqlServer in the web.config.
We need to create a Database object that we will use as our connection. I typically have a public common class that I put this in. So, I’m going to add a using statement and then add a public static variable for my connection.
// Add this to the using statements
using Microsoft.Practices.EnterpriseLibrary.Data;
public class _Common
{
public static Database dbConn = DatabaseFactory.CreateDatabase("LocalSqlServer");
}
Next, let’s code a function in the business logic layer that uses the connection, adds some parameters, and calls the stored procedure and returns a dataset with the results.
using System;
using System.Data;
using System.Data.Common;
using System.Web;
public class _Event
{
#region Variables
private string _EventName;
private string _EventDesc;
#endregion
#region Properties
public int EventId { get; set; }
public string EventName
{
get { return _EventName; }
set { _EventName = value.Trim(); }
}
public string EventDesc
{
get { return _EventDesc; }
set { _EventDesc = value.Trim(); }
}
public bool bCompleted { get; set; }
#endregion
#region Methods
///
/// Selects all events.
///
public bool SelectEvents(ref DataSet objDS)
{
try
{
DbCommand cmd = _Common.dbConn.GetStoredProcCommand("usp_Event_Sel");
_Common.dbConn.AddInParameter(cmd, "bIncludeCompleted", DbType.Boolean, bCompleted);
objDS = _Common.dbConn.ExecuteDataSet(cmd);
return true;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
}
As you can see, I have created a method called SelectEvents that takes a DataSet as a parameter. This will be the dataset that gets populated. The method returns a bool indicating success or failure of the method. The name of the stored procedure I am calling is usp_Event_Sel. You will also see that I have used a parameter called bIncludeCompleted. This is the parameter that the stored procedure accepts. Then, I call ExecuteDataSet on my database connection dbConn, pass in the DbCommand object cmd and assign the result to my passed in DataSet.
A few last notes. By using the try-catch block, you will return any error messages that the stored procedure throws. The nice thing about this is that any Raiserror functions will return that message and you can display it to the user. Sometimes you have to do database checks before you execute an INSERT or UPDATE in a stored procedure and you can return any problems with these checks through the raiserror function to let your user know what is happening.
You can also add output parameters by calling the AddOutParameter function in the same way as the AddInParameter. Likewise, you can simply call ExecuteNonQuery or ExecuteDataReader if you need that functionality.
