ADO.NET 3.5 Cookbook, 2nd Edition

5.13. Getting Stored Procedure Parameter Information at Runtime

Problem

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

Solution

Use the DeriveParameters() method of the CommandBuilder class. With Microsoft SQL Server, you can also use system stored procedures, catalog views, and information schema views.

The solution demonstrates each of these techniques. In each case, information about the stored procedure parameters is output to the console.

The C# code in Program.cs in the project GetStoredProcedureParameterInformation is shown in Example 5-24.

Example 5-24. File: Program.cs for GetStoredProcedureParameterInformation solution

using System; using System.Data; using System.Data.SqlClient; namespace GetStoredProcedureParameterInformation { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; SqlDataAdapter da; DataTable dt; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { // Get parameter information using CommandBuilder SqlCommand command = new SqlCommand("uspGetBillOfMaterials", connection); command.CommandType = CommandType.StoredProcedure; // Get the parameters. connection.Open( ); SqlCommandBuilder.DeriveParameters(command); connection.Close( ); // Output the results Console.WriteLine("---Using CommandBuilder---"); foreach (SqlParameter param in command.Parameters) Console.WriteLine("ParameterName = {0}\tDirection ...