Dhwani Dhwani - 8 months ago 60
C# Question

SQL Server Recognise SP_EXECUTESQL as object rather than Procedure Name

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.

Answer Source

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