Ever got fed up with creating all the code behind parameters for your stored procedures? I have. So, I wrote this stored proc to generate the C# code for me. This has been written for use with “Microsoft Data Application Block” (SQLHelper.cs), however it could be hacked around to write the code without using the MDAB or even to write the code in VB. The script handles both input and output parameters, setting the size of text types, and you could even use it for direct access to a view or table.

Using the code

To implement, just copy the code into Query Analyzer and run. This will create a SPROC called “usp_getCode”.

To use, just execute the SPROC passing the name of the SPROC you want the code for (see below). Note: don’t include any owner prefix, e.g.: (dbo.).

EXEC usp_getCode ‘mySprocsNameHere’

The message window in the Query Analyzer will write out all the code required for your class or code behind. There is a variable in “usp_getCode” called “@connName” which you can set to the name of your connection instance. By default, it’s set to “conn.Connection” – just alter this for your own project’s naming convention.

The code

CREATE PROCEDURE usp_getCode
(
@objName nvarchar(100)
)
AS
/*
___________________________________________________________________
Name: CS SPROC Builder
Version: 1
Date: 07/30/2010
Author: Shabbir
Description: Call this stored procedue passing the name of your
database object that you wish to insert/update
from .NET (C#) and the code returns code to copy
and paste into your application. This version is
for use with “Microsoft Data Application Block”.

DECLARE cur CURSOR FOR
SELECT * FROM #t_obj
OPEN cur
— Perform the first fetch.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype

if(@source_type=N’U’) SET @parameterAt=’@’
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col_redef=(SELECT CASE @col_xtype
WHEN 34 THEN ‘Image’
WHEN 35 THEN ‘Text’
WHEN 36 THEN ‘UniqueIdentifier’
WHEN 48 THEN ‘TinyInt’
WHEN 52 THEN ‘SmallInt’
WHEN 56 THEN ‘Int’
WHEN 58 THEN ‘SmallDateTime’
WHEN 59 THEN ‘Real’
WHEN 60 THEN ‘Money’
WHEN 61 THEN ‘DateTime’
WHEN 62 THEN ‘Float’
WHEN 99 THEN ‘NText’
WHEN 104 THEN ‘Bit’
WHEN 106 THEN ‘Decimal’
WHEN 122 THEN ‘SmallMoney’
WHEN 127 THEN ‘BigInt’
WHEN 165 THEN ‘VarBinary’
WHEN 167 THEN ‘VarChar’
WHEN 173 THEN ‘Binary’
WHEN 175 THEN ‘Char’
WHEN 231 THEN ‘NVarChar’
WHEN 239 THEN ‘NChar’
ELSE ‘!MISSING’
END AS C)