Compile Time Stored Procedures

(code for this project can be found here)

One of the largest problems with interacting with databases is the lack of compile time assurance that the application code and the SQL code interact correctly. Testing helps with finding the errors, but as a system grows in size and complexity it becomes both difficult and costly to find every mistake. There are pre-made solutions such as F# Type Providers or Entity Framework, but these present their own challenges such as not having the project in F# or avoiding the heavy handedness of Entity Framework.

Fortunately SQL server provides enough information about Stored Procedures in the Metadata tables that makes it possible to create a from scratch compile time safe mapping between stored procedures and application code. Combining this with
T4 Text Templates it’s possible to automatically generate all the code necessary to handle stored procedure inputs and returns.

The first part is to pull all the stored procedure data from the sys.procedures view.

I remove all the procedures starting with sp_ as this prefix is meant to designate that it is a system procedure for use by SQL Server. The three important pieces from the table are the Procedure Name (p.name), Object Identifier (Object_Id), and the Schema Name (s.name).

Select Sys Procedures

Armed with the object id it's now possible to pull all the data for a procedures parameters from the sys.parameters view.

With this and the procedure's name it's possible to construct code to call SQL Server stored procedures which is compile time safe. Simply generate a method which takes in the appropriate parameters to call the stored procedure. The last step is to generate a lookup table which translates the SQL datatypes to ones which are valid C# syntax.

Idenfitfing which parameters have a default value and be not passed to the procedure

Since stored procedure parameters can have default values and therefore aren't necessary to populate, it would be nice to be able to know which one's are available but aren't required. This is a little trickier. There is a field has_default_value which looks like it would tell you if the parameter has a default value and therefore is optional, but there is a problem in the SQL Server metadata table, and it is only correct for CLR parameters, and not SQL ones. In order to actually find out if parameter has a default value, there are two options.

  1. Use Sql Management Objects (SMO)
  2. Pull the stored procedure code using something like SELECT OBJECT_DEFINITION (PROCEDURE_OBJECT_ID)

Using SMO to pull the definition is actually relatively simple assuming you have access to the appropriate dlls from Sql Server Management Studio.

Code:

If you don’t have access to the SMO dlls, you can still use something akin to a regular expression (or some other parsing method) to pull the parameter information out from the Object_Definition. As with parsing anything, there are a number of gotchas to watch out for, but it is possible with a little bit of work.

User Defined Tables

Starting in SQL Server 2008 it is possible to pass tables as parameters into stored procedures. There is view called sys.table_types which lists all of the user defined tables and has the necessary columns to link it back to which stored procedures use it as a parameter, and retrieve its column definition.

Stored Procedure Output

To automatically generate C# code for SQL Server output, you’ll really need SQL Server 2012 and later for it to be effective. In that edition they added, the dm_exec_describe_first_result_set_for_object procedure

This procedure takes a stored procedure Object_Id and returns the column information concerning the first result set it finds.

Result Set

The great thing about this procedure is that not only can you generate classes which can automatically update their types and names based on a result set that changes during development, but the procedure will return nothing if the stored procedure is non functional because of a changes in the database. By running this this and having it map all the result sets, the ones which come up blank which should return data indicate the procedure will not work with the current schema and needs to be fixed. It quickly helps eliminate the difficulties of knowing which procedures will break during program execution.

Putting It All Together

With these procedure, all that is really necessary is something that opens a connection to the database, pulls the necessary data and create the appropriate C# files.

As an example and a quick starter I’ve created an example solution. The SQL metadata retrieval is in the Classes.tt file, and all the code C# generation is in the StoredProcedures.tt file. The database I used to generate the example code is found here, and uses the Adventure Works Database.