Introduction

It was with much interest that I saw Alex Grinberg’s article dealing with creating wrappers for calling stored procedures. His article echoed a central concern that I have encountered many times through my experience as a developer – why can’t a call to a SQL Server stored procedure be as simple as calling a function in one’s code.

We all know what it takes to call a stored procedure using ADO.NET (the prevalent technology for communicating with SQL Server). One has to define a command object and supply the stored procedure name, then define all the parameters specifying their name, type and direction. Finally the stored procedure can be invoked through the command object. If the stored procedure returns output through its arguments, those must be retrieved and if the stored procedure returns a result set, that result set must also be acquired. Furthermore all of this code has to be wrapped in some kind of exception handling logic and the transient database objects disposed of properly to recycle connections and resources. It is uncommon to have a page of code devoted to calling just one stored procedure.

I have always found this proposition rather depressing and have dealt with the rote drudgery of accessing the database by creating my own custom methods. However with the advent of .NET Framework and its set of new languages (C# and VB.NET) I think I have finally arrived at the holy grail of interacting with a SQL Server database – calling a stored procedure is now as simple as a one line method call that is validated at compile time. This has an additional benefit that a change to a stored procedure’s name or argument types and order can be detected at compile time thus greatly reducing bugs that would otherwise materialize at runtime.

I have a written a .NET component called ActiveDB (a nod to ActiveX technology and its ease of accessing complex systems) and which I peddle on www.mfbyte.com.

In this article I will cover my thinking and techniques I used to create ActiveDB.

Inspiration

Like I said before, I have always used my own custom code to make it easier to call stored procedures. The “Eureka” moment came when I noticed how dropping a UI control on a Windows Form in the newly released Visual Studio .NET created underlying application code. This code modified the appearance of the UI control by setting its various properties. I then thought that if I could create a custom control that could be dropped on a design surface and its properties set to point to a SQL Server database, the control could then generate the stored procedure invocation logic. From the beginning, since I had well exercised and flexible methods for calling stored procedures, it was never about generating pages and pages of code to simulate the brute force method of invoking stored procedures – it was more about creating the C# (or VB.NET) function stub that would be called from application code and that would tell the programmer through IntelliSense the name of the stored procedure, its arguments and their type. The code inside the stub would still be rather concise and rely on smart logic to do its job.

Implementation

I will concentrate on the SQL Server aspect of the development as it will be of more interest to the readers here. I will therefore skip the intricacies of Microsoft’s System.ComponentModel and how to use it to build components for Visual Studio .NET.

The first step in generating the code is to get a list of all user stored procedures. This is accomplished with the following query:

 SELECT      so.name,
 sc.name,
 sc.xtype,
 sc.isoutparam
 FROM        sysobjects so LEFT OUTER JOIN syscolumns sc ON sc.id = so.id
 WHERE       so.xtype = 'P'
 AND         SUBSTRING(so.name, 1, 3) <> 'dt_'
 ORDER BY    so.name, sc.colorder

This gives us all the stored procedure names, their parameters, the SQL type of the parameter and whether it is an output parameter. The SQL Server type of the parameter is used to derive the native .NET type corresponding to it. If a parameter is an output parameter, we can declare it as a ref argument in C# (ByRef in VB.NET).

An interesting situation comes about if a stored procedure parameter accepts a default value. In that case we can generate overloaded signatures for the stored procedure method call omitting the parameters with default values from the list of arguments (Note: This only works if the parameters with default values are at the end of the stored procedure’s parameter list).

Unfortunately there is no way to query the database schema to determine if a stored procedure argument takes a default value. I resort to reading the stored procedure definition into a varchar variable and doing syntactical parsing to see if there is an equal sign after the parameter’s type. Please let me know if there is a better method.

That takes care of the parameters. We must now determine if the stored procedure returns a result set. Alex does a search through the stored procedure text for a “SELECT” keyword. However this method is obviously very inaccurate. The technique I use is to create an EXEC query that calls the stored procedure and supplies a NULL for each required parameter and to prefix the call with “SET FMTONLY ON;“. Executing the query returns the definition of all the record sets the stored procedure could potentially return. If the query returns no record sets, we know the stored procedure is a scalar and can be executed through the ADO.NET Command object’s ExecuteNonQuery method. Just like Alex, if the stored procedure returns a record set, I like to return it as a DataSet object.

There is one more useful benefit of using “SET FMTONLY ON“. One can analyze the columns returned by the query and extract the column names and their types. This can be very useful information in our code generation component. One way that programmers typically refer to columns in a returned ADO.NET DataTable is by using the quoted name of the column as an index into the collection of row values (the other way is by using the ordinal position of the column). This method is fraught with mistakes as the programmer can easily mistype the name of the column not to mention someone changing the data returned by the stored procedure’s SELECT statement. The code generator allows us to create static (Shared in VB.NET) classes that return actual column names as properties. The programmer then does not have to worry about typing the column name correctly – he simply uses an IntelliSensed reference to a static property which returns the actual column name. What’s more, because we know the data type of the columns, we can generate an IntelliSense comment statement that will tell the programmer the .NET type of the column that he can then use to cast the value into a native .NET variable. Below is sample code generated for Northwind’s CustOrdersDetail stored procedure and a C# statement that takes advantage of that declaration:

 internal static class @CustOrdersDetail_Table0
{
 /// <summary>Type: String</summary>
       internalstatic string @ProductName { get { return"ProductName"; } }
       /// <summary>Type: Decimal</summary>
       internalstatic string @UnitPrice { get { return"UnitPrice"; } }
       /// <summary>Type: Int16</summary>
       internalstatic string @Quantity { get { return"Quantity"; } }
       /// <summary>Type: Int32</summary>
       internalstatic string @Discount { get { return"Discount"; } }
       /// <summary>Type: Decimal</summary>
       internalstatic string @ExtendedPrice { get { return"ExtendedPrice"; } }
}
 decimal nPrice = (decimal) dr[Northwind.CustOrdersDetail_Table0.ExtendedPrice];

Finally, the call to the actual stored procedure in the application’s code can be written like this:

 foreach (DataRow dr in Northwind.CustOrdersDetail(nOrderID).Tables[0].Rows)
{
       …
       …
}

For those curious, the invocation code inside the actual stub for the stored procedure is pretty small:

 internal static DataSet @CustOrdersDetail(Int32 @OrderID)
{
       DataSet ds = m_DBEngine.ExecDataSetSP(ConnectionString, "CustOrdersDetail", @OrderID);
       return ds;
}

Conclusion

Ideally it makes sense for Microsoft to tackle this whole problem of discontinuity between application code and the database. They keep taking some steps with the Enterprise Class Library, LINQ to SQL and the new Entity Framework. However for my taste I would like to see the database as part of my application with tables and stored procedures as design time references that can be accessed through code which will generate compile time errors if something in the underlying database changes. Until then, individual developers like me and Alex will be crafting their own little widgets to make it easier to bridge the gap between the program and its database.

For more information on my component, please visit www.mfbyte.com