SQL Server将SP_EXECUTESQL识别为对象而不是过程名称 [英] SQL Server Recognise SP_EXECUTESQL as object rather than Procedure Name

问题描述

我正在使用 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 列中，它将对象作为对象而不是过程名称（GetCaseList）显示为sp_executesql。 我只能从c＃代码进行更改。

解决方案

问题是大部分的EF执行数据库调用使用 DbCommand 与 CommadType 文本，所以虽然SqlServer识别SP调用，它通过 sp_executesql 执行它们作为文本。

为了获得所需的行为，命令应该是以这种方式设置：

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

不幸的是，EF不提供指定命令类型的标准方式。我建议的解决方案是基于：

• 自定义SP调用SQL语法使用 CallPrefix StoredProcedureName 为了不干扰常规电话


• EF命令拦截删除前缀并在执行命令之前更改命令类型。

这里是执行：

 使用System.Data; 
使用System.Data.Common; 
使用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进程（DbCommand命令）
 {
 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命令，DbCommandInterceptionContext< DbDataReader> interceptionContext）
 {
进程（命令）; 
 base.ReaderExecuting（command，interceptionContext）; 
} 
} 
} 
  

所有你需要的是添加上面的类到你的项目，调用 Sp.CallInterceptor.Install（）一次，例如在你的 DbContext 静态构造函数：

  public class YourDbContext：DbContext 
 {
 static YourDbContext（）
 {
 Sp.CallInterceptor.Install（）; 
} 
 // ... 
} 
  

然后更改您的SP呼叫（使用您的样本）：

from：

  return DataContext.Database.SqlQuery< CaseList>（EXEC GetCaseList @CaseStage，
 new SqlParameter（@ CaseStage，paramList.CaseStageID））ToList（）; 
  

to：

 code> return DataContext.Database.SqlQuery< CaseList>（Sp.Call（GetCaseList），
 new SqlParameter（@ CaseStage，paramList.CaseStageID））ToList（）; 
  

这将生成（对于 paramList.CaseStageID == 9 ）：

  EXEC GetCaseList @CaseStage = 9 
  

I am using DBContext.Database.SqlQuery<entity> to execute stored procedure from my C# code repository.

It works fine but I want to know that why it is executing procedure like below:

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

rather than

EXEC GetCaseList @CaseStage = 9

And is there any way that my all procedures execute from c# like this
EXEC GetCaseList @CaseStage = 9 rather than exec sp_executesql N'EXEC GetCaseList @CaseStage',N'@CaseStage int',@CaseStage=9?

How can I make SQL Server Profiler to treat procedure name as object rather than SP_EXECUTESQL ?

Note: I want to execute procedure from c# as EXEC GetCaseList @CaseStage = 9 because I am saving trace data through SQL Server Profiler in table format. And in ObjectName column, it is showing sp_executesql as object rather than procedure name(GetCaseList) as object. I can make changes only from c# code.

解决方案

The problem is that most of the EF performed database calls use DbCommand with CommadType Text, so although SqlServer recognizes SP calls, it executes them as text via sp_executesql.

To get the desired behavior, the command should be setup this way:

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

Unfortunately EF does not provide a standard way of specifying the command type. The solution I'm suggesting is based on:

• Custom SP call SQL syntax using CallPrefix StoredProcedureName in order to not interfere with regular calls
• EF command interception to remove the prefix and change the command type before executing the command.

Here is the implementation:

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

All you need is to add the above class to your project, call Sp.CallInterceptor.Install() once, for instance inside your DbContext static constructor:

public class YourDbContext : DbContext
{
    static YourDbContext()
    {
        Sp.CallInterceptor.Install();
    }
    // ...
}

and then change your SP calls like this (using your sample):

from:

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

to:

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

which will generate (for paramList.CaseStageID == 9):

EXEC GetCaseList @CaseStage = 9

这篇关于SQL Server将SP_EXECUTESQL识别为对象而不是过程名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！