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;
This solution demonstrates two techniques to retrieve information about parameters for a stored procedure.
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.
Tip
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.
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.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.