Click here to Skip to main content
15,305,508 members
Articles / Database Development / SQL Server
Posted 30 Jun 2004


87 bookmarked

Wrapper Generator for SQL Server Stored Procedures

Rate me:
Please Sign up or sign in to vote.
4.46/5 (20 votes)
30 Jun 20042 min read
A utility application that generates .NET code for wrapping stored procedures.

Sample Image - SPGenerator.png


I recently became very tired of continuously rewriting the same code over and over again. In my case, it was code to access stored procedures in a SQL Server database. The end result of my frustration is the application presented here.


A search on CodeProject located an article by leppie describing a DBHelper class that he had developed. After test driving the application, I was hooked. Unfortunately, the existing code base did not handle the return value from stored procedures nor did it handle output parameters from the stored procedures. The original article is available here.

I could have used the built in support in Visual Studio and had it generate SqlCommand classes for me. This approach however scatters the database access throughout the application and makes it extremely fragile in the face of changes.

My solution was to use the ideas that I had seen in leppie's article and extend them to handle both return values and output parameters.

Using the application

To use this application, all you have to do is point the application at an existing SQL Server, select the stored procedures that you want wrappers generated for, and hit the lightning bolt. This application and all source code (with the exception of the GetSQL class; for information on it, see this article) are completely free for whatever use you see fit.

Generated Source Code

The generated code provides a single static method on the class that you specify for each stored procedure. The method has the same name as the stored procedure. The sample below is the generated code for a single stored procedure. Using the generated source is as easy as passing in the connection object.

User Code

private void somefunc()
   int iRet=0;
   int iNewId=0;
   iRet = heatgmsm_DAL.wl_AddUser(myConn,null,"User",
           "password",false,false, false,true,ref iNewId);

Generated Code

namespace heatgmsm
using System.Data;
using System.Data.SqlClient;

