Lors de l'exécution d'une procédure stockée, quel est l'avantage d'utiliser CommandType.StoredProcedure par rapport à CommandType.Text?

Selon les tests dans ce blog SQL Server fera le paramétrage pour vous, en enveloppant votre déclaration dans sp_executesql, lorsque vous utilisez CommandType.Text. Mais lorsque vous utilisez CommandType.StoredProcedure vous permettra de paramétrer et ainsi sauvegarder la base de données de quelques travaux. La dernière méthode est plus rapide.

Edit:

Installation

J'ai fait quelques tests moi-même et voici les résultats.

Créer cette procédure:

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

Ajouter une trace à l'aide de SQL Server Profiler.

Et ensuite l'appeler à l'aide du code suivant:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main()
        {
            CallProcedure( CommandType.Text );
            CallProcedure( CommandType.StoredProcedure );
        }

        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"]);
                        }
                    }
                }
            }
        }
    }
}

Résultats

Dans les deux cas, les appels sont effectués à l'aide de RPC.

Voici ce que la trace révèle à l'aide de CommandType.Text:

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

Et voici le résultat à l'aide de CommandType.StoredProcedure:

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

Comme vous pouvez le voir le texte de l'appel est enveloppé dans un appel à sp_executesql de sorte qu'il est correctement paramétrée. Ce sera bien sûr de créer une légère surcharge, et donc ma déclaration précédente, que l'utilisation de CommandType.StoredProcedure est plus rapide, est encore debout.

Une autre chose à noter, et qui est aussi une sorte de deal breaker ici, c'est que lorsque j'ai créé la procédure sans valeurs par défaut j'ai l'erreur suivante:

Msg 201, Niveau 16, État 4, Procédure de Test, la Ligne 0 de la Procédure ou
la fonction "Test" s'attend à ce paramètre '@Text1', qui n'a pas été fourni.

La raison pour cela est la façon dont l'appel à sp_executesql est créé, vous pouvez voir les paramètres sont déclarées et initialisées, mais ils ne sont pas utilisés. Pour l'appel à travailler, il devrait ressembler à ceci:

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

Sens, lorsque vous utilisez CommandType.Text vous devez ajouter les paramètres à l' CommandText sauf si vous voulez toujours les valeurs par défaut seront utilisées.

Donc, pour répondre à votre question

1. À l'aide de CommandType.StoredProcedure est plus rapide.
2. Si vous utilisez CommandType.Textalors vous aurez à ajouter les noms de paramètre à l'appel de la procédure, sauf si vous voulez les valeurs par défaut seront utilisées.

Il est en fait une énorme différence. Si vous spécifiez le type de commande StoredProcedure puis n'importe quel paramètre que vous ajoutez à votre SqlCommand sera un paramètre ajouté le appel de procédure. Si vous le laissez tel Text ensuite les paramètres seront ajoutés à la lotpas à la procédure. Pour illustrer ce point, permet de créer un mannequin de procédure:

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

Ensuite compiler cette petite application en C#:

   static void Main(string[] args)
    {
        ExecWithType(CommandType.Text);
        ExecWithType(CommandType.StoredProcedure);
    }

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

Le résultat est:

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

Ouch! Pour le CommandType.Text cadre, même si les paramètres ont été transmis à la lotils n'ont pas été transmis à la procédure. Source de beaucoup d'heures de débogage de plaisir...