Getting Stored Procedure Parameter Information at Runtime

[ Team LiB ] 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.
Nội dung trích xuất từ tài liệu:
Getting Stored Procedure Parameter Information at Runtime [ Team LiB ]Recipe 4.9 Getting Stored Procedure Parameter Information at RuntimeProblemYou want to get information about the parameters used by a stored procedure at runtime.SolutionUse 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. Ineither case, the results are stored to a DataTable and its default view is bound to a datagrid on the form.The C# code is shown in Example 4-12.Example 4-12. File: SpParameterForm.cs// Namespaces, variables, and constantsusing 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;DiscussionThis solution demonstrates two techniques to retrieve information about parameters for astored procedure.DeriveParameters( ) methodThe first technique uses the static DeriveParameters( ) method of the CommandBuilderobject to populate the Parameters collection of the Command object with the parameterinformation for the stored procedure specified by the Command. Any existinginformation in the Parameters collection is overwritten.The example demonstrates creating a stored procedure Command object. The name of thestored procedure and the Connection object are both specified in the Commandconstructor. The Connection is opened and the DeriveParameters( ) method is called toretrieve the information about the parameters for the stored procedure into a Parameterscollection. 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_columnsThe second technique is specific to Microsoft SQL Server. The system stored proceduresp_sproc_columns returns parameter information for one or more stored procedures.Unlike the DeriveParameters( ) method, you cannot use it to automatically populate aParameters collection with parameter information. It does, however, return moreinformation than the DeriveParameters( ) method, and you can use it to return results formore than one stored procedure at a time. It also supports filtering options and does notrequire a stored procedure Command object. Executing this procedure returns a result setin which the rows correspond to stored procedure columns. For more information aboutthe parameter information returned, see SQL Server Books Online.The example demonstrates retrieving information about parameters for only a singlestored procedure by specifying the name of the stored procedure in the@procedure_name parameter. See SQL Server Books Online for other parameters thatyou can use to filter the information returned.[ Team LiB ]