Auto-Generating Wrapper Classes for Stored Procedures, Part 1: Using the AutoSproc Tool

 

Steve J. Rodgers
Software Consultant

March 2004

Applies to:
   Microsoft® Visual Studio® .NET
   Microsoft® Visual Basic® .NET
   Microsoft® Visual C#® .NET

Summary: The AutoSproc tool uses reflection to auto-generate wrapper classes for calling stored procedures. This saves the developer from having to work with ADO.NET classes such as SqlCommand, SqlParameter, SqlDataAdapter, etc. Here, we'll take a look at the salient points of the tool and review how to use it in your application. For more details on the internals of the tool, see the companion article Auto-Generating Wrapper Classes for Stored Procedures, Part 2: The Internals of the AutoSproc Tool. (18 printed pages)

Download the Autosproc.exe sample code.

Contents

Introduction
The Basics
Relevance of Method Return Types
Using the ISprocBase.AutoClose Property
Using the ISprocBase.Transaction Property
SprocFactory.CreateInstance() Method Overloads
Performance
Miscellaneous
Limitations
Wrap-Up

Introduction

These days, folks who are building scalable distributed applications tend to partition application code into discrete tiers conforming to the classic three tier model: Presentation, Business and Data respectively. When building an application of this kind with .NET technologies, the data tier usually consists of one or more classes with methods that exhibit a distinct programming pattern:

  1. Build a command object (SqlCommand/OleDbCommand).
  2. Add parameters to the command object (SqlParameter/OleDbParameter).
  3. Issue a call to a stored procedure in the database to carry out the request from the business tier.
  4. Return results (if any) to the business tier.

As an example, consider the following stored procedure up_insert_auth that we could define for use in the pubs database:

CREATE PROCEDURE up_insert_auth
@au_lname varchar(40)
as
insert into authors (au_id, au_lname, au_fname, phone, contract) values 
('123-56-1321', @au_lname, 'steve', '11111111', 1)
return

To actually call the stored procedure up_insert_auth from the data tier using ADO.NET, one is more than likely to define a wrapper function to build the call and execute it:

Public Class Sample1VB
    Public Shared Function Wrapper_up_insert_auth(ByVal conn As _
                        IDbConnection, _
         ByVal au_lname As String) As _
         Integer
        Dim p As IDataParameter = New SqlParameter("@au_lname", _
                                                au_lname)

        Dim cmd As IDbCommand = conn.CreateCommand()
        cmd.Parameters.Add(p)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "up_insert_auth"
        Return cmd.ExecuteNonQuery()
    End Function

    Public Shared Sub Main()
        Dim conn As IDbConnection = New SqlConnection()
        conn.ConnectionString ="database=pubs;Integrated Security=SSPI"
        conn.Open()
        Dim rowsAffected As Integer
        rowsAffected = Wrapper_up_insert_auth(conn, "rodgers")
        conn.Close()
        Console.WriteLine("{0} rows affected", rowsAffected)
    End Sub
End Class

Writing code of this nature can be both tedious and potentially error prone with many developers using a cut and paste solution each time. This kind of repetition leads one to consider alternative approaches based on code generation techniques—in this case, code that automatically generates the wrappers for you based on type information.

This article outlines the usage of a code generation tool, AutoSproc, whose internals are based on classes exposed from the System.Reflection.Emit namespace. To whet your appetite, here is the same stored procedure being called from Microsoft® Visual Basic® .NET code that is leveraging the code generation layer. The remainder of this article will describe how this tool works.

Note The language neutrality of .NET means the code generation layer can be leveraged from most other managed languages such as Microsoft® Visual C#®, Managed C++ etc. Code samples in both C# and Visual Basic .NET can be located in the folder "CodeSamples" of the project download directory. All code samples in this article are written in C#.

'client code must reference assembly AutoSproc.dll 
'for it to compile successfully
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports AutoSproc 'code generation assembly namespace

Public Interface IPubs
    Inherits ISprocBase
    Function up_insert_auth(ByVal au_lname As String) As Integer
End Interface


