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<int> interceptionContext) { CreateAuditMessage(command, interceptionContext); } public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { CreateAuditMessage(command, interceptionContext); } public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { CreateAuditMessage(command, interceptionContext); } public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { CreateAuditMessage(command, interceptionContext); } public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { CreateAuditMessage(command, interceptionContext); } public static void CreateAuditMessage<T>(DbCommand command, DbCommandInterceptionContext<T> 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