Click here to Skip to main content
Click here to Skip to main content

Stored Procedure Wrapper

, 23 May 2007
Rate this:
Please Sign up or sign in to vote.
An article on wrapping stored procedure invocations in strongly typed intefaces

Introduction

Calling stored procedures using the mechanism provided by .NET Framework requires a lot of boilerplate code. Usually, stored procedures are wrapped into methods of database access classes. The wrapping work must be done by a programmer and it is very boring. My idea is to automatically generate wrappers for stored procedures based on strongly typed interface definition and some metadata fetched from the DBMS. The solution uses Castle Project's DynamicProxy library to generate proxies. The source code contains the very first version of the solution, created only for the purposes of this demonstration. It only works with one DBMS, SQL Server, and contains very dirty code.

Using the code

DaoFactory class serves as a facade for the whole solution. It provides one static method for creating wrapper interface implementations.

public static T CreateDao>T>()
{
    ProxyGenerator gen = new ProxyGenerator();
    return (T)gen.CreateProxy(typeof(T), 
        new StubCallInterceptor(), new SpWrapperBase());
}

This class is also responsible for maintaining an internal cache of stored procedure metadata. The two other classes mentioned in the above code sample are StubCallInterceptor and SpWrapperBase. The former is the core of the solution responsible for calling stored procedures, while the latter provides the basic functionality of the wrapper instance, such as setting and retrieving database connection objects.

public object Intercept(IInvocation invocation, params object[] args)
{
    bool closeConnection = false;
    /*
     * Is the method is decorated with StoredProcedure attribute it 
     * should be stored procedure call
     */
    if (invocation.Method.IsDefined(typeof(StoredProcedureAttribute), true))
    {
        StoredProcedureAttribute attr = (
            StoredProcedureAttribute)invocation.Method.GetCustomAttributes(
            typeof(StoredProcedureAttribute), true)[0];
        /*
        * Stored procedure metadata is loaded here 
        * (by selecting rows from INFORMATION_SCHEMA.PARAMETERS view)
        */
        SpMetadata data = DaoFactory.LoadProcMetadata(attr.Name);
        /*
         * New connection is opened if not set in the property of 
         * interface implementation instance
         */
        SqlConnection conn = ((
            SpWrapperBase)invocation.InvocationTarget).Connection;
        if (conn == null)
        {
            conn = new SqlConnection(DaoFactory.ConnectionString);
            conn.Open();
            closeConnection = true;
        }
        try
        {
            using (SqlCommand cmd = new SqlCommand(data.Name, conn))
            {
                 cmd.CommandType = CommandType.StoredProcedure;
                 int paramIndex = 0;
                 /*
                 * Procedure parameters are created based on fetched 
                 * metadata and values of method invocation arguments
                 */
                 foreach (SqlParameter paramType in data.Parameters)
                 {
                     SqlParameter param = new SqlParameter(
                         paramType.ParameterName, paramType.SqlDbType);
                     param.Direction = paramType.Direction;
                     param.Value = args[paramIndex];
                     cmd.Parameters.Add(param);
                     paramIndex++;
                 }
                 paramIndex = 0;
                 cmd.ExecuteNonQuery();
                 /*
                 * To support out/inout parameters values must be 
                 * re-written ofter calling procedure.
                 */
                 foreach (SqlParameter param in cmd.Parameters)
                 {
                     args[paramIndex] = param.Value;
                     paramIndex++;
                 }                        
            }
            return null;
        }
        finally
        {
            if (closeConnection && conn != null)
            {
                conn.Dispose();
            }
        }
    }
    else
    {
        return invocation.Proceed(args);
    }
}

The intercepting method first tests whether the intercepted method call should be treated as a stored procedure call. It does so by checking for the existence of the StoredProcedure attribute. Next, the metadata object describing the stored procedure is retrieved from the internal cache. The metadata object contains information about procedure parameters, i.e. name, type, direction. It is assumed that the intercepted method's argument order is the same as the stored procedure's parameter order. Also, the types and directions must be the same. These assumptions are needed because the engine copies argument values to SqlParameter objects, simply iterating through the metadata object's parameter list.

Next, the real store procedure call is made through the SqlCommand object. After the call, all values from the parameter objects are re-written to the arguments array. This is needed in order to support the in and inout parameters, whose values get changed during the stored procedure call. These changed values must be returned to caller. Included in the prototype code is an example using Wrapper. This version contains CRD functionality for a customer table, but not read. The interface delivering this functionality looks like this:

public interface ICustomerDao : IDao
{
    [StoredProcedure("spInsertCustomer")]
    void InsertCustomer(string firstName, 
        string lastName, DateTime dateOfBirth, out int customerId);
    [StoredProcedure("spUpdateCustomer")]
    void UpdateCustomer(int customerId, string firstName, 
        string lastName, DateTime dateOfBirth);
    [StoredProcedure("spDeleteCustomer")]
    void DeleteCustomer(int customerId);
}

The IDao interface is here to provide access to SpWrapperBase functionality. In this prototype version, this consists of one single property to get or set the connection object. The three methods are mapped to store procedures whose names are provided in the StoredProcedure attribute. It is worth noting that when developing DB access code based on the Wrapper solution, it is only needed to provide interfaces such as ICustomerDao. All the rest is done via internal mechanisms of the Wrapper. The stored procedure code and database schema are included in the example package. When running the sample, don't forget to check/change settings in the App.config file.

Points of interest

I have written this code as a prototype; it is not meant to be used in any serious production solutions. I plan to write production-quality solutions based on this prototype. In addition to SQL Server, it will also support other database engines and will perform better although the stored procedure calling engine needs to be optimized.

History

  • 23 May, 2007 - Original version posted

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Szymon Pobiega
Software Developer (Senior) VSoft
Poland Poland
No Biography provided

Comments and Discussions

 
GeneralNice PinmemberSacha Barber23-May-07 8:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 23 May 2007
Article Copyright 2007 by Szymon Pobiega
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid