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


, 19 Nov 2004
Rate this:
Please Sign up or sign in to vote.
With agStoredProc, you can generate a C# class out of all the stored procedures in a MS-SQL database. No Microsoft ApplicationBlocks needed.


With all the time developing applications using stored procedures, generating classes that used this stored procs, I got sick of having to go through the same process all the time; name of stored proc, parameters, parameter type, parameter direction,... oh God, really, it was sickening. So once, I decided I wouldn't do this anymore. That's why I did this little application and decided to share it with you.

At the time I developed it, May 2004, I didn't know about Microsoft Application Blocks, so I had to hard code it, let's say so. If I had known before about MS App Blocks, I would have done it with it, but it was done already, and it works sort of fast: some 40,000 lines of code in five seconds. Some friend said to me I could have used the CodeDom namespace to do so, but honestly I only used SqlClient class and one miserable StringBuilder, so it was pure appending strings.

This is a picture of the running application:

Sample image

This is a little snippet of the code I use to get the parameters for each of the stored procs:

foreach (string str in commands)
  SqlCommand myCommand = new SqlCommand("SELECT * " + 
             + str + "'", mycon);
  SqlDataReader myReader = myCommand.ExecuteReader();
  if (myReader.HasRows)
    string prms = "";
    SqlCommand cmdParams = new SqlCommand("SELECT DATA_TYPE," + 
               " WHERE SPECIFIC_NAME = '"+str+"'", mycon2);
    SqlDataReader ParamsReader = cmdParams.ExecuteReader();
    int prm = 0;
    if (ParamsReader.HasRows)
        string prmname = ParamsReader.GetString(0);
        prms += ParamTypeToString(prmname) + " _" + 
                +", ";
    prms = prms.Remove(prms.Length -1, 1);
    strBuilder.Append("\n\n\tpublic void " + commands[i] + "(" + 
                      prms.Remove(prms.Length -1, 1)+")");
    string sqlcon = (this.tbConnection.Text == "")? "\"" + 
                     this.connection+"\"" : this.tbConnection.Text;
    strBuilder.Append("\n\t\tSqlConnection sqlCon = 
                                  new SqlConnection("+sqlcon+");");
    strBuilder.Append("\n\t\tSqlCommand myCommand = 
                    new SqlCommand(\""+commands[i]+"\", sqlCon);");
    strBuilder.Append("\n\t\tmyCommand.CommandType = 
      string _size = (myReader.IsDBNull(9)) ? "": ", " + 
      string param_name_whole = myReader.GetString(7);
      string param_name = param_name_whole.Remove(0,1);

As you can see, it was just some commands I run against the SQL Server. I didn't know about these commands but with the Help, I found them and it really worked.


Honestly, there are better ways to do this, but this was the first way I found. I didn't know about Application Blocks at the time and after I finished the application, I really wouldn't change the code because it really works and, for the use it has, it is pretty fine. It does go several times to the SQL Server for finding the stored procs, then parameters, etc., but I consider it runs real fast.

I expect that you can find some use out of it. Hope you enjoy it.


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

Alexander German
Web Developer
Spain Spain
I have been programming for a few years now, like since 1996. Currently working as freelance.
C#.NET MCP. Works with tools such as Delphi, C#.NET, VB.NET, ASP.NET, ADO.NET, XML, java, j2me, Oracle, mySQL, and Sql Server, etc, etc. Open to everything.

Comments and Discussions

GeneralPerfect PinmemberFrench Fry19-Sep-05 15:39 
GeneralGreat Start,,, PinmemberHugh Will Nevreaux2-Dec-04 7:51 
GeneralRe: Great Start,,, PinmemberAlexander German2-Dec-04 8:50 
GeneralEncapsulate SqlConnection PinmemberBloodBaz25-Nov-04 3:39 
GeneralRe: Encapsulate SqlConnection PinmemberAlexander German29-Nov-04 7:43 
GeneralCodegeneration PinmemberMarc Sommer20-Nov-04 22:46 
there is a nice and free utility for this. Have a look at mygeneration ( Mygeneration is a template based (VB.Net / C# / MS VBScript or JScript) code generation tool with schema / meta data support for most of the common databases (MS SQL / MS Access / MySQL / Oracle / ......). Thera are already templates for building classes from tables or stored procedures or even stored procedures (CRUD) from exisitng tables.
If you do not feel comfortable with the supplied templates, you even may write your own templates but still having the full advantage of using the schema information provided by mygeneration.
1. You do not have to care how to fetch schema information for different databases
2. You write only one template, but get code for different databases at the same time
3. The underlying code has not to be hardcoded, therefore it is more easy to edit and reuseable.
4. ....
5. ....
GeneralRe: Codegeneration PinmemberAlexander German21-Nov-04 5:33 
GeneralRe: Codegeneration PinmemberOskar Austegard24-Nov-04 8:13 
GeneralGood Utility Pinmembersides_dale19-Nov-04 17:22 
GeneralRe: Good Utility PinmemberAlexander German21-Nov-04 5:21 
GeneralRe: Good Utility Pinmembersides_dale21-Nov-04 14:42 

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
Web03 | 2.8.150327.1 | Last Updated 19 Nov 2004
Article Copyright 2004 by Alexander German
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid