Dhwani Dhwani - 15 days ago 9
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

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