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:
private void loadStoredProcs()
{
ddlSPs.Items.Clear();
SqlConnection cn = new SqlConnection(
System.Configuration.ConfigurationSettings.
AppSettings["ConnString"]);
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)
{
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:
private void bindParameters( string strName)
{
SqlConnection cn = new SqlConnection(
System.Configuration.ConfigurationSettings.
AppSettings["ConnString"]);
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 + "')");
SqlDataAdapter adapter = new SqlDataAdapter(sb.ToString(), cn);
DataTable dt = new DataTable();
try
{
cn.Open();
adapter.Fill(dt);
this.dgEnum.DataSource=dt;
this.dgEnum.DataBind();
}
catch (Exception exc)
{
this.lblException.Text = exc.ToString();
}
finally
{
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.