Auto-Generate Wrapper Classes for Stored Procedures Using the AutoSproc Tool

Auto-Generate Wrapper Classes for Stored Procedures Using the AutoSproc Tool

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


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.

ISprocBase.AutoClose Property

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.




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.




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.







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


2.
Public Shared Function CreateInstance (ByVal itf as Type, _
ByVal prov as DBProvider, _
                  autoClose as Boolean) as Object 
3
Public Shared Function CreateInstance (ByVal itf as Type, _
ByVal conn as IDbConnection) _
as Object
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.