Stored Procedure class generator

• Download source - 11 Kb

Introduction

This is a small project to generate classes for accessing stored procedures via a webservice, born out of frustration with SQLXML 3.0.

Using the code

This is a very basic (and somewhat rough) code class that finds all the stored procedures in a SQL Server database, and generates a C# class to access them. This came about because I was looking for a fast way to expose several stored procedures via a web service layer I was working on. After a couple of days spent fooling around with SQLXML 3.0, and ultimately becoming a bit disappointed with the results, a code generator seemed like a good idea. So this is it. The code is not fancy at all. Just grabs a list of stored procedures, and uses a lot of string builders to produce a code class. I did look at some of the template based generators around on Code Project and elsewhere (a la Raptier), but at the end, didn't have much time to go through them.

There are two types of classes produced, depending on the setting of the 'webMethod' property. If set to true, then a few extra things are included to expose the methods via webservices. If you choose this option, the only thing produced is the code class. You'll still have to link it up to an .asmx page.

Another thing this generates is a app.config file, with a section containing all the procedure names, along with a boolean to indicate if the procedure should be accessible via the C# code. It defaults to 'false', which would mean that the procedure is restricted. Just change it to 'true' for the procedures you want to expose. The intention was to copy the section from the generated file into your 'real' .config file, although it seems to work fine, just as it is.

Before running the code, you will have to set the connection string (on line 17 of generateProcCode.cs) to something useful:

private string m_sconn = 
  "Persist Security Info=False;Integrated Security=false;" + 
  "user id=[lebowski];password=[thedude];database=arkis2db;" + 
  "server=[ipaddress];encrypt=false\";

I've included a small test project which should help you figure out the rest. Basically, just instantiate the class, set the webMethod property, and call the genClass method with a path for output as the only parameter. Once the stored procedures class is generated, you will have to change the namespace and set the connection string for your project.

Here is an example of the output:

///<summary>


///summary description for p_generateLoanID
///</summary>
public DataSet p_generateLoanID(
    out int RETURN_VALUE,
    int type,
    string aukt,
    ref int NewID){

    if(false == procRunStatus("p_generateLoanID")) 
        throw new Exception("Stored procedure p_generateLoanID not enabled");

    using(SqlConnection oconn = new SqlConnection(m_sconn)){
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand("p_generateLoanID",oconn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter prm_RETURN_VALUE = 
           cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
        SqlParameter prm_type = cmd.Parameters.Add("@type", type);
        SqlParameter prm_aukt = cmd.Parameters.Add("@aukt", aukt);
        SqlParameter prm_NewID = cmd.Parameters.Add("@NewID", NewID);

        prm_RETURN_VALUE.Direction = ParameterDirection.ReturnValue;
        prm_NewID.Direction = ParameterDirection.Output;
        oconn.Open();
        da.Fill(ds);
        RETURN_VALUE = 
          cmd.Parameters["@RETURN_VALUE"].Value == DBNull.Value ? -12345 : 
          (int)cmd.Parameters["@RETURN_VALUE"].Value;
        NewID = cmd.Parameters["@NewID"].Value == DBNull.Value ? -1 : 
          (int)cmd.Parameters["@NewID"].Value;
        return ds;
    }
}

Points of Interest

There may be some problems with methods handling type conversion between SQL and C#. I opted to just use string/int most of the time. This seems to be working fine in our apps, which mostly use uniqueidentifier, nvarchar[] and int as params. If you have other types of input params to your stored procedures, you may get some trouble.

I had another problem with some SQL params called '@ref'. I've tried to keep the param names the same in C# and SQL Server, but obviously that isn't going to work well with keywords. In this case, I just convert all C# params to 'reff' from 'ref'.

That's it. I hope someone might find this interesting or useful. I welcome all feedback, both positive and negative. I've been coding in a vacuum (only developer on site) for a few years, so please let me know if you think something could/should/must be done differently or better.