public class heatgmsm_DAL
  private heatgmsm_DAL() //Private since this is never meant to be instaniated

  public static int wl_AddUser(System.Data.SqlClient.SqlConnection connection, 
            System.Data.DataTable table, string uname, string pwd, 
            bool IsUserAdmin, bool IsProviderAdmin, bool IsWaitlistAdmin, 
            bool IsUser, ref int uid)
    int RETURN_VALUE = 0;

    System.Data.SqlClient.SqlCommand cmd = null;
    System.Data.SqlClient.SqlDataReader reader = null;

    if ((connection == null))
      throw new System.ArgumentException("The connection object cannot be null");
      if ((connection.State == System.Data.ConnectionState.Closed))
        cmd = new System.Data.SqlClient.SqlCommand("wl_AddUser", 
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                    System.Data.SqlDbType.Int, 0);
        cmd.Parameters["@RETURN_VALUE"].Direction = 
        cmd.Parameters["@RETURN_VALUE"].Value = RETURN_VALUE;
        cmd.Parameters.Add("@uname", System.Data.SqlDbType.VarChar, 50);
        cmd.Parameters["@uname"].Direction = 
        cmd.Parameters["@uname"].Value = uname;
        cmd.Parameters.Add("@pwd", System.Data.SqlDbType.VarChar, 50);
        cmd.Parameters["@pwd"].Direction = System.Data.ParameterDirection.Input;
        cmd.Parameters["@pwd"].Value = pwd;
        cmd.Parameters.Add("@IsUserAdmin", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsUserAdmin"].Direction = 
        cmd.Parameters["@IsUserAdmin"].Value = IsUserAdmin;
        cmd.Parameters.Add("@IsProviderAdmin", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsProviderAdmin"].Direction = 
        cmd.Parameters["@IsProviderAdmin"].Value = IsProviderAdmin;
        cmd.Parameters.Add("@IsWaitlistAdmin", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsWaitlistAdmin"].Direction = 
        cmd.Parameters["@IsWaitlistAdmin"].Value = IsWaitlistAdmin;
        cmd.Parameters.Add("@IsUser", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsUser"].Direction = 
        cmd.Parameters["@IsUser"].Value = IsUser;
        cmd.Parameters.Add("@uid", System.Data.SqlDbType.Int, 0);
        cmd.Parameters["@uid"].Direction = 
        cmd.Parameters["@uid"].Value = uid;

        if ((table != null))
          reader = cmd.ExecuteReader();

        if (((table != null) && (reader != null)))
          for (int i = 0; (i < reader.FieldCount); i = (i + 1))
            System.Type __type;
            string __name;
            __type = reader.GetFieldType(i);
            __name = reader.GetName(i);
            table.Columns.Add(__name, __type);

          for (; reader.Read();)
            System.Data.DataRow row = table.NewRow();
            object[] rowdata = new object[reader.FieldCount];
            row.ItemArray = rowdata;

        // The Parameter @RETURN_VALUE is not an output type
        // The Parameter @uname is not an output type
        // The Parameter @pwd is not an output type
        // The Parameter @IsUserAdmin is not an output type
        // The Parameter @IsProviderAdmin is not an output type
        // The Parameter @IsWaitlistAdmin is not an output type
        // The Parameter @IsUser is not an output type
        uid = ((int)(cmd.Parameters["@uid"].Value));


        RETURN_VALUE = ((int)(cmd.Parameters["@RETURN_VALUE"].Value));
        return RETURN_VALUE;
        throw new System.ArgumentException("The connection" + 
           " must be closed when calling this method.");

As you can see, the amount of code generated, in other words code that you no longer have to write, is quite extensive.

Future Directions

Any and all comments, suggestions, and or feature requests are welcome :).


  • July 1st 2004 -- Initial submission.
  • July 1st 2004
    • Updated to use the GetSQL class from the excellent article by Micheal Potter. This allowed me to eliminate the use of my SQLUtils.dll helper library.
    • Added a refresh option to the checked list box context menu.
    • Fixed a bug in the application idle event handler that was causing CPU spikes after items had been selected in the checked list box.
  • July 2nd 2004
    • Fixed a bug pointed out by jobr1ch, namely incorrect method names where generated for stored procedures with spaces in the name. Spaces are now replaced with an underscore character. So, 'Sales By Year' becomes 'Sales_By_Year' instead. Thanks for pointing that out jobr1ch :).
    • Sorted the stored procedures by name.


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

Charles Horan
Web Developer
Canada Canada
I have been a sofware developer for over 20 years. I first started out developing programs on an old PDP8 in my highschool. Over the years I have seen many changes to our profession. The one constant however has been the need for understanding the problem space at hand. Once you have that understanding the solution space tends to solve itself.

Comments and Discussions

QuestionObjectDatasource Pin
EdenMachine221-Nov-06 5:20
MemberEdenMachine221-Nov-06 5:20 
GeneralReally suberb Pin
vivekthangaswamy26-Oct-05 2:22
professionalvivekthangaswamy26-Oct-05 2:22 
GeneralA reliable way Pin
chris Liang30-Aug-05 9:38
Memberchris Liang30-Aug-05 9:38 
GeneralRe: A reliable way - a free one! Pin
Thomas Schittli28-Sep-05 3:41
MemberThomas Schittli28-Sep-05 3:41 
... or just use one of the many free tools like MyGeneration:

Kind regards,

People who wait until the eleventh hour to call on Jesus die at 10:30.
Generalgenerated code may be a lot better Pin
nsimeonov16-Aug-05 1:32
Membernsimeonov16-Aug-05 1:32 
Generalcalling storedprocedure names having spaces Pin
Member 180312514-Mar-05 12:27
MemberMember 180312514-Mar-05 12:27 
GeneralMyGeneration - it's free Pin
MyGeneration Software17-Dec-04 5:13
MemberMyGeneration Software17-Dec-04 5:13 
GeneralAdd Windows NT Integrated Security Pin
BaileyMW10-Jul-04 7:49
MemberBaileyMW10-Jul-04 7:49 
GeneralVery Small Suggestion Pin
Daaron8-Jul-04 16:05
MemberDaaron8-Jul-04 16:05 
GeneralRe: Very Small Suggestion Pin
Charles Horan8-Jul-04 16:29
MemberCharles Horan8-Jul-04 16:29 
Generalhaha Pin
thekilla_184-Dec-05 16:34
Memberthekilla_184-Dec-05 16:34 
QuestionIs it really more efficient to build your own DataTables? Pin
SethMW8-Jul-04 5:00
MemberSethMW8-Jul-04 5:00 
AnswerRe: Is it really more efficient to build your own DataTables? Pin
Charles Horan8-Jul-04 6:02
MemberCharles Horan8-Jul-04 6:02 
Generalsp_helpdb Pin
betterc7-Jul-04 1:30
Memberbetterc7-Jul-04 1:30 
GeneralRe: sp_helpdb Pin
Charles Horan7-Jul-04 5:17
MemberCharles Horan7-Jul-04 5:17 
GeneralHave u think in use DAAB Pin
Bruno Capuano6-Jul-04 11:42
MemberBruno Capuano6-Jul-04 11:42 
GeneralAlternative method to populate datatable... Pin
Arjan Einbu4-Jul-04 21:15
MemberArjan Einbu4-Jul-04 21:15 
GeneralRe: Alternative method to populate datatable... Pin
Charles Horan4-Jul-04 22:33
MemberCharles Horan4-Jul-04 22:33 
GeneralRe: Alternative method to populate datatable... Pin
Arjan Einbu5-Jul-04 2:46
MemberArjan Einbu5-Jul-04 2:46 
GeneralReturning DataTables Pin
Dan Crowell3-Jul-04 19:33
MemberDan Crowell3-Jul-04 19:33 
GeneralRe: Returning DataTables Pin
Arjan Einbu3-Jul-04 21:15
MemberArjan Einbu3-Jul-04 21:15 
GeneralRe: Returning DataTables Pin
Charles Horan4-Jul-04 4:49
MemberCharles Horan4-Jul-04 4:49 
Generala few suggestions and a more advanced alternative Pin
Marc Sommer2-Jul-04 21:45
MemberMarc Sommer2-Jul-04 21:45 
GeneralRe: a few suggestions and a more advanced alternative Pin
Charles Horan4-Jul-04 4:56
MemberCharles Horan4-Jul-04 4:56 
GeneralVery good &amp; a note Pin
jobr1ch2-Jul-04 2:26
Memberjobr1ch2-Jul-04 2:26 

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

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