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?

c-sharp sql sql-server entity-framework

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

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

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

What is SQL dba Server?

Computer developers and administrators are able to manipulate the data within the database with the use of SQL statements. These statements enable the programmer to perform certain tasks, such as updating data, retrieving data and filtering...

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++. C#. 차이점 알려드림. 5분 순삭. Explain C.C++.C#. Like I'm Five

C. C++. C#. 차이점 알려드림. 5분 순삭. Explain C.C++.C#. Like I'm Five. 아시죠? C는 C++.C# 줄인말이 아니라는거?

Learn how to perform CRUD operations using C Sharp

QuickStart: CRUD Operations in C Sharp. Learn how to perform CRUD operations using C Sharp for MongoDB databases.

Loops in C++ | For, While, and Do While Loops in C++

In this Video We are going to see how to use Loops in C++. We will see How to use For, While, and Do While Loops in C++.