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

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

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.

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"; // .... } 

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(); } } } } 

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!

 SqlCommandBuilder.DeriveParameters(command) 

Esta statement 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 

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.

Todas estas soluciones ADO.NET están pidiendo 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.

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 está creando 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?