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 
Posted by devio 
