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

Stored Procedure class generator

, 21 Nov 2004
Rate this:
Please Sign up or sign in to vote.
Generate code classes to access stored procedures via web services.

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.

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

Share

About the Author

b_hardman
Web Developer Gerson Lehrman Group
Ireland Ireland
Just a hopeless codemonkey trying to keep up with the times.

Comments and Discussions

 
GeneralGood Jolly Pinmemberxcorporation7-Jun-07 22:18 
Generaluse CodeDOM Pinsussleechdr19-Nov-04 16:00 
GeneralRe: use CodeDOM PinmemberPeter Hancock22-Nov-04 13:04 
GeneralRe: use CodeDOM Pinsussleechdr22-Nov-04 15:21 
GeneralRe: use CodeDOM PinmemberPeter Hancock23-Nov-04 16:38 

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.141216.1 | Last Updated 22 Nov 2004
Article Copyright 2004 by b_hardman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid