Click here to Skip to main content
11,575,925 members (57,209 online)
Click here to Skip to main content

C# Code Generator for Stored Procedures

, 20 Feb 2006 330.9K 5.2K 233
Rate this:
Please Sign up or sign in to vote.
This stored procedure will generate all the C# code for calling any Stored Procedure


Ever got fed up with creating all the code behind parameters for your stored procedures? I have. So, I wrote this stored proc to do the code for me. (I love code that writes code). 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 "tools_CS_SPROC_Builder".

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 tools_CS_SPROC_Builder '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 "tools_CS_SPROC_Builder" 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

@objName nvarchar(100)
Name:  CS SPROC Builder
Version: 1
Date:  10/09/2004
Author:  Paul McKenzie
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 @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
//Change the following variable to the name of your connection instance
SET @connName='conn.Connection'
SET @parameterAt=''
 dbo.sysobjects.xtype AS ObjType, AS ColName, 
 dbo.syscolumns.colorder AS ColOrder, 
 dbo.syscolumns.length AS ColLen, 
 dbo.syscolumns.colstat AS ColKey, 
INTO #t_obj
 dbo.syscolumns INNER JOIN
 dbo.sysobjects ON = INNER JOIN
 dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
 ( = @objName) 
 (dbo.systypes.status <> 1) 

SET @parameterCount=(SELECT count(*) FROM #t_obj)

IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName

IF(@errMsg is null)
  PRINT 'try'
  PRINT '   {'
  PRINT '   SqlParameter[] paramsToStore = 
new SqlParameter[' + cast(@parameterCount as varchar) + '];'
  PRINT ''
  DECLARE @source_name nvarchar,@source_type varchar,
    @col_name nvarchar(100),@col_order int,@col_type varchar(20),
    @col_len int,@col_key int,@col_xtype int,@col_redef varchar(20)
  SELECT * FROM #t_obj
  OPEN cur
  -- Perform the first fetch.
  INTO @source_name,@source_type,@col_name,@col_order,
  if(@source_type=N'U') SET @parameterAt='@'
  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
   SET @col_redef=(SELECT 
      CASE @col_xtype
    WHEN 34 THEN 'Image'
    WHEN 35 THEN 'Text'
    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'

      END AS C) 
   --Write out the parameter
   PRINT '   paramsToStore[' + cast(@col_order-1 as varchar) 
    + '] = new SqlParameter("' + @parameterAt + @col_name
    + '", SqlDbType.' + @col_redef
    + ');'

   --If the type is a string then output the size declaration
    PRINT '   paramsToStore[' + cast(@col_order-1 as varchar) 
     + '].Size=' + cast(@col_len as varchar) + ';'
   PRINT '   paramsToStore['+ cast(@col_order-1 as varchar) 
    + '].Value =  ;'
      -- This is executed as long as the previous fetch succeeds.
   INTO @source_name,@source_type,@col_name,@col_order,
  PRINT ''
  PRINT '   SqlHelper.ExecuteNonQuery(' + @connName + 
  ', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);'
  PRINT '   }'
  PRINT 'catch(Exception excp)'
  PRINT '   {'
  PRINT '   }'
  PRINT 'finally'
  PRINT '   {'
  PRINT '   ' + @connName + '.Dispose();'
  PRINT '   ' + @connName + '.Close();'
  PRINT '   }'  
  CLOSE cur

if(LEN(@errMsg)>0) PRINT @errMsg


Output Example

   SqlParameter[] paramsToStore = new SqlParameter[9];
   paramsToStore[0] = new SqlParameter("@organisationid", SqlDbType.BigInt);
   paramsToStore[0].Value =  ;
   paramsToStore[1] = new SqlParameter("@DisplayName", SqlDbType.NVarChar);
   paramsToStore[1].Value =  ;
   paramsToStore[2] = new SqlParameter("@DefaultCurrencyFID", SqlDbType.Int);
   paramsToStore[2].Value =  ;
   paramsToStore[3] = new SqlParameter("@TaxCode", SqlDbType.NVarChar);
   paramsToStore[3].Value =  ;
   paramsToStore[4] = new SqlParameter("@UserFID", SqlDbType.BigInt);
   paramsToStore[4].Value =  ;
   paramsToStore[5] = new SqlParameter("@IsClient", SqlDbType.Bit);
   paramsToStore[5].Value =  ;
   paramsToStore[6] = new SqlParameter("@IsContractor", SqlDbType.Bit);
   paramsToStore[6].Value =  ;
   paramsToStore[7] = new SqlParameter("@IsSupplier", SqlDbType.Bit);
   paramsToStore[7].Value =  ;
   paramsToStore[8] = new SqlParameter("@IsDesigner", SqlDbType.Bit);
   paramsToStore[8].Value =  ;
CommandType.StoredProcedure,"usp_Insert_Organisation", paramsToStore);
catch(Exception excp)

All you then have to do is copy-paste and fill in the values you want to pass and catch any exceptions... (Yes you do have to write some code!).

Points of Interest

In order for me to work out the @col_xtype variable from sysobjects, I ran lots of tests on a table I created with every type variation in both directions. It certainly works fine for all the usual data types, but I haven't had a chance to test all types within .NET, so if you find a bug, let me know. Enjoy!


  • Feb 20, 2006 - updated the script to version 1.1 which includeds two additions:
    1. UniqueIdentifier data type support.
    2. Support for ParameterDirection.Output.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Software Developer (Senior)
United Kingdom United Kingdom
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Md. Marufuzzaman25-Feb-12 7:24
mentorMd. Marufuzzaman25-Feb-12 7:24 
GeneralMy vote of 5 Pin
rajyarr17-Dec-11 15:46
memberrajyarr17-Dec-11 15:46 
QuestionThanks Pin
TabishSagheer7-Dec-11 19:38
memberTabishSagheer7-Dec-11 19:38 
GeneralFREE C# Code Generator - Pin
Member 131575514-Jan-10 1:38
memberMember 131575514-Jan-10 1:38 
GeneralFree Web C# CRUD Generator - Pin
netdevelop16-Apr-09 10:29
membernetdevelop16-Apr-09 10:29 
QuestionSQL Server only? Pin
PIEBALDconsult23-Mar-08 16:19
memberPIEBALDconsult23-Mar-08 16:19 
Generalbravo! this has been good help - thank you Pin
sonicoal4-Mar-08 22:31
membersonicoal4-Mar-08 22:31 
GeneralAdded a few things... Pin
robrichard29-Aug-07 17:08
memberrobrichard29-Aug-07 17:08 
GeneralThanks for Sharing Pin
/randz1-Aug-07 14:38
member/randz1-Aug-07 14:38 
Generaltimestamp data type Pin
mike5359-Jul-07 8:39
membermike5359-Jul-07 8:39 
GeneralWell Done!!! Pin
D Strauss24-Jun-07 4:08
memberD Strauss24-Jun-07 4:08 
General Pin
Gary Brewer4-Jun-07 14:21
memberGary Brewer4-Jun-07 14:21 
Very handy tool thanks a lot.

