执行存储过程时，使用CommandType.StoredProcedure与使用CommandType.Text有什么好

本文关键字：CommandType Text 什么 StoredProcedure 存储过程 使用 执行 | 更新日期: 2023-09-27 18:24:16

因此，在C#中，为了使用存储过程，我有如下代码（省略了连接代码）：

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

其中sql是存储过程的名称。现在，无论有没有注释行，这段代码似乎都能很好地工作。

那么，我需要这条线路吗？设置此项是否对性能（或其他）有好处？不设置或设置为文本有好处吗？

执行存储过程时，使用CommandType.StoredProcedure与使用CommandType.Text有什么好

根据本博客文章中的测试，当您使用CommandType.Text时，SQL Server将通过在sp_executesql中包装您的语句来为您进行参数化。但是，当您使用CommandType.StoredProcedure时，您将参数化它，从而为数据库节省一些工作。后一种方法更快。

编辑：

设置

我自己做了一些测试，结果如下。

创建此过程：

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

使用SQL Server Profiler向其添加跟踪。

然后使用以下代码调用它：

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

结果

在这两种情况下，调用都是使用RPC进行的。

以下是使用CommandType.Text:跟踪显示的内容

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

这是使用CommandType.StoredProcedure:的结果

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

正如您所看到的，文本调用被封装在对sp_executesql的调用中，因此它被正确地参数化了。这当然会产生一点开销，因此我之前关于使用CommandType.StoredProcedure更快的说法仍然有效。

另一件值得注意的事情，也是这里的一个破坏交易的因素，是当我创建没有默认值的过程时，我得到了以下错误：

Msg 201，级别16，状态4，过程测试，第0行过程或函数"Test"需要参数"@Text1"，但未提供该参数。

原因是如何创建对sp_executesql的调用，正如您所看到的，参数被声明和初始化，但它们没有被使用。对于工作呼叫，它应该是这样的：

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

也就是说，当您使用CommandType.Text时，您必须将参数添加到CommandText中，除非您总是希望使用默认值。

因此，为了回答您的问题

  1. 使用CommandType.StoredProcedure更快
  2. 如果使用CommandType.Text，则必须将参数名称添加到对过程的调用中，除非希望使用默认值

实际上存在巨大差异。如果指定命令类型StoredProcedure，则添加到SqlCommand的任何参数都将是通过过程调用添加的参数。如果将其保留为Text，则参数将添加到批次中，而不是添加到过程中。为了说明这一点，让我们创建一个伪过程：

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

然后编译这个小小的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]);
                    }
                }
            }
        }
    }

结果是：

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

哎哟对于CommandType.Text设置，尽管参数被传递到批次，但它们没有被传递到过程。源于许多小时的调试乐趣。。。

您可以将其设置为允许ADO.NET帮助您。当您使用CommandType.StoredProcedure时，您只需要将CommandText设置为等于存储过程名称。

例如：

YourSqlCommand.CommandType = CommandType.StoredProcedure;
YourSqlCommand.CommandText = "dbo.YourStoredProc";

相当于：

YourSqlCommand.CommandText = "exec dbo.YourStoredProc";

CommandType不是SQL Server特有的。它是IDbCommand接口的一个属性，指示基础提供程序以特定方式处理CommandText。虽然SQL Server可能会将单个单词的名称视为过程，但您不应该期望在其他提供程序中也能这样做。

通常，您应该更喜欢使用提供程序生成的类，如DbCommand，而不是使用特定的类（如SqlCommand）。这样，只需更改配置文件中的提供程序字符串，就可以针对不同的数据库。

相关文章：