Wrapper Generator for SQL Server Stored Procedures

• Download source files - 25.5 Kb
• Download demo project - 47.1 Kb

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 :).

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