Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Enterprise Library for .NET Framework 2.0

By David Poole,

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

.NET Add Reference dialog

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.

A very simple ASPX page

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.

default.aspx Page_Load event code

Executing my .NET web site produced the following output.

Example DataGrid 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

MethodDescription
ExecuteScalarBrings back the first column of the first row of a recordset
ExecuteNonQueryRuns the stored procedure without bringing back a record set but will bring back the number of records affected by the last command to run if SET NOCOUNT ON is not used.
If SET NOCOUNT ON is used then the technique described for dealing with RETURN_VALUE can be used.
ExecuteReaderOpens a data reader, otherwise known as a firehose cursor, to retrieve data in a fast forward only cursor

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.

Total article views: 14668 | Views in the last 30 days: 6
 
Related Articles
FORUM

Code libraries / includes in stored procedures

Code libraries / includes in stored procedures

FORUM

stored procedure parameters question

stored procedure parameters question

FORUM

Stored procedures with output parameters

Stored procedures with output parameters

FORUM

Retrieving stored procedure parameters for modification

ADO stored procedure parameter modification

FORUM

stored procedure with optional parameters

creating stored procedure with optional parameters

Tags
.net    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones