Using the Microsoft Enterprise Library to access SQL Data

Microsoft provide an Enterprise Library for .NET Framework 2.0. In their own words

The patterns & practices Enterprise Library is a library of application blocks designed to assist developers with common enterprise development challenges.

Sounds interesting but what does this mean for the SQL Developer? As a relative newcomer to .NET I was

interested to see what this library could achieve.

The article below outlines my experiments with this tool.

Starting off

Stage one is to download the Enterprise Library from the preceding link.

You will have to register to do so but this is free to do so.

The installation is straight forward but at this stage I recommend that if you develop in a team then you should all standardise on the physical location where you install the libary. This reduces problems caused by references to the

libary on different machines.

The next stage is to start a new Web Project. For simplicity stake I used a VB.NET based web app. To this project I added a reference to the Microsoft.Practices.EnterpriseLibrary.Data.DLL

We are now ready to amend the web sites WEB.CONFIG file

WEB.CONFIG

.NET web sites have a file called web.config. This file contains (as the name suggests) the configuration settings for

your web site. It is an XML file so think of it as the descendant of the INI file.

This file is an XML file which is broken down into sections.

<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <configSections>
    <section name="dataConfiguration" 
    type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>
  </configSections>
  <appSettings/>
  <dataConfiguration defaultDatabase="AdventureWorks"/>
  <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>

The first block is the <configSections>.

This describes the section of the web.config file that is relevant to our needs.

In our case this tells us that we are ready to use a section called dataConfiguration. The two entries in the type attribute

are the class that handles the processing of the web.config file in relation to this section followed by the assembly name

to which this refers.

As an aside you can set up your own sections within web.config and providing you have a class in your assembly that

inherits from SerializableConfigurationSection the web.config file can allow you to plug precompiled assemblies into

your applications.

The <dataConfiguration/> section allows us to specify the name of a

connection string that will be used as a default connection string. The precise use for this will be described later.

Finally there is the <connectionStrings> section.

This allows us to specify our explicit connection strings.

This differs from .NET 1.0 where these were lumped together in the <appSettings/> section.

Note that the defaultDatabase is set to AdventureWorks. This refers to the name attribute in the connection string XML not the

name of the database itself.

A sample stored procedure

The whole aim of my experiment was to trigger a stored procedure and bring back a recordset so my first port

of call was the SQL 2005 sample database, Adventureworks.

I found that the stored procedure dbo.uspGetBillOfMaterials expected two parameters

• @StartProductID
• @CheckDate

I discovered that when executing the stored procedure in SQL Management Studio with the values listed below

I could expect a recordset to be returned.

• @StartProductID=773
• @CheckDate was equivalent of 17th October 2000

A sample web page

For the purposes of the experiment I set up the simplest possible .NET web page. It is not intended to do

anything other than proove that the Enterprise Library behaves as expected.

The default behaviour for the asp:gridview control is to render all rows and columns automatically.

The code behind the form

Preceding the class declaration for my form I entered the following imports statement.

Imports Microsoft.Practices.EnterpriseLibrary.Data

This allows us to refer to objects in the library namespace directly without having to qualify them with the

full name.

In the default.aspx page of my .NET application I entered the following code for my Page Load event.

Executing my .NET web site produced the following output.

How the code works

The first two lines are self explanatory

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

dtCheckDate is initialised with a date set to 17th October 2000.

params is an array of objects initialised to the values in the curly braces.

The contents of the params variable must be defined in the same order as the parameters in the stored procedure.

The line that does all the work is as follows

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

What this does it tell the Enterprise Library to execute the stored procedure in the defaultDatabase

with the contents of the params array.

If my stored procedure was not in the Adventureworks connection but in the MyDB connection then I would have written

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

In short we can see that the Enterprise Library makes the retrieval of data from a database a very simple process.

We have not had to worry about

• Connection strings because these are specified in the web.config file.
• Connections because this is handled by the Enterprise Library
• Parameters because the Enterprise Library has matched our values to the stored procedure parameters that it discovered

  for itself

What about optional parameters?

I set up a stored procedure in the AdventureWorks database as follows:

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

