Microsoft provides the Enterprise Library for .NET Framework 2.0, which is a library of application blocks designed to assist developers with common enterprise development challenges. While it may sound interesting, you may be wondering what this means for SQL Developers. In this article, we will explore the capabilities of the Enterprise Library and how it can simplify SQL Server development.

Getting Started

To begin, you will need to download the Enterprise Library from the Microsoft website. The installation process is straightforward, but it is recommended to standardize the physical location where you install the library, especially if you work in a team. This helps avoid issues caused by references to the library on different machines.

Once the library is installed, you can start a new web project. For simplicity, we will use a VB.NET based web app. Add a reference to the Microsoft.Practices.EnterpriseLibrary.Data.DLL to your project.

Configuring the Web.Config File

In .NET web applications, the web.config file contains the configuration settings for your website. It is an XML file that is divided into sections. To use the Enterprise Library, we need to make some modifications to the web.config file.

First, add the following section to the configSections:

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>

Next, add the following section to specify the default database:

<dataConfiguration defaultDatabase="AdventureWorks"/>

Finally, add the connection strings for your databases:

<connectionStrings>
    <add name="AdventureWorks" connectionString="server=(local);database=AdventureWorks;Integrated Security=true" providerName="System.Data.SqlClient"/>
    <add name="MyDB" connectionString="server=(local);database=SampleDB;Integrated Security=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

With these configurations in place, the Enterprise Library will handle connection strings and parameters for us, making the retrieval of data from a database a simple process.

Executing Stored Procedures

Let’s say we want to execute a stored procedure and retrieve a recordset. We can use the Enterprise Library to achieve this with just a few lines of code.

First, we need to define the parameters for the stored procedure. For example:

Dim dtCheckDate As Date = New Date(2000, 10, 17)
Dim params() As Object = {773, dtCheckDate}

Next, we execute the stored procedure using the Enterprise Library:

Me.grdVw.DataSource = DatabaseFactory.CreateDatabase().ExecuteDataSet("dbo.uspGetBillOfMaterials", params)

By specifying the stored procedure name and the parameters, the Enterprise Library handles the execution and retrieval of the recordset.

Optional Parameters

If your stored procedure has optional parameters, you can still use the Enterprise Library to handle them. For example, let’s say we have a stored procedure that retrieves departments based on a group name:

CREATE PROC HumanResources.GetDepartment
@GroupName NVARCHAR(50) = NULL
AS
SET NOCOUNT ON
SELECT DepartmentID, Name, GroupName, ModifiedDate
FROM HumanResources.Department
WHERE GroupName = COALESCE(@GroupName, GroupName)
RETURN @@ROWCOUNT
GO

In this case, we can pass in a parameter that is set to nothing to retrieve all departments, or specify a specific group name to retrieve departments for that group.

Conclusion

The Microsoft Enterprise Library provides a powerful toolset for SQL Developers. By leveraging the library’s capabilities, developers can simplify the process of accessing SQL Server data and focus on more interesting aspects of development work. This article has only scratched the surface of what the Enterprise Library can offer, and I encourage you to explore it further to enhance your SQL Server development skills.