Click here to Skip to main content
Click here to Skip to main content

Wrapper Generator for SQL Server Stored Procedures

, 30 Jun 2004
Rate this:
Please Sign up or sign in to vote.
A utility application that generates .NET code for wrapping stored procedures.

Sample Image - SPGenerator.png

Introduction

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.

Background

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");
    }
    else
    {
      if ((connection.State == System.Data.ConnectionState.Closed))
      {
        connection.Open();
        cmd = new System.Data.SqlClient.SqlCommand("wl_AddUser", 
                                                      connection);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("@RETURN_VALUE", 
                                    System.Data.SqlDbType.Int, 0);
        cmd.Parameters["@RETURN_VALUE"].Direction = 
                       System.Data.ParameterDirection.ReturnValue;
        cmd.Parameters["@RETURN_VALUE"].Value = RETURN_VALUE;
        cmd.Parameters.Add("@uname", System.Data.SqlDbType.VarChar, 50);
        cmd.Parameters["@uname"].Direction = 
                             System.Data.ParameterDirection.Input;
        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 = 
                                        System.Data.ParameterDirection.Input;
        cmd.Parameters["@IsUserAdmin"].Value = IsUserAdmin;
        cmd.Parameters.Add("@IsProviderAdmin", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsProviderAdmin"].Direction = 
                                        System.Data.ParameterDirection.Input;
        cmd.Parameters["@IsProviderAdmin"].Value = IsProviderAdmin;
        cmd.Parameters.Add("@IsWaitlistAdmin", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsWaitlistAdmin"].Direction = 
                                        System.Data.ParameterDirection.Input;
        cmd.Parameters["@IsWaitlistAdmin"].Value = IsWaitlistAdmin;
        cmd.Parameters.Add("@IsUser", System.Data.SqlDbType.Bit, 0);
        cmd.Parameters["@IsUser"].Direction = 
                                         System.Data.ParameterDirection.Input;
        cmd.Parameters["@IsUser"].Value = IsUser;
        cmd.Parameters.Add("@uid", System.Data.SqlDbType.Int, 0);
        cmd.Parameters["@uid"].Direction = 
                                   System.Data.ParameterDirection.InputOutput;
        cmd.Parameters["@uid"].Value = uid;

        if ((table != null))
        {
          reader = cmd.ExecuteReader();
        }
        else
        {
          cmd.ExecuteNonQuery();
        }

        if (((table != null) && (reader != null)))
        {
          table.Clear();
          table.Columns.Clear();
          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];
            reader.GetValues(rowdata);
            row.ItemArray = rowdata;
            table.Rows.Add(row);
          }
          reader.Close();
        }

        // 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));

        connection.Close();

        RETURN_VALUE = ((int)(cmd.Parameters["@RETURN_VALUE"].Value));
        return RETURN_VALUE;
      }
      else
      {
        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 Smile | :) .

History

  • 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 Smile | :) .
    • Sorted the stored procedures by name.

License

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 PinmemberEdenMachine221-Nov-06 5:20 
GeneralReally suberb Pinmembervivekthangaswamy26-Oct-05 2:22 
GeneralA reliable way Pinmemberchris Liang30-Aug-05 9:38 
GeneralRe: A reliable way - a free one! PinmemberThomas Schittli28-Sep-05 3:41 
Generalgenerated code may be a lot better Pinmembernsimeonov16-Aug-05 1:32 
Generalcalling storedprocedure names having spaces PinmemberNumburisat14-Mar-05 12:27 
GeneralMyGeneration - it's free PinmemberMyGeneration Software17-Dec-04 5:13 
GeneralAdd Windows NT Integrated Security PinmemberBaileyMW10-Jul-04 7:49 
GeneralVery Small Suggestion PinmemberDaaron8-Jul-04 16:05 
GeneralRe: Very Small Suggestion PinmemberCharles Horan8-Jul-04 16:29 
Generalhaha Pinmemberthekilla_184-Dec-05 16:34 
QuestionIs it really more efficient to build your own DataTables? PinmemberSethMW8-Jul-04 5:00 
AnswerRe: Is it really more efficient to build your own DataTables? PinmemberCharles Horan8-Jul-04 6:02 
Generalsp_helpdb Pinmemberbetterc7-Jul-04 1:30 
GeneralRe: sp_helpdb PinmemberCharles Horan7-Jul-04 5:17 
GeneralHave u think in use DAAB PinmemberBruno Capuano6-Jul-04 11:42 
GeneralAlternative method to populate datatable... PinmemberArjan Einbu4-Jul-04 21:15 
GeneralRe: Alternative method to populate datatable... PinmemberCharles Horan4-Jul-04 22:33 
GeneralRe: Alternative method to populate datatable... PinmemberArjan Einbu5-Jul-04 2:46 
GeneralReturning DataTables PinmemberDanny Crowell3-Jul-04 19:33 
GeneralRe: Returning DataTables PinmemberArjan Einbu3-Jul-04 21:15 
GeneralRe: Returning DataTables PinmemberCharles Horan4-Jul-04 4:49 
Generala few suggestions and a more advanced alternative PinmemberMarc Sommer2-Jul-04 21:45 
GeneralRe: a few suggestions and a more advanced alternative PinmemberCharles Horan4-Jul-04 4:56 
GeneralVery good &amp; a note Pinmemberjobr1ch2-Jul-04 2:26 

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 | Mobile
Web04 | 2.8.140721.1 | Last Updated 1 Jul 2004
Article Copyright 2004 by Charles Horan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid