How to use IDbCommandInterceptor to modify SQL when CommandType is StoredProcedure

Question

I'm using Entity Framework 6 and would like to set CONTEXT_INFO before any modifications to data, so my audit triggers can log which user made modifications. It seems this should be possible using IDbCommandInterceptor to change the CommandText that will be executed, something like the code below. But if the DbCommand.CommandType == CommandType.StoredProcedure then it doesn't like this, because it expects the CommandText will be just the procedure name, and throws an error if I prefix it with my extra SQL.

public class SetContextInterceptor : IDbCommandInterceptor
{

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        command.CommandText = GetSqlForSetContextInfo() + command.CommandText;
    }

     // ... other interface methods similar

    private string GetSqlForSetContextInfo()
    {
        var currentUsername = Thread.CurrentPrincipal != null && Thread.CurrentPrincipal.Identity != null
            ? Thread.CurrentPrincipal.Identity.Name
            : null;

        if (String.IsNullOrEmpty(currentUsername))
        {
            return "";
        }

        return "EXEC usp_setUserContext '" + currentUsername.Replace('\'', ' ') + "'; ";
    }

}

In particular the Parameters aren't in the CommandText in this case (i.e. CommandText contains just the stored procedure name), so I don't want to get into the messy business of doing string manipulation for the parameters. Is there an easy way of handling this case?

Here's some other articles on achieving similar things with CONTEXT_INFO. It seems like there's lots of variations on this theme but no agreed way of doing it, and many techniques were pre-EF6 so didn't have the IDbCommandInterceptor option.

UPDATE

I'm guessing because it's not possible to get the 'SQL' that's executed when CommandType = StoredProcedure that it's not possible to manipulate it. The option that occurs to me (for my scenario where I want to set CONTEXT_INFO) is to grab the command.Connection and execute a separate command to set CONTEXT_INFO. I'm not sure how well that'll work, e.g. if connection isn't already open then I have to open it myself.

From .NET can I get the full SQL string generated by a SqlCommand object (with SQL Parameters)?

In particular the Parameters aren't in the CommandText in this case I don't understand what you mean by that. It would help to see how you run the stored procedure. — Gert Arnold
– Gert Arnold, Commented Nov 13, 2016 at 22:12
command.CommandText looks like "usp_EmployeeUpdate" (i.e. it contains just the proc name). command.Parameters contains the parameter names and values. In my particular example the proc is called by Entity Framework, autogenerated code, but could be any other case where you use command.CommandType = CommandType.StoredProcedure. — Rory
– Rory, Commented Nov 13, 2016 at 22:23
e.g. return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("MyEntities.usp_EmployeeUpdate", idParameter, billingIdParameter, ... <other params> ... ); — Rory
– Rory, Commented Nov 13, 2016 at 22:24

Rory · Accepted Answer · 2017-08-16 13:03:20Z

0

As per my UPDATE above, I decided this isn't possible.

answered Aug 16, 2017 at 13:03

Rory

42.2k56 gold badges192 silver badges271 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use IDbCommandInterceptor to modify SQL when CommandType is StoredProcedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related