c# - stored - ¿Cómo puedo recuperar una lista de parámetros de un procedimiento almacenado en SQL Server?

recuperar parametro de salida c#

Question

Usando C # y System.Data.SqlClient, ¿hay alguna forma de recuperar una lista de parámetros que pertenecen a un procedimiento almacenado en un SQL Server antes de que realmente lo ejecute?

Tengo un escenario de "multi-entorno" donde hay múltiples versiones del mismo esquema de base de datos. Ejemplos de entornos pueden ser "Desarrollo", "Puesta en escena" y "Producción". "Desarrollo" va a tener una versión del procedimiento almacenado y "Etapa" va a tener otra.

Todo lo que quiero hacer es validar que un parámetro va a estar allí antes de pasarle un valor y llamar al procedimiento almacenado. Evitar esa SqlException en lugar de tener que atraparla es una ventaja para mí.

Joshua

Answer 1

Aunque no es exactamente lo que quiere, aquí hay un ejemplo de código que usa el método SqlConnection.GetSchema () para devolver todos los procedimientos almacenados asociados con una base de datos y luego todos los nombres y tipos de parámetros para cada procedimiento almacenado. El siguiente ejemplo solo carga esto en variables. Tenga en cuenta que esto también devuelve todos los procedimientos almacenados del "sistema", lo que podría no ser deseable.

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 2

Desea el método SqlCommandBuilder.DeriveParameters (SqlCommand) . Tenga en cuenta que requiere un viaje de ida y vuelta adicional a la base de datos, por lo que es un golpe de rendimiento bastante significativo. Debería considerar almacenar en caché los resultados.

Una llamada de ejemplo:

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 3

Mark tiene la mejor implementación de DeriveParameters. Como dijo, asegúrese de guardar en caché como en este tutorial .

Sin embargo, creo que esta es una forma peligrosa de resolver su problema original de control de versiones de bases de datos. Si va a cambiar la firma de un procedimiento agregando o eliminando parámetros, debe hacer una de las siguientes cosas:

• Código de una manera compatible con versiones anteriores mediante el uso de valores predeterminados (para nuevos parámetros) o simplemente ignorando un parámetro (para parámetros eliminados). Esto garantiza que su código de cliente siempre pueda llamar a cualquier versión de su procedimiento almacenado.
• Versión explícitamente el procedimiento por nombre (para que tenga my_proc y my_proc_v2). Esto asegura que su código de cliente y sprocs permanecen sincronizados.

Confiando en DeriveParameters para validar qué versión del sproc que está utilizando parece ser la herramienta incorrecta para el trabajo, en mi humilde opinión.

Answer 4

Puede usar el objeto SqlCommandBuilder y llamar al método DeriveParameters.

Básicamente, necesita pasarle un comando, que está configurado para llamar a su proceso almacenado, y golpeará el DB para descubrir los parámetros, y creará los parámetros apropiados en la propiedad Parámetros del SqlCommand.

EDITAR: ¡Eres demasiado rápido!

Answer 5

Todas estas soluciones de ADO.NET están solicitando a la biblioteca de códigos que consulte los metadatos de la base de datos en su nombre. Si va a tomar ese rendimiento de todos modos, tal vez debería escribir algunas funciones auxiliares que llamen

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

O tal vez incluso genere sus parámetros en función de la lista de parámetros que obtiene. Esta técnica funcionará con múltiples versiones de MS SQL y otras bases de datos ANSI SQL.

Answer 6

He usado DeriveParameters con .NET 1.1 y 2.0 desde hace un par de años, y he trabajado como un hechizo todo el tiempo.

Ahora estoy trabajando en mi primera tarea con .NET 3.5, y acabo de encontrar una sorpresa desagradable: DeriveParameters está creando todos los parámetros con SqlDbType "Variant", en lugar de SqlDbTypes. Esto crea una SqlException al intentar ejecutar SP con parámetros numéricos, porque SQL Server 2005 dice que los tipos sql-variant no se pueden convertir implícitamente a valores int (o smallint, o numéricos).

Acabo de probar el mismo código con .NET CF 2.0 y SQL Server 2000, y funcionó como se esperaba, asignando el SqlDbType correcto a cada parámetro.

Probé aplicaciones .NET 2.0 contra bases de datos SQL Server 2005, por lo que no es un problema relacionado con SQL Server, por lo que tiene que ser algo relacionado con .NET 3.5

¿Algunas ideas?

Answer 7

SqlCommandBuilder.DeriveParameters(command)

Esta declaración hace lo que necesito.

Aquí hay una muestra de código completo de la forma en que resolví este problema.

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 8

Puede usar SqlCommandBuilder.DeriveParameters () (consulte SqlCommandBuilder.DeriveParameters - Obtener información de parámetros para un procedimiento almacenado - Tutoriales de ADO.NET ) o de esta manera no es tan elegante.