How can I retrieve a list of parameters from a stored procedure in SQL Server

Question

Using C# and System.Data.SqlClient, is there a way to retrieve a list of parameters that belong to a stored procedure on a SQL Server before I actually execute it?

I have an a "multi-environment" scenario where there are multiple versions of the same database schema. Examples of environments might be "Development", "Staging", & "Production". "Development" is going to have one version of the stored procedure and "Staging" is going to have another.

All I want to do is validate that a parameter is going to be there before passing it a value and calling the stored procedure. Avoiding that SqlException rather than having to catch it is a plus for me.

Joshua

Answer 1

SqlCommandBuilder.DeriveParameters(command)

This statement does what I need it to.

Here is a full code sample for the way I solved this problem.

Public Sub GetLogEntriesForApplication(ByVal settings As FilterSettings, Optional ByVal RowGovernor As Integer = -1)

    Dim command As New SqlCommand("GetApplicationActions", New SqlConnection(m_environment.LoggingDatabaseConnectionString))
    Dim adapter As New SqlDataAdapter(command)

    Using command.Connection

        With command

            .Connection.Open()
            .CommandType = CommandType.StoredProcedure

            SqlCommandBuilder.DeriveParameters(command)

            With .Parameters

                If settings.FilterOnLoggingLevel Then
                    If .Contains("@loggingLevel") Then
                        .Item("@loggingLevel").Value = settings.LoggingLevel
                    End If
                End If

                If settings.FilterOnApplicationID Then
                    If .Contains("@applicationID") Then
                        .Item("@applicationID").Value = settings.ApplicationID
                    End If
                End If

                If settings.FilterOnCreatedDate Then
                    If .Contains("@startDate") Then
                        .Item("@startDate").Value = settings.CreatedDate.Ticks
                    End If
                End If

                If settings.FilterOnEndDate Then
                    If .Contains("@endDate") Then
                        .Item("@endDate").Value = settings.EndDate.Ticks
                    End If
                End If

                If settings.FilterOnSuccess Then
                    If .Contains("@success") Then
                        .Item("@success").Value = settings.Success
                    End If
                End If

                If settings.FilterOnProcess Then
                    If settings.Process > -1 Then
                        If .Contains("@process") Then
                            .Item("@process").Value = settings.Process
                        End If
                    End If
                End If

                If RowGovernor > -1 Then
                    If .Contains("@topRows") Then
                        .Item("@topRows").Value = RowGovernor
                    End If
                End If

            End With

        End With

        adapter.TableMappings.Clear()
        adapter.TableMappings.Add("Table", "ApplicationActions")
        adapter.TableMappings.Add("Table1", "Milestones")

        LogEntries.Clear()
        Milestones.Clear()
        adapter.Fill(m_logEntryData)

    End Using

End Sub

Answer 2

You can use SqlCommandBuilder.DeriveParameters() (see here) or there's this way which isn't as elegant.

Answer 3

You can use the SqlCommandBuilder object, and call the DeriveParameters method.

Basically you need to pass it a command, that is setup to call your stored proc, and it will hit the DB to discover the parameters, and create the appropriate parameters in the Parameters property of the SqlCommand

EDIT: You're all too fast!!

Answer 4

You want the SqlCommandBuilder.DeriveParameters(SqlCommand) method. Note that it requires an additional round trip to the database, so it is a somewhat significant performance hit. You should consider caching the results.

An example call:

using (SqlConnection conn = new SqlConnection(CONNSTRING))
using (SqlCommand cmd = new SqlCommand("StoredProc", conn)) {
   cmd.CommandType = CommandType.StoredProcedure;
   SqlCommandBuilder.DeriveParameters(cmd);

   cmd.Parameters["param1"].Value = "12345";

   // ....
}

Answer 5

Although its not exactly what you want, here's some sample code that uses the SqlConnection.GetSchema() method to return all the stored procedures associated with a database, and then subsequently all the parameter names and types for each stored procedure. The example below just loads this into variables. Note that this also returns all the "system" stored procedures, which might not be desirable.

Steve

 public void LoadProcedureInfo()
 {
  SqlConnection connection = new SqlConnection();

  ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ConnectionString"];

  connection.ConnectionString = settings.ConnectionString;
  connection.Open();

  DataTable procedureDataTable = connection.GetSchema("Procedures");
  DataColumn procedureDataColumn = procedureDataTable.Columns["ROUTINE_NAME"];

  if (procedureDataColumn != null)
  {
   foreach (DataRow row in procedureDataTable.Rows)
   {
    String procedureName = row[procedureDataColumn].ToString();

    DataTable parmsDataTable = connection.GetSchema("ProcedureParameters", new string[] { null, null, procedureName });

    DataColumn parmNameDataColumn = parmsDataTable.Columns["PARAMETER_NAME"];
    DataColumn parmTypeDataColumn = parmsDataTable.Columns["DATA_TYPE"];

    foreach (DataRow parmRow in parmsDataTable.Rows)
    {
     string parmName = parmRow[parmNameDataColumn].ToString();
     string parmType = parmRow[parmTypeDataColumn].ToString();
    }
   }
  }
 }

Answer 6

Mark has the best implementation of DeriveParameters. As he said, make sure you cache like in this tutorial.

However, I think this is a dangerous way of solving your original problem of database sproc versioning. If you are going to change the signature of a procedure by adding or removing parameters, you should do one of the following:

• Code in a backwards-compatible manner by using defaults (for new params) or by simply ignoring a param (for deleted params). This ensures that your client code can always call any version of your stored procedure.
• Explicitly version the procedure by name (so you will have my_proc and my_proc_v2). This ensures that your client code and sprocs stay in sync.

Relying on DeriveParameters to validate what version of the sproc you're using seems like the wrong tool for the job, IMHO.

Answer 7

All of these ADO.NET solutions are are asking the code library to query the database's metadata on your behalf. If you are going to take that performance hit anyhow, maybe you should just write some helper functions that call

Select count(*) from information_schema.parameters 
where ...(proc name =.. param name=...) (pseudo-code)

Or maybe even generate your parameters based on the param list you get back. This technique will work with multiple versions of MS SQL and sometimes other ANSI SQL databases.

Answer 8

Hi. I have been using DeriveParameters with .NET 1.1 and 2.0 since a couple of years now, and worked like a charm every time.

Now I'm working on my first assignment with .NET 3.5, and just found and ugly surprise: DeriveParameters is creating all parameters with SqlDbType "Variant", instead proper SqlDbTypes. This is creating a SqlException when trying to execute SPs with numeric parameters, because SQL Server 2005 says that sql-variant types cant be implictily converted to int (or smallint, or numeric) values.

I just tested the same code with .NET CF 2.0 and SQL Server 2000, and worked as expected, assigning the correct SqlDbType to each parameters.

I had tested .NET 2.0 apps against SQL Server 2005 Databases, so is not a SQL Server related issue, so it has to be something related with .NET 3.5

Any ideas?