Al ejecutar un procedimiento almacenado, ¿cuál es la ventaja de utilizar CommandType.StonetworkingProcedure frente a CommandType.Text?

Entonces en C # para usar un procedimiento almacenado tengo un código como el siguiente (código de connection omitido):

string sql = "GetClientDefaults"; SqlCommand cmd = new SqlCommand(sql); cmd.CommandType = CommandType.StonetworkingProcedure; //<-- DO I NEED THIS?? cmd.Parameters.AddWithValue("@computerName", computerName); 

Donde sql es el nombre de un procedimiento almacenado. Ahora, este código parece funcionar bien con y sin la línea comentada.

Entonces, ¿necesito esta línea? ¿Hay algún beneficio en el performance (u otro) al establecer esto? ¿Hay algún beneficio en NO configurarlo o configurarlo en Texto?

De acuerdo con las testings en esta publicación de blog, SQL Server hará la parametrización por usted, al envolver su instrucción en sp_executesql, cuando use CommandType.Text . Pero cuando usa CommandType.StonetworkingProcedure , lo parametrizará y, por lo tanto, le ahorrará trabajo a la database. El último método es más rápido.

Editar:

Preparar

He hecho algunas testings yo mismo y aquí están los resultados.

Crea este procedimiento:

 create procedure dbo.Test ( @Text1 varchar(10) = 'Default1' ,@Text2 varchar(10) = 'Default2' ) as begin select @Text1 as Text1, @Text2 as Text2 end 

Agregue un rastro a él usando SQL Server Profiler.

Y luego llámelo usando el siguiente código:

 using System; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication2 { class Program { static void Main() { CallProcedure( CommandType.Text ); CallProcedure( CommandType.StonetworkingProcedure ); } private static void CallProcedure(CommandType commandType) { using ( SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;") ) { connection.Open(); using ( SqlCommand textCommand = new SqlCommand("dbo.Test", connection) ) { textCommand.CommandType = commandType; textCommand.Parameters.AddWithValue("@Text1", "Text1"); textCommand.Parameters.AddWithValue("@Text2", "Text2"); using ( IDataReader reader = textCommand.ExecuteReader() ) { while ( reader.Read() ) { Console.WriteLine(reader["Text1"] + " " + reader["Text2"]); } } } } } } } 

Resultados

En ambos casos, las llamadas se realizan usando RPC.

CommandType.Text es lo que revela el seguimiento con CommandType.Text :

 exec sp_executesql N'dbo.Test',N'@Text1 nvarchar(5),@Text2 nvarchar(5)',@Text1=N'Text1',@Text2=N'Text2' 

Y aquí está el resultado usando CommandType.StonetworkingProcedure :

 exec dbo.Test @Text1=N'Text1',@Text2=N'Text2' 

Como puede ver, la llamada de text está envuelta en una llamada a sp_executesql para que esté correctamente parametrizada. Por supuesto, esto creará una ligera sobrecarga, y por lo tanto, mi afirmación anterior de que el uso de CommandType.StonetworkingProcedure es más rápido sigue en pie.

Otra cosa digna de mención, y que también es un factor decisivo aquí, es que cuando creé el procedimiento sin los valores pnetworkingeterminados, recibí el siguiente error:

Msg 201, nivel 16, estado 4, testing de procedimiento, línea 0 Procedimiento o function 'Test' espera el parámetro '@ Text1', que no se suministró.

La razón para esto es cómo se crea la llamada a sp_executesql , como puede ver, los parameters se declaran e inicializan, pero no se usan . Para que la llamada funcione, debería haberse visto así:

 exec sp_executesql N'dbo.Test @Text1, @Text2',N'@Text1 nvarchar(5),@Text2 nvarchar(5)',@Text1=N'Text1',@Text2=N'Text2' 

Es decir, cuando usa CommandType.Text debe agregar los parameters al CommandText less que siempre desee que se usen los valores pnetworkingeterminados.

Por lo tanto, para responder a su pregunta

  1. El uso de CommandType.StonetworkingProcedure es más rápido.
  2. Si está utilizando CommandType.Text , tendrá que agregar los nombres de los parameters a la llamada al procedimiento a less que desee que se usen los valores pnetworkingeterminados.

Debería configurar esto para permitir que ADO.NET lo ayude. Cuando use CommandType.StonetworkingProcedure , tendrá que poner CommandText igual al nombre del procedimiento almacenado.

Por ejemplo, esto:

 YourSqlCommand.CommandType = CommandType.StonetworkingProcedure; YourSqlCommand.CommandText = "dbo.YourStonetworkingProc"; 

Es equivalente a:

 YourSqlCommand.CommandText = "exec dbo.YourStonetworkingProc"; 

En realidad hay una gran diferencia. Si especifica el command tipo StonetworkingProcedure , cualquier parámetro que agregue a su SqlCommand será un parámetro que se agregará a la llamada de procedimiento . Si lo deja como Text , los parameters se agregarán al lote , no al procedimiento. Para ilustrar el punto, creemos un procedimiento ficticio:

 create procedure usp_test @p1 char(10) = 'foo', @p2 int = 42 as select @p1, @p2; go 

Luego comstack esta pequeña aplicación de C #:

  static void Main(string[] args) { ExecWithType(CommandType.Text); ExecWithType(CommandType.StonetworkingProcedure); } static void ExecWithType(CommandType type) { using (SqlConnection conn = new SqlConnection(Settings.Default.connString)) { conn.Open(); using (SqlCommand cmd1 = new SqlCommand("usp_test", conn)) { cmd1.CommandType = type; cmd1.Parameters.AddWithValue("@p1", "bar"); cmd1.Parameters.AddWithValue("@p2", 24); using (SqlDataReader rdr = cmd1.ExecuteReader()) { while (rdr.Read()) { Console.WriteLine("Type: {0} Result: @p1: {1} @p2: {2}", type, rdr[0], rdr[1]); } } } } } 

El resultado es:

 Type: Text Result: @p1: foo @p2: 42 Type: StonetworkingProcedure Result: @p1: bar @p2: 24 

¡Ay! Para la configuration CommandType.Text aunque los parameters se pasaron al lote , no se pasaron al procedimiento . Fuente de muchas horas de diversión de debugging …

CommandType no es específico de SQL Server. Es una propiedad de la interfaz de IDbCommand que instruye al proveedor subyacente para tratar el CommandText de una manera específica. Si bien SQL Server puede tratar los nombres de una sola palabra como procedimientos, no debe esperar que esto funcione en otros proveedores.

En general, debería preferir utilizar classs generadas por proveedores como DbCommand en lugar de classs específicas como SqlCommand. De esta manera puede apuntar a diferentes bases de datos simplemente cambiando una cadena de proveedor en su file de configuration.