If I don't supply a parameter then all departments will be retrieved, otherwise only those departments for my

specific group will be retrieved.

Using the technique described so far you have to specify each and every parameter. This means that you

have to supply your object {n1,n2,....n99} array with a value per stored procedure parameter.

The most you can do is pass in a parameter that is set to nothing. An example could be as follows:

    Private Sub GetDepartmentList(ByVal DepartmentGroup As String)
        Dim DeptGroup As String
        If Not DepartmentGroup.Equals("{All}") Then
            DeptGroup = DepartmentGroup
        End If
        Dim params() As Object = {DeptGroup}
        Me.grdVw.DataSource = _
            DatabaseFactory.CreateDatabase().ExecuteDataSet("HumanResources.GetDepartment", params)
        Me.grdVw.DataBind()
    End Sub

When a variable is unset then it will be interpreted by the Enterprise Library as being an explicit NULL. If our stored procedure

parameter had a default value other than NULL then the VB.NET routine would still pass in a NULL so our stored procedure

would not do what we would expect.

This is still a very simple piece of code compared with old ASP.

What happens if I want @RETURN_VALUE?

In general when I write stored procedures I specify the @RETURN_VALUE from a stored procedure as being the @@ROWCOUNT.

This allows me to use ExecuteNonQuery even when SET NOCOUNT ON is used.

We can still use the Enterprise Library to help us with this.

Firstly we need to add a new imports statement above our class declaration

Imports System.Data

Now we change our GetDepartmentList method into a function as follows

    Private Function GetDepartmentList(ByVal DepartmentGroup As String) As Integer
        Dim ReturnValue As Integer
        Dim DeptGroup As String
        If Not DepartmentGroup.Equals("{All}") Then
            DeptGroup = DepartmentGroup
        End If
        ' Use the Enterprise Library to retrieve the command object.
        Dim cmd As SqlClient.SqlCommand = DatabaseFactory.CreateDatabase().GetStoredProcCommand("HumanResources.GetDepartment")
        ' Now discover the parameters for the stored procedure
        DatabaseFactory.CreateDatabase().DiscoverParameters(cmd)
        ' Set our @GroupName parameter value
        DatabaseFactory.CreateDatabase().SetParameterValue(cmd, cmd.Parameters(1).ParameterName, DeptGroup)
        Me.grdVw.DataSource = _
            DatabaseFactory.CreateDatabase().ExecuteDataSet(cmd)
        Me.grdVw.DataBind()
        ' Grab our RETURN_VALUE
        ReturnValue = DatabaseFactory.CreateDatabase().GetParameterValue(cmd, "RETURN_VALUE")
        ' I know .NET is supposed to clean up after you but it doesn't hurt
 ' to give it a hint.
        cmd.Dispose()
        Return ReturnValue
    End Function

Considering what it is supposed to do there is remarkably little code in this procedure. Now obviously if this was live

code there would be error trapping and exception handling thrown into the mix but even so this should give a taster of what

the Microsoft Enterprise Library can offer the SQL Developer.

Other methods of executing stored procedures within DatabaseFactory.CreateDatabase

I have used ExecuteDataset because I wanted to display my data directly in a DataGrid however

for the purposes of executing stored procedures I could have used any of the following

There are multiple versions of each of these methods

Is all this too good to be true?

If you have stepped from old fashioned ASP straight to .NET 2.0 the Enterprise Library sounds too good to be true.

I have spoken to colleagues with vastly more .NET experience and their comment was that there is a slight performance hit

over explicitly specifying a command, declaring parameters, connections etc.

I discussed this with a senior enterprise level technical architect and his take on this was that the cost of developer

time that would be lost in doing things the more explicit way would be far greater than the cost of the hardware to offset

any performance gains.

If nothing else it means that, as a developer, I can concentrate on the more interesting aspects of development work

rather than the repetitive hand cranking of background drudgery.

Conclusion

The examples given in this article are straight forward and simple. There is a great deal more in the Microsoft

Enterprise Library that bears greater investigation. Hopefully this article will have wetted your appetite for further

experimentation.

One particular point to note is that the Enterprise Library is supplied with its source code so you can learn a

great deal by examining this in more detail.