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();
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
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;
if (invocation.Method.IsDefined(typeof(StoredProcedureAttribute), true))
StoredProcedureAttribute attr = (
SpMetadata data = DaoFactory.LoadProcMetadata(attr.Name);
SqlConnection conn = ((
if (conn == null)
conn = new SqlConnection(DaoFactory.ConnectionString);
closeConnection = true;
using (SqlCommand cmd = new SqlCommand(data.Name, conn))
cmd.CommandType = CommandType.StoredProcedure;
int paramIndex = 0;
foreach (SqlParameter paramType in data.Parameters)
SqlParameter param = new SqlParameter(
param.Direction = paramType.Direction;
param.Value = args[paramIndex];
paramIndex = 0;
foreach (SqlParameter param in cmd.Parameters)
args[paramIndex] = param.Value;
if (closeConnection && conn != null)
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
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
void InsertCustomer(string firstName,
string lastName, DateTime dateOfBirth, out int customerId);
void UpdateCustomer(int customerId, string firstName,
string lastName, DateTime dateOfBirth);
void DeleteCustomer(int customerId);
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.
- 23 May, 2007 - Original version posted