SQL Server将SP_EXECUTESQL识别为对象而不是过程名

本文关键字：对象 过程 识别 Server SP EXECUTESQL SQL | 更新日期: 2023-09-27 18:17:53

我使用DBContext.Database.SqlQuery<entity>从我的c#代码库执行存储过程。

它工作得很好，但我想知道为什么它像下面这样执行过程:

exec sp_executesql N'EXEC GetCaseList @CaseStage',N'@CaseStage int',@CaseStage=9

而不是

EXEC GetCaseList @CaseStage = 9

有没有办法让我的所有过程在c#中像这样执行
EXEC GetCaseList @CaseStage = 9而不是exec sp_executesql N'EXEC GetCaseList @CaseStage',N'@CaseStage int',@CaseStage=9 ?

如何使SQL Server Profiler将过程名作为对象而不是SP_EXECUTESQL ?

注意:我想从c#作为EXEC GetCaseList @CaseStage = 9执行过程，因为我通过SQL Server Profiler以表格式保存跟踪数据。在ObjectName列中，它显示sp_executesql作为对象而不是过程名称(GetCaseList)作为对象。

SQL Server将SP_EXECUTESQL识别为对象而不是过程名

问题是大多数EF执行的数据库调用使用DbCommand和CommadType Text，所以尽管SqlServer识别SP调用，它通过sp_executesql将它们作为文本执行。

要获得期望的行为，应该这样设置命令:

DbCommand command = ...;
command.CommandText = "StoredProcedureName";
command.CommandType = CommadType.StoredProcedure;

遗憾的是，EF没有提供指定命令类型的标准方法。我建议的解决方案是基于:

  • 使用CallPrefix StoredProcedureName自定义SP调用SQL语法，以便不干扰常规调用
  • EF命令拦截，在命令执行前去掉前缀，改变命令类型。

实现如下:

using System.Data;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
public static class Sp
{
    public const string CallPrefix = "CallSP ";
    public static string Call(string name) { return CallPrefix + name; }
    public class CallInterceptor : DbCommandInterceptor
    {
        public static void Install()
        {
            DbInterception.Remove(Instance);
            DbInterception.Add(Instance);
        }
        public static readonly CallInterceptor Instance = new CallInterceptor();
        private CallInterceptor() { }
        static void Process(DbCommand command)
        {
            if (command.CommandType == CommandType.Text && command.CommandText.StartsWith(Sp.CallPrefix))
            {
                command.CommandText = command.CommandText.Substring(Sp.CallPrefix.Length);
                command.CommandType = CommandType.StoredProcedure;
            }
        }
        public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            Process(command);
            base.ReaderExecuting(command, interceptionContext);
        }
    }
}
你所需要的就是把上面的类添加到你的项目中，调用一次Sp.CallInterceptor.Install()，例如在你的DbContext静态构造函数中: 
public class YourDbContext : DbContext
{
    static YourDbContext()
    {
        Sp.CallInterceptor.Install();
    }
    // ...
}

然后像这样改变你的SP调用(使用你的示例):

来自:

return DataContext.Database.SqlQuery<CaseList>("EXEC GetCaseList @CaseStage", 
    new SqlParameter("@CaseStage", paramList.CaseStageID)).ToList();

: 

return DataContext.Database.SqlQuery<CaseList>(Sp.Call("GetCaseList"), 
    new SqlParameter("@CaseStage", paramList.CaseStageID)).ToList();

将生成(对于paramList.CaseStageID == 9):

EXEC GetCaseList @CaseStage = 9

在实体框架/ADO.net中使用sp_executesql是有意的。可以观察到，在生成的sql中，有时EF在直接执行查询和有时使用sp_executesql之间显得非常重要。当有一个客户端参数化帮助重用一个参数化编译计划时，sp_executesql就会发挥作用。当没有指定参数时，SQL Server会尝试自动参数化，以帮助查询计划的重用。

似乎决定使用sp_executesql或直接sql批处理是由ADO控制的。Net的SQLCommand对象。根据表格数据流(TDS)，执行SQL查询似乎只有两种方式——使用RPC执行SQL存储过程，使用SQL批处理执行T-SQL。因此，当我们有一个参数化查询时，我们倾向于使用RPC并调用sp_executesql。关于查询执行模式的更多信息。

更多关于查询参数化的信息在这里

原因如下:

主要原因(1): TSQL字符串只构建一次，之后每次使用sp_executesql调用相同的查询时，SQL Server从缓存中检索查询计划并重用它。

(2): sp_executesql允许语句参数化，因此在SQL注入方面比EXEC更安全。

比较示例请查看下面的链接:EXEC和EXEC的比较sp_executesql

我认为这是因为EF需要动态生成SQL命令，所以需要使用exec sp_executesql动态执行SQL。

相关文章：