Recipe 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,
            param.Direction.ToString( ),
            param.SqlDbType.ToString( )});

    dataGrid.CaptionText = "Stored procedure '" + procedureName +
        "' parameters using CommandBuilder.DeriveParameters";
}
else if(spRadioButton.Checked)
{
    // Build a command object to use SQL Server stored procedure
    // to retrieve parameters.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    SqlCommand cmd = new SqlCommand("sp_sproc_columns", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter param = cmd.Parameters.Add("@procedure_name",
        SqlDbType.NVarChar, 390);
    param.Value = procedureName;

    // Fill the results table.
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);

    dataGrid.CaptionText = "Stored procedure '" + procedureName +
        "' parameters using sp_proc_columns.";
}

// Bind the default view of the results table to the grid.
dataGrid.DataSource = dt.DefaultView;

Discussion

This solution demonstrates two techniques to retrieve information about parameters for a stored procedure.

DeriveParameters( ) method

The first technique uses the static DeriveParameters( ) method of the CommandBuilder object to populate the Parameters collection of the Command object with the parameter information for the stored procedure specified by the Command. Any existing information in the Parameters collection is overwritten.

The example demonstrates creating a stored procedure Command object. The name of the stored procedure and the Connection object are both specified in the Command constructor. The Connection is opened and the DeriveParameters( ) method is called to retrieve the information about the parameters for the stored procedure into a Parameters collection. The collection is iterated over to extract information about the parameters, which is subsequently displayed.

If the stored procedure specified does not exist, an InvalidOperationException is raised.

The DeriveParameters( ) method incurs a performance penalty because it requires an extra round trip between the application and the data server to retrieve parameter metadata. It is more efficient to populate the parameters collection explicitly if the parameter information is known. As a result, the DeriveParameters( ) method is not recommended for use in production environments. The method exists primarily for design-time or ad-hoc use.

Microsoft SQL Server System stored procedure: sp_sproc_columns

The second technique is specific to Microsoft SQL Server. The system stored procedure sp_sproc_columns returns parameter information for one or more stored procedures. Unlike the DeriveParameters( ) method, you cannot use it to automatically populate a Parameters collection with parameter information. It does, however, return more information than the DeriveParameters( ) method, and you can use it to return results for more than one stored procedure at a time. It also supports filtering options and does not require a stored procedure Command object. Executing this procedure returns a result set in which the rows correspond to stored procedure columns. For more information about the parameter information returned, see SQL Server Books Online.

The example demonstrates retrieving information about parameters for only a single stored procedure by specifying the name of the stored procedure in the @procedure_name parameter. See SQL Server Books Online for other parameters that you can use to filter the information returned.