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

• Download source files - 13.2 Kb

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.