ADO.NET Cookbook

4.9. Getting Stored Procedure Parameter Information at Runtime

Problem

You want to get information about the parameters used by a stored procedure at runtime.

Solution

Use DeriveParameters( ) method of the CommandBuilder. With Microsoft SQL Server, you could also use system stored procedures.

The sample code demonstrates either one of these techniques, as specified by the user. In either case, the results are stored to a DataTable and its default view is bound to a data grid on the form.

The C# code is shown in Example 4-12.

Example 4-12. File: SpParameterForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; // . . . String procedureName = "Sales by Year"; // Create the table to hold the results. DataTable dt = new DataTable( ); if(commandBuilderRadioButton.Checked) { // Build a command object for the 'Sales by Year' stored procedure. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand(procedureName, conn); cmd.CommandType = CommandType.StoredProcedure; // Get the parameters. conn.Open( ); SqlCommandBuilder.DeriveParameters(cmd); conn.Close( ); // Define table columns to hold the results. dt.Columns.Add("Name"); dt.Columns.Add("Direction"); dt.Columns.Add("SqlType"); // Retrieve the results from the command object to the table. foreach (SqlParameter param in cmd.Parameters) dt.Rows.Add(new object[] {param.ParameterName, ...