If you get a chance have a look at GENNIT @ it is a code generator tool which uses industrial strength foundations (NHibernate / ActiveRecord) to build a DAL from scratch (as well as other generators such as XHTML and Facebook API), all using online tools - you download a zip file containing the VS project with everything you designed, ready to go.
GeneralBrilliant Pin
Tomaž Štih2-May-07 23:34
memberTomaž Štih2-May-07 23:34 
GeneralVery useful tool Pin
andy_fcm8-Apr-07 14:51
memberandy_fcm8-Apr-07 14:51 
GeneralADO.NET question Pin
leahlegge9-Mar-07 11:18
memberleahlegge9-Mar-07 11:18 
GeneralGood idea, but may use SqlHelperParameterCache instead Pin
Phan Dung2-Mar-07 3:47
memberPhan Dung2-Mar-07 3:47 
GeneralMy generator - Pin
Xin Zhao10-Feb-07 5:03
memberXin Zhao10-Feb-07 5:03 
GeneralRe: My generator - Pin
zhaojicheng25-Apr-07 17:30
memberzhaojicheng25-Apr-07 17:30 
GeneralRe: My generator - Pin
chunchun200525-May-09 10:13
memberchunchun200525-May-09 10:13 
GeneralRe: My generator - Pin
SupermanDT1-Aug-07 9:25
memberSupermanDT1-Aug-07 9:25 
GeneralRe: My generator - Pin
alpha7-Dec-07 9:12
sussalpha7-Dec-07 9:12 
GeneralRe: My generator - Pin
Hamed Mosavi3-Oct-08 4:07
memberHamed Mosavi3-Oct-08 4:07 
Questionoutput variables Pin
chacimulch10-Nov-06 3:51
memberchacimulch10-Nov-06 3:51 
GeneralCool, but not quite what I'm looking for Pin
Judah Himango1-Mar-06 4:59
memberJudah Himango1-Mar-06 4:59 
NewsI GOT WHAT YOUR LOOKING FOR! how bad do u want it? lol. [modified] Pin
meaningoflights5-Jun-07 0:05
membermeaningoflights5-Jun-07 0:05 
GeneralCONVERTED TO VB.NET & Enterprise Library 2.0 Data Access Application Block Pin
jAY wASSER28-Feb-06 10:35
memberjAY wASSER28-Feb-06 10:35 
GeneralMS DAAB Jan 2006 Version Pin
zac everett5-Feb-06 22:34
memberzac everett5-Feb-06 22:34 
GeneralCodes for SQLTypes Pin
solusoft22-Dec-05 0:46
membersolusoft22-Dec-05 0:46 
QuestionOracle Procedure? Pin
jhtang16-Dec-05 9:18
memberjhtang16-Dec-05 9:18 
GeneralDAL Generator Pin
m2xdam15-Nov-05 20:53
memberm2xdam15-Nov-05 20:53 
GeneralRe: DAL Generator Pin
Owen Gunter6-Jan-06 0:10
memberOwen Gunter6-Jan-06 0:10 
Generalmissing UniqueIdentifier Pin
mliss4-Nov-05 10:51
membermliss4-Nov-05 10:51 
GeneralYou are AWESOME! Pin
drkwtkns14-Apr-05 3:31
memberdrkwtkns14-Apr-05 3:31 
Generalgreat stuff Pin
swamp631-Jan-05 10:39
memberswamp631-Jan-05 10:39 
GeneralNice... Pin
theJazzyBrain13-Nov-04 4:04
membertheJazzyBrain13-Nov-04 4:04 
GeneralNice code Pin
Phan Dung27-Oct-04 18:14
memberPhan Dung27-Oct-04 18:14 
GeneralSqlHelperParameterCache Pin
He Who Codes7-Oct-04 12:56
memberHe Who Codes7-Oct-04 12:56 
GeneralMDAB params overload Pin
SteveBacon7-Oct-04 1:50
memberSteveBacon7-Oct-04 1:50 
GeneralGreat idea Pin
Mark Morgen6-Oct-04 6:39
memberMark Morgen6-Oct-04 6:39 
GeneralRe: Great idea Pin
McCodeJunky7-Oct-04 7:49
memberMcCodeJunky7-Oct-04 7:49 
GeneralUse DeriveParameters() instead Pin
Jeff Firestone6-Oct-04 6:23
memberJeff Firestone6-Oct-04 6:23 
GeneralRe: Use DeriveParameters() instead Pin
McCodeJunky7-Oct-04 7:41
memberMcCodeJunky7-Oct-04 7:41 
GeneralRe: Use DeriveParameters() instead Pin
Jeff Firestone7-Oct-04 8:27
memberJeff Firestone7-Oct-04 8:27 
Generalnice approach Pin
Ashley van Gerven30-Sep-04 18:17
memberAshley van Gerven30-Sep-04 18:17 
GeneralRe: nice approach Pin
McCodeJunky1-Oct-04 3:03
memberMcCodeJunky1-Oct-04 3:03 
GeneralRe: nice approach Pin
edward.collum7-Oct-04 2:11
memberedward.collum7-Oct-04 2:11 
GeneralRe: nice approach Pin
Oskar Austegard28-Feb-06 12:07
memberOskar Austegard28-Feb-06 12:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 20 Feb 2006
Article Copyright 2004 by PaulMcKenzie
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid