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

Using C# to Enumerate Through Stored Procedures in MS SQL Server 2000

, 3 Jan 2006
Rate this:
Please Sign up or sign in to vote.
This guide will show you how to enumerate through the stored procedures in MSSQL 2000, as well as retrieve parameter information for a stored procedure.

Introduction

This article will demonstrate how to enumerate through a stored procedure's parameters using C#, ASP.NET, and MS SQL Server 2000.

Background

After looking around 'net for some time trying to locate a simple way to enumerate through the parameters of a stored procedure, I decided to post the solution I have here. Why is it useful? Well, if you want to create a solution that can dynamically pass parameters to a stored procedure, it's quite useful. I used the solution to create a reporting application that will return data to a user (through a web application) by executing stored procedures on a SQL Server. But it was important that I allowed the user to provide parameters, such as report start date, or report end date, and I needed to be sure I could validate that the data from the input was the right type for the stored procedure's parameter. I'll show you how to do that in this article.

Using the Code

This code can be used in any C# application, web or otherwise, but the example will demonstrate usage in a web application. If you download the sample project, you will of course need VS.NET 2003, an instance of MS SQL Server 2000, and IIS running. You'll want to change the connection string in the Web.config to point to your local instance of MS SQL Server.

List the Stored Procedures

Since MS SQL Server doesn't provide an extended stored procedure for enumerating through stored procedures, we just do it directly using a SELECT command:

select name from dbo.sysobjects where type ='P' order by name asc

Our C#/ASP.NET function:

//Enumerate and load all stored procedures from the database 
private void loadStoredProcs()
{
    //Clear out the dropdownlist    
    ddlSPs.Items.Clear();
    SqlConnection cn = new SqlConnection(
      System.Configuration.ConfigurationSettings.
      AppSettings["ConnString"]);
    //We'll use a SQL command here. 
    //We use an adapter below. 
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "select name from sysobjects" + 
                      " where type='P' order by name asc";
    cmd.CommandType = CommandType.Text;
    cmd.Connection = cn;
     try 
    {
        cn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
             this.ddlSPs.Items.Add(rdr["name"].ToString());
        }
    }
     catch (Exception exc)
    {
         //Send the exception to our exception label 
         this.lblException.Text = exc.ToString();
    }
     finally 
    {
        cn.Close();
    }
}

List the Parameters for a Stored Procedure

Now that we can list all of the stored procedures, we'll use the following SQL to get a table listing the important columns for the parameters of a selected procedure. We can use the ID from sysobjects and the rest of the data from syscolumns to obtain all of the parameter and type information for each parameter of our selected stored procedure:

 select s.id , s.name, t.name as [type], t.length
 from  syscolumns s
 inner join systypes t
 on s.xtype = t.xtype 
 where id = (select id from sysobjects where name = 
             'sp_TheNameOfYourStoredProcedure')

Our C#/ASP.NET function:

 //Get all parameters for a specified stored procedure 
 private void bindParameters( string strName)
 {
    SqlConnection cn = new SqlConnection(
        System.Configuration.ConfigurationSettings.
        AppSettings["ConnString"]);
    //Use a string builder to hold our SQL command 
    StringBuilder sb = new StringBuilder();
    sb.Append("select s.id, s.name, t.name as [type], t.length ");
    sb.Append("from syscolumns s ");
    sb.Append("inner join systypes t ");
    sb.Append("on s.xtype = t.xtype ");
    sb.Append("where id = (select id from" + 
              " sysobjects where name='" + strName + "')");
    //Use a SqlDataAdapter to fill a datatable, 
    //using the above command 
    SqlDataAdapter adapter = new SqlDataAdapter(sb.ToString(), cn);
    DataTable dt = new DataTable();
    try 
    {
        cn.Open();
        adapter.Fill(dt);
        //Bind the resulting table to the grid 
        this.dgEnum.DataSource=dt;
        this.dgEnum.DataBind();
    }
     catch (Exception exc)
    {
         //Send the exception to our exception label 
         this.lblException.Text = exc.ToString();
    }
    finally 
    {
        //Clean up the connection
        cn.Close();
    }
}

Points of Interest

There has been a lot of concern over the past few years about SQL injection attacks. As a web programmer, you leave yourself wide open to this when you utilize raw SQL and query strings. If you decide to use the above SQL or code, I'd recommend compiling the SQL into parameterized stored procedures, and executing them that way. I left them as raw SQL here for the purpose of illustration.

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

UsualDosage
Architect
United States United States
I have been an ASP.NET/C# Programmer/Software Architect for about 10 years, specializing in web architecture, user interface, and user experience. I formerly wrote business applications for mortgage banking front-ends in C++ before switching to the .NET Framework many years ago, and I've never looked back. I'm an evangelist of HTML5 and web standards, and spend the majority of my time working on front end design, performance and scale.

My primary website is located at http://www.usualdosage.com

Comments and Discussions

 
QuestionC# access to SQL Server Stored Procedure PinmemberLeifDalkarChr12-Jan-10 4:03 
GeneralThanks PinmemberAhmed R El Bohoty17-Nov-08 7:42 
GeneralFix for NVARCHAR PinmemberTallBear11-Dec-07 6:50 
GeneralLong Line PinmemberJay Giganti25-Jan-07 11:42 
GeneralRe: Long Line PinmemberUsualDosage26-Jan-07 4:07 
GeneralRe: Long Line PinmemberJay Giganti26-Jan-07 5:28 
GeneralShould use standard SQL sp for this PinmemberJanvdK10-Jan-06 8:33 
GeneralRe: Should use standard SQL sp for this PinmemberHyperX2-Feb-06 5:17 
GeneralRe: Should use standard SQL sp for this [modified] PinmemberLeblanc Meneses19-Jul-06 22:03 
NewsBad Idea PinmemberGrimolfr4-Jan-06 3:49 

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
Web03 | 2.8.140814.1 | Last Updated 3 Jan 2006
Article Copyright 2006 by UsualDosage
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid