Generating a C# Stored Procedure Wrapper in TSQL

I love compiling languages. Whenever you change something that consequently does not fit the rest of the program, something breaks and you get a compiler error. Great!

If you develop database applications, this nice change-it-break-it mechanism stops to work, as the relation between database objects and their usage by a C# application is in no way hard-coded.

Therefore I developed a small piece of T-SQL code which will generate a static C# class which encapsulates all stored procedures in a given database in the form:

public static class MyStoredProc {
    public static SqlCommand MyProc1(SqlConnection conn)
    {
        SqlCommand cmd = new SqlCommand("MyProc1", conn);
        ...
        return cmd;
    }
    ...
}

The strategy is simple: loop through all desired procedures, and for each procedure loop through its parameters twice: once for the declaration, second time for the parameter values.

Create file and class header:

PRINT 'using System;
using System.Data;
using System.Data.SqlClient;
'

PRINT    'namespace ' + @NameSpace
PRINT    '{'
PRINT    'public static class ' + @Class + '
{'

Now we declare the loop for the procedure names (the code uses SQL2000 dictionary objects, which are also present on SQL2005; adaption should be simple):

DECLARE cP CURSOR FOR
SELECT  dbo.sysobjects.name
FROM     dbo.sysobjects
WHERE    (dbo.sysobjects.name NOT LIKE 'dt[_]%')
AND    ... other conditions ...
AND     (dbo.sysobjects.xtype = 'P')
ORDER BY dbo.sysobjects.name

OPEN cP
FETCH cP INTO @Proc

WHILE @@FETCH_STATUS = 0 BEGIN

PRINT '    public static SqlCommand ' + @Proc + '(SqlConnection conn'

Next we declare the cursor for the procedure parameters in the correct order:

DECLARE c CURSOR FOR
SELECT  dbo.syscolumns.name, dbo.systypes.name, dbo.syscolumns.length
FROM     dbo.sysobjects
INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE    (dbo.sysobjects.name = @Proc) AND (dbo.systypes.name <> N'sysname')
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

OPEN c
FETCH c INTO @Column, @Type, @Length

WHILE @@FETCH_STATUS = 0 BEGIN

For parameter declaration, we need to translate the SQL datatypes into C# datatypes. In this example, we use the following mapping:

int -> int?
datetime -> DateTime?
n/varchar -> string
bit -> bool?

PRINT  '        , ' +
    CASE @Type
    WHEN 'int' THEN 'int?'
    WHEN 'datetime' THEN 'DateTime?'
    WHEN 'nvarchar' THEN 'string'
    WHEN 'varchar' THEN 'string'
    WHEN 'bit' THEN 'bool?'
    ELSE '**datatype-not-supported**' END +
    ' ' + SUBSTRING(@Column, 2, LEN(@Column)-1)

All the wrapping method does is to create a SqlCommand using the SqlConnection:

PRINT ' )'
PRINT ' {'
PRINT '   SqlCommand cmd = new SqlCommand("' + @Proc + '", conn);'
PRINT '   cmd.CommandType = CommandType.StoredProcedure;'
PRINT '   cmd.CommandTimeout = 0;'

Then we loop through the parameters again to set the SqlCommand’s Parameters’ values. As shown above, parameter types are mapped to C# Nullable<T> types (question-mark notation), except for strings, as they can have the value null. Thus we have to treat string parameters differently from nullable (in C#) parameters:

OPEN c
FETCH c INTO @Column, @Type, @Length

WHILE @@FETCH_STATUS = 0 BEGIN

    IF @Type='nvarchar' OR @Type='varchar'
        PRINT ' cmd.Parameters.AddWithValue("' + @Column + '", ' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + ' != null ? (object)' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + ' : DBNull.Value);'
    ELSE
        PRINT ' cmd.Parameters.AddWithValue("' + @Column + '", ' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + '.HasValue ? (object)' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + '.Value : DBNull.Value);'

Finally, return the new SqlCommand:

PRINT '        return cmd;'
PRINT '    }'

Run this and paste the result into your C# application 😉