本文介绍了SQL Server将SP_EXECUTESQL识别为对象而不是过程名称的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用 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执行数据库调用使用与 文本,所以虽然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识别为对象而不是过程名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-21 08:43