Entity Framework 6, Command Interception & Stored Procedures

Entity Framework 6, Command Interception & Stored Procedures

I was asked to develop auditing for a system at my work. The system has already been completed. I think EF 6's Command Interception should work well for my purposes.

I was asked to develop auditing for a system at my work. The system has already been completed. I think EF 6's Command Interception should work well for my purposes.

However, there are situations like this where we would want to know who sent a request for leave, and we would like to be able to intercept this Insert query.

using (DataContext context = new DataContext())
    {
      var result = context.CreateLeavePrerequest(
        leaveRequest.LeaveType,
        leaveRequest.StartDate,
        leaveRequest.EndDate,
        leaveRequest.NumberOfDays,
        leaveRequest.EmployeeComment,
        leaveRequest.HasSupportingDocumentation,
        leaveRequest.ResourceTag,
        leaveRequest.RemainingBalance,
        leaveRequest.ApproverResourceTag,
        leaveRequest.CapturerResourceTag,
        leaveRequest.SupportingDocumentID,
        ref id
        );

then the stored procedure is:

CREATE PROCEDURE [dbo].[CreateLeavePrerequest]
(
  @LeaveType VARCHAR(50) ,
  @StartDate DATETIME ,
  @EndDate DATETIME ,
  @NumberOfDays DECIMAL(18, 5) ,
  @EmployeeComment VARCHAR(512) ,
  @SickNoteIndicator BIT ,
  @ResourceTag INT,
  @RemainingBalance DECIMAL,
  @ApproverResourceTag INT,
  @CapturerResourceTag INT,
  @SupportingDocumentID INT,
  @id INT = 0 OUT
)  
AS 
BEGIN
    INSERT  INTO [ESS PER LVE PreRequest]
            ( [Resource Tag] ,
              [Leave Type] ,
              [Start Date] ,
              [End Date] ,
              [No Of Days] ,
              [Employee Comments] ,
              [Sick Note Indicator],
              [Status],
              [Remaining Balance],
              [Approver Resource Tag],
              [Capturer Resource Tag],
              [SupportingDocumentID]
            )
            SELECT  @ResourceTag ,
                    @LeaveType ,
                    @StartDate ,
                    @EndDate ,
                    @NumberOfDays ,
                    @EmployeeComment ,
                    @SickNoteIndicator,
                    'Captured',
                    @RemainingBalance,
                    @ApproverResourceTag,
                    @CapturerResourceTag,
                    @SupportingDocumentID;
SELECT @id
END 

UPDATE:

CreateLeavePrerequest is implemented as follows:

public ISingleResult<CreateLeavePrerequestResult> CreateLeavePrerequest([global::System.Data.Linq.Mapping.ParameterAttribute(Name="LeaveType", DbType="VarChar(50)")] string leaveType, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="StartDate", DbType="DateTime")] System.Nullable<System.DateTime> startDate, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="EndDate", DbType="DateTime")] System.Nullable<System.DateTime> endDate, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="NumberOfDays", DbType="Decimal(18,5)")] System.Nullable<decimal> numberOfDays, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="EmployeeComment", DbType="VarChar(512)")] string employeeComment, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="SickNoteIndicator", DbType="Bit")] System.Nullable<bool> sickNoteIndicator, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="ResourceTag", DbType="Int")] System.Nullable<int> resourceTag, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="RemainingBalance", DbType="Decimal(18,0)")] System.Nullable<decimal> remainingBalance, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="ApproverResourceTag", DbType="Int")] System.Nullable<int> approverResourceTag, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="CapturerResourceTag", DbType="Int")] System.Nullable<int> capturerResourceTag, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="SupportingDocumentID", DbType="Int")] System.Nullable<int> supportingDocumentID, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] ref System.Nullable<int> id)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), leaveType, startDate, endDate, numberOfDays, employeeComment, sickNoteIndicator, resourceTag, remainingBalance, approverResourceTag, capturerResourceTag, supportingDocumentID, id);
        id = ((System.Nullable<int>)(result.GetParameterValue(11)));
        return ((ISingleResult<CreateLeavePrerequestResult>)(result.ReturnValue));
    }

UPDATE 2

DBCommandInterceptor registration in Global.asax:

 protected void Application_Start()
 {
     DbInterception.Add(new Auditor());
 }

DBCommandInterceptor implementation:

I implemented this quickly so that I could just see whether I could intercept anything, so it just writes to the Debug window. I have been able to intercept some Select queries, but that's not what we want to audit.

 public class Auditor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        CreateAuditMessage(command, interceptionContext);
    }

public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext&lt;int&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext&lt;DbDataReader&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext&lt;DbDataReader&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext&lt;object&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext&lt;object&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public static void CreateAuditMessage&lt;T&gt;(DbCommand command, DbCommandInterceptionContext&lt;T&gt; interceptionContext)
{
    string message;

    var parameters = new StringBuilder();
    foreach (DbParameter param in command.Parameters)
    {
        parameters.AppendLine(param.ParameterName + " " + param.DbType + " = " + param.Value);
    }

    if (interceptionContext.Exception == null)
    {
        message = (parameters.ToString() + "  " + command.CommandText);
    }
    else
    {
        message =  (parameters.ToString() + command.CommandText + "  " + interceptionContext.Exception);
    }

    Debug.WriteLine(message);
}

}

Recently, I've been reading a lot about Entity Framework, but I am not very knowledgeable. I have implemented the IDbCommandInterface and registered it etc. I am able to see some other queries being intercepted, but since the above situation is such that the stored procedure is being called "outside" I can't get hold of the parameters.

This is a simple example. Not all the stored procedures that are called in a similar way in the system are so simple.

What would be the best way to change the above situation so that we can apply the interception and thus the auditing?

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

C/C++ vs. Rust: A developer’s perspective

In this post, you'll see the difference between Rust and C/C++ in a developer’s perspective