Class Sample2VB
    Public Shared Sub Main()
        Dim conn As IDbConnection = New SqlConnection()
        conn.ConnectionString ="database=pubs;Integrated Security=SSPI"
        conn.Open()

        Dim imp As Object
        'Call to code generation layer
        imp = SprocFactory.CreateInstance(GetType(IPubs), _
                                            DBProvider.SQLServer)

        Dim pubs As IPubs = DirectCast(imp, IPubs)
        pubs.Connection = conn

        Dim rowsAffected As Integer
        'invoke stored procedure
        rowsAffected = pubs.up_insert_auth("rodgers")
        Console.WriteLine("{0} rows affected", rowsAffected)
    End Sub
End Class

The Basics

The essence of the idea behind AutoSproc is for the developer to define methods on an interface that correspond to the stored procedures that exist in a database. These interface methods are abstract at the time of program compilation, but receive an implementation at runtime from a code generation assembly the client program references, named AutoSproc.dll. At the heart of this assembly there is a publicly callable static (shared) method called SprocFactory.CreateInstance(). A key parameter that must be passed when making a call to this method is type information for an interface. The primary function of this method is to dynamically fabricate a class that implements the designated interface and to return an instance of that class to the caller. As an example, consider the following stored procedure definition:

CREATE PROCEDURE upAddTwoNumbers
@a int,
@b int,
@result int output
AS
select @result = @a+@b

Given this definition, the interface would be defined in Visual Basic .NET as follows:

Public Interface IMath
    Inherits ISprocBase
    Function upAddTwoNumbers(ByVal a As Integer, _
      ByVal b As Integer, _
      ByRef result As Integer) As Integer
End Interface

Some observations can be made about the interface:

  1. The interface must derive from ISprocBase, which is defined in the referenced assembly, AutoSproc.dll. This is because some important properties from ISprocBase need to be made available on your deriving interface—these will be discussed shortly.
  2. The method name exactly matches the stored procedure name in the database, and the parameter names exactly match those of the stored procedure but drop the leading '@' character. The reason for this is that the stored procedure name and parameter names are inferred from type information for the interface method and parameters, respectively, at code generation time.
  3. The interface name you choose is up to you (this example chose IMath, the previous example chose IPubs).

ISprocBase adds three properties to your deriving interface:

Public Interface ISprocBase
   Property Connection() as IDbConnection
   Property AutoClose () as Boolean
   Property Transaction () as IDbTransaction
End Interface

The Connection property is the means by which one passes the database connection to the code generated class at runtime. The AutoClose and Transaction properties will be discussed a little later.

At runtime, type information for interface IMath is passed to the code generation assembly by invoking one of the SprocFactory.CreateInstance() method overloads. These factory methods return a class implementation for the interface. Internally, the code generation layer is using the System.Reflection.Emit.ILGenerator class to flesh out each of the methods described in the interface. However, before a stored procedure can be invoked, it is important to set the Connection property to a valid database connection. Once this is done, the stored procedure is invoked by calling the appropriate interface method.

'client code must reference assembly AutoSproc.dll 
'for it to compile successfully
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports AutoSproc 'code generation assembly namespace


Public Interface IMath
    Inherits ISprocBase
    Function upAddTwoNumbers(ByVal a As Integer, _
      ByVal b As Integer, _
      ByRef result As Integer) As Integer
End Interface

Class Sample3VB
    Public Shared Sub Main()
        'Initialise the database connection 
        Dim conn As IDbConnection = New SqlConnection()
        conn.ConnectionString = "database=stevesdb;"
        conn.ConnectionString += "Integrated Security=SSPI"

        conn.Open()

        'create an implementation of IMath specifying 
        'SQL Server as the resource manager
        Dim imp As Object
        imp = SprocFactory.CreateInstance(GetType(IMath), _
                                            DBProvider.SQLServer)

        Dim math As IMath = DirectCast(imp, IMath)
        'set up the connection
        math.Connection = conn

        'invoke the stored procedure by calling the method
        'on the interface
        Dim result As Integer
        math.upAddTwoNumbers(2, 2, result)
        Console.WriteLine("2 + 2 = {0}", result)
    End Sub
End Class

The following example is pseudo-code to illustrate an approximation of what's being generated. In reality no Visual Basic .NET instructions are generated, as System.Reflection.Emit generates Common Intermediate Language (CIL) instructions directly. The second part of this article Auto-Generating Wrapper Classes for Stored Procedures, Part 2: The Internals of the AutoSproc Tool is geared towards explaining the inner workings of the tool and will show how System.Reflection.Emit is being used.

 'Generated class pseudo code rendered in Visual Basic .NET
Public Class SprocMathImpl
   Inherits SprocBase
   Implements IMath
   
   Public Function upAddTwoNumbers (ByVal a as Integer, _
               ByVal b as Integer, _
               ByRef result as Integer) as Integer _
               Implements IMath.upAddTwoNumbers

      Dim conn as IDbConnection = Me.Connection
      
      Dim cmd as IDbCommand = conn.CreateCommand ()
      cmd.CommandText = "upAddTwoNumbers"
      cmd.CommandType = CommandType.StoredProcedure

      'marshal first parameter
      Dim p as IDataParameter = New SqlParameter ("@a", a)
      p.Direction = ParameterDirection.Input
      cmd.Parameters.Add (p)
      
      'marshal second parameter
      p = New SqlParameter ("@b", b)
      p.Direction = ParameterDirection.Input
      cmd.Parameters.Add (p)
      
      'marshal third parameter
      p = New SqlParameter ("@result", result)
      p.Direction = ParameterDirection.InputOutput
      cmd.Parameters.Add (p)
      
      Dim rowsAffected as Integer
      rowsAffected = cmd.ExecuteNonQuery ()

      'fix up output parameter
      result = CType (p.Value, Integer)      

      Return rowsAffected
   End Function 
End Class

As noted above, the method name normally must match the stored procedure name. However, it is possible to deviate from this rule by specifying a custom attribute, SprocName, on the interface method:

Public Interface IMath
      Inherits ISprocBase
   <SprocName("upAddTwoNumbers")> _
   Function not_a_valid_sproc_name (ByVal a as Integer, _
         ByVal b as Integer, _
         ByRef result as Integer) as Integer
End Interface

C# and Visual Basic .NET do not allow one to define two methods of the same name on a given type that only differ by return value. So if, for example, one wanted to define two or more methods that dispatch to the same stored procedure and differ only by return type, then the methods should be uniquely named and the SprocName custom attribute used to identify the same stored procedure. Here is an example:

Public Interface IMath
      Inherits ISprocBase
   <SprocName("upAddTwoNumbers")> _
   Function upAddTwoNumbersReturnInteger(ByVal a as Integer, _
         ByVal b as Integer, _
         ByRef result as Integer) as Integer<SprocName("upAddTwoNumbers")> _
   Function upAddTwoNumbersReturnDataSet(ByVal a as Integer, _
         ByVal b as Integer, _
         ByRef result as Integer) as DataSet
End Interface

Relevance of Method Return Types

The code generation algorithm makes a point of inspecting the return type of each method defined in the interface, and will generate code for the method differently depending on the return type it sees. The current implementation of the algorithm only permits four different kinds of method return types:

  • System.Int32 (Integer / int)
  • System.Data.DataSet (or a class derived from System.Data.DataSet; also known as a strongly typed dataset)
  • System.Data.IDbCommand
  • System.Data.IDataReader

Each of the return type behaviors is now described.

Return type: System.Int32

The generated method automatically creates the command object, populates parameter(s), sets the stored procedure name on the command object and invokes ExecuteNonQuery() on the command object. ExecuteNonQuery() yields the number of rows affected. This return value flows back to the caller.

Return type: System.Data.DataSet or a strongly typed dataset (therefore derived from System.Data.DataSet)

The generated method automatically creates the command object, populates parameter(s), sets the stored procedure name on the command object, creates either a DataSet or a strongly typed dataset and populates it with a data adapter by calling the Fill() method. The populated dataset is then returned to the caller. When using a strongly typed dataset, the table name is the same as the dataset class name.

Return type: System.Data.IDbCommand

The generated method automatically creates the command object, populates parameter(s) and sets the stored procedure name on the command object. The command object is then returned to the caller without executing the stored procedure.

Return type: System.Data.IDataReader

The generated method automatically creates the command object, populates parameter(s), sets the stored procedure name on the command object and invokes ExecuteReader() on the command object. ExecuteReader() yields an IDataReader implementation that is returned to the caller. It is important to note that AutoClose (discussed shortly) must be set to false when specifying this return type, otherwise the generated method will throw an exception of type IllegalAutoCloseSettingException. This is because the database connection must remain open for the IDataReader.Read() method to function—remember, you should call Close() on both the database connection and the IDataReader interface when you have finished using them.

Using the ISprocBase.AutoClose Property

Something that can seriously impact application scalability is forgetting to call the Close() method on the database connection once stored procedure execution is complete. To assist the developer in this respect, it is possible to configure the generated code to automatically close the connection prior to returning control to application code from all of the generated interface method bodies.

The AutoClose property on the interface can be used to toggle this feature on and off dynamically at runtime during the lifetime of the application.

'client code must reference assembly AutoSproc.dll 
'for it to compile successfully
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports AutoSproc 'code generation assembly namespace


Public Interface IMath
    Inherits ISprocBase
    Function upAddTwoNumbers(ByVal a As Integer, _
        ByVal b As Integer, _
        ByRef result As Integer) As Integer
End Interface


Public Class Sample4VB
    Public Shared Sub Main()
        'Initialise the database connection 
        Dim conn As IDbConnection = New SqlConnection()
        conn.ConnectionString = "database=stevesdb;"
        conn.ConnectionString += "Integrated Security=SSPI"

        conn.Open()

        'create an implementation of IMath specifying 
        'SQL Server as the resource manager
        Dim imp As Object
        imp = SprocFactory.CreateInstance(GetType(IMath), _
                                            DBProvider.SQLServer)

        Dim math As IMath = DirectCast(imp, IMath)
        math.Connection = conn
        'turn on AutoClose
        math.AutoClose = True

        'invoke the stored procedure by calling the method
        'on the interface
        Dim result As Integer
        math.upAddTwoNumbers(2, 2, result)

        Console.WriteLine("Connection State:" + conn.State.ToString())
        'Database connection now implicitly closed
        'due to AutoClose set to true earlier
        Console.WriteLine("2 + 2 = {0}", result)

        math.AutoClose = False 'toggle AutoClose off
        conn.Open() 'reopen the connection
        math.upAddTwoNumbers(3, 3, result)
        Console.WriteLine("Connection State:" + conn.State.ToString())
        'Database connection left open
        'due to AutoClose set to false earlier      
        conn.Close()  'must explicitly call Close
        Console.WriteLine("3 + 3 = {0}", result)
    End Sub
End Class

Using the ISprocBase.Transaction Property

It is often the case that a local transaction is used to protect multiple operations carried out against a database. To facilitate this, a transaction property is defined on the base interface. Its usage is fairly straightforward: Use the database connection to begin the transaction (IDbConnection.BeginTransaction()), feed the transaction to the transaction property on the code generated class, make one or more transacted stored procedure calls then either commit / abort or rollback the transaction (IDbTransaction.Commit() / Abort() / Rollback()).

It is important to note that if AutoClose is set to true, then the transaction will exhibit potentially undesirable auto commit behavior because closing the database connection implicitly attempts to commit the transaction. It is recommended that if the Transaction property is being used then AutoClose is set to false.

'client code must reference assembly AutoSproc.dll 
'for it to compile successfully
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports AutoSproc 'code generation assembly namespace

Public Interface IMath
    Inherits ISprocBase
    Function upAddTwoNumbers(ByVal a As Integer, _
        ByVal b As Integer, _
        ByRef result As Integer) As Integer
End Interface

Public Class Application
    Public Shared Sub Main()
        'Initialise the database connection 
        Dim conn As IDbConnection = New SqlConnection()
        conn.ConnectionString = "database=stevesdb;"
        conn.ConnectionString += "Integrated Security=SSPI"
        conn.Open()

        'create an implementation of IMath specifying 
        'SQL Server as the resource manager
        Dim imp As Object
        imp = SprocFactory.CreateInstance(GetType(IMath), _
                                    DBProvider.SQLServer)

        Dim math As IMath = DirectCast(imp, IMath)
        math.Connection = conn
        'turn off AutoClose for transactions!!        math.AutoClose = False

        Dim result1 As Integer
        Dim result2 As Integer

        'obtain a database local transaction
        Dim tx As IDbTransaction = conn.BeginTransaction()
        math.Transaction = tx
        'perform two operations protected by the transaction
        math.upAddTwoNumbers(2, 2, result1)
        math.upAddTwoNumbers(3, 3, result2)
        tx.Commit()   'Attempt to commit the transaction
        conn.Close()  'AutoClose is set to false so we must Close   

        Console.WriteLine("2 + 2 = {0}", result1)
        Console.WriteLine("3 + 3 = {0}", result2)
    End Sub
End Class

SprocFactory.CreateInstance() Method Overloads

There are four overloaded SprocFactory.CreateInstance() methods offered, only one of which is strictly necessary (the second one). The other three are offered as a convenience to the developer for seeding Connection and AutoClose properties at creation time. It should be noted that overloads not providing an AutoClose parameter (the first and third) will configure it to true by default.

Overload 1

Public Shared Function CreateInstance (ByVal itf as Type, _
ByVal prov as DBProvider) _
as Object

Overload 2

Public Shared Function CreateInstance (ByVal itf as Type, _
ByVal prov as DBProvider, _
                  autoClose as Boolean) as Object 

Overload 3

Public Shared Function CreateInstance (ByVal itf as Type, _
ByVal conn as IDbConnection) _
as Object

Overload 4

Public Shared Function CreateInstance (ByVal itf as Type, _
ByVal conn as IDbConnection, _
                  ByVal autoClose as Boolean) _
                  as Object

The DBProvider enumeration is the means by which the user configures the code generation layer to emit an implementation. For this release, only SQL Server and OleDb managed providers are available. If the user elects to use the third or fourth CreateInstance() method then the database provider type is inferred by the code generation layer from the IDbConnection parameter, thus making a DBProvider enumeration parameter unnecessary.

Performance

Of course, performance is something most people are interested in these days, and this section attempts to give you some figures that provide an insight into the overhead associated with using the code generation layer as compared with writing the wrappers yourself.

There are potentially two types of performance figures to discuss:

  1. The time it takes to generate the class and return an implementation from SprocFactory.CreateInstance().
  2. The time it takes to issue a stored procedure call through a code generated method.

The former is not discussed here since the application could pre-cache interface types used during the lifetime of the application by calling SprocFactory.CreateInstance() once for each interface type at application start-up time (for web applications, this is done in Application_OnStart). When pre-caching, do not store the object reference for sharing

Seven different tests were carried out to give an indication of performance metrics (tests can be located in the folder "PerformanceTests" in the project directory when downloaded). The results were as follows:

Number of stored procedure parameters (parameter type: System.String) Return type Average call time (sampled 9600 calls) without using code generation layer Average call time (sampled 9600 calls) using code generation layer Percentage slow down of call time when using code generation layer
1 DataSet 5121 5838 14.0%
2 DataSet 5193 5899 13.6%
2 Strongly typed data set 5708 6461 13.2%
3 DataSet 5783 6522 12.8%
3 Strongly typed data set 5234 5960 13.9%
9 DataSet 6888 7638 10.9%
10 DataSet 6912 7655 10.7%

It is interesting to note that the more parameters one passes, the less the time difference between generated and non generated wrapper methods is observed. This is because the code generated method only creates the parameters (SqlDbParameters/OleDbParameters) on the first call. Subsequent calls to the same method retrieve a cached copy of the parameters and simply set the value property on each one.

It is worth bearing in mind that the overhead incurred when making the call through a code generated wrapper method is likely to be minimal compared to the time spent executing the SQL of the stored procedure itself or a network roundtrip if the store procedure is executed on a different host.

Miscellaneous

Code generated method implementations will validate that a database connection has been set prior to performing any work. If, for some reason, the connection is set to a valid connection reference but has not been opened, then the implementation will make the call to Open() on your behalf before proceeding any further. When this situation arises, the implementation will automatically close the connection prior to the call, returning control to the caller, regardless of the AutoClose setting.

The generated assembly is always created in memory, though it is possible (but not necessary) to save the generated assembly to disk by calling SprocFactory.PersistAssembly(). If the call is to be made then it should be done as late as possible, preferably just prior to application shutdown. The reason for this stipulation is that once an assembly is persisted to disk, it is no longer possible to dynamically create new types in that assembly—in other words, once you have called SprocFactory.PersistAssembly(), you cannot call SprocFactory.CreateIntance() specifying a new interface type. The persisted assembly can be found in the client binary directory and is called "AutoSprocDynamicAsm.dll" (Though the file name can be configured by the SprocFactory.AssemblyName property)

Inspection of the assembly using the .NET Framework SDK tool ildasm.exe will reveal the generated class implementation(s) and CIL.

It is not hard to imagine a higher-level tool that might use the assembly persistence feature of AutoSproc to save the dynamic assembly to disk. One could then write an application that simply references the new assembly and creates instances of the wrapper classes using new instead of calling SprocFactory.CreateInstance(). The foundation for such a tool can be located in the "SprocInterfaceGen" folder of the project download directory. It is a GUI tool that allows one to browse databases on a machine and pick stored procedures for which wrapper implementations are emitted to disk. See the "readme.txt" file in the project for instructions on usage.

When the database specified is SQL Server, parameter names are prefixed automatically with the character '@' by the code generation layer. However, when using the OleDb provider, no such assumption can be made since the underlying database could be one of many. To help solve this problem, the custom attribute ParamPrefixChar is defined to allow you to control this on a per interface basis when using the OleDb provider.

During the lifetime of an application, if two or more calls are made to SprocFactory.CreateInstance() specifying the same interface type as a parameter, the first call causes the generated class type information to be cached in an internal hash table—the second, third or nth call will retrieve the cached class type information from the hash table and pass it to Activator.CreateInstance().

From a design perspective, you may want to define more than one interface to hold all your wrapper methods. For example, if you have twenty stored procedures in the application to invoke, it may not make sense to define a single interface that has twenty methods in it. Perhaps you want to partition the wrapper methods into multiple interfaces, where the interface name corresponds to the table name the stored procedure operates on. Whatever policy you feel makes sense for your application, the code generation layer will support it, because SprocFactory.CreateInstance() can be called as many times as you like with a different interface type each time (and of course you can pass the same interface type each time if you wanted to). Ultimately, the decision as to whether you define one interface with twenty methods or two interfaces with ten methods each is up to you.

Limitations

There are several known limitations to the code generation layer and they are as follows:

  1. The following table shows the CLR data types that were tested as both input and output parameters when calling a stored procedure. System.Data.SqlTypes are also supported but not listed here.

    CLR Type C# alias Visual Basic .NET alias
    System.Int16 short Short
    System.Int32 int Integer
    System.Int64 long Long
    System.Single float Single
    System.Double double Double
    System.DateTime System.DateTime Date
    System.Boolean bool Boolean
    System.Decimal decimal Decimal
    System.String string String
    System.Guid System.Guid System.Guid
    System.Byte byte Byte
    *System.Byte [] byte [] Byte ()

    *supported as an input parameter only

  2. When calling a stored procedure that has output parameters, the parameters of the corresponding interface method should use the ref keyword in C# (do not use the out keyword) and the ByRef keyword in Visual Basic .NET. This means the parameter must be set to a valid value prior to making the call.

  3. The following database providers are supported:

    1. SQL Server – generated code will work in terms of types from the System.Data.SqlClient namespace e.g. SqlCommand / SqlDataAdapter etc
    2. OLE DB – generated code will work in terms of types from the System.Data.OleDb namespace e.g OleDbCommand / OleDbDataAdapter etc. It should be noted that this provider has only been tested using the OLE DB driver against Microsoft® SQL Server™ 2000.

Wrap-Up

AutoSproc is a code generation tool that makes stored procedures callable as methods on a runtime generated class. The developer simply has to design an interface that is populated with methods whose name(s) and signature(s) match the stored procedures in the database. Type information for the interface is then passed to the tool at runtime which in turn emits a class that implements the designated interface. The interface method implementations on the class are generated using System.Reflection.Emit

Several individuals have contributed ideas and suggestions that have improved both the article and the code and Steve would like to thank the following people for their input: Ian Griffiths, Bob Beauchemin, Ross Cockburn and Dafydd Hughes.

Steve Rodgers is an independent software consultant living in Brisbane. He spends most of his time helping customers who are building .NET Enterprise applications.