Develop UNDO feature with Entity Framework 6 and trigger

Develop UNDO feature with Entity Framework 6 and trigger

EF 6.0 overview

Entity Framework 6.0 maintains connection and transaction internally when SaveChanges() method is called. It maintains a transaction for multiple entity insert, update and delete in a single SaveChanges() method. Each time calling this method EF creates a new connection and transaction.

In earlier version of EF, System.Transaction.TransactionScope was used to control the transaction. So passing information to triggers is as easy as adding one additional call to a stored procedure. However, in EF6 this approach was out.

New API with EF 6.0

EF 6.0 introduced two new APIs for transaction.

  1. DbContext.Database.BeginTransaction: It allows us to begin a transaction. It allows us to combine several operations to be combined within the same transaction and hence all the transactions are either committed or rolled back. This method allows us to specify the isolation level for the transaction. It returns a DbContextTransaction object. The BeginTransaction method has two overloads, one has no argument and the other accepts an explicit Isolation Level.
  2. DbContext.Database.UseTransaction: It allows DbContext to use a transaction that was stated outside of the Entity Framework. It means using this API we can use any existing transaction with Entity Framework.

Implementation

In application:
using(var tran = _context.Database.BeginTransaction())
{
try
{
await CallProcAsync(“dbo.USP_HIS_ContextInfo @empId={0}, @recordId={1}”, UserId, 0);
await _context.SaveChangesAsync();
tran.Commit();
}
catch
{
tran.Rollback();
}
}
In trigger:
ALTER trigger [dbo].[trg_update_TBL_E2DIALER_MSI_CellCodes] on [dbo].[TBL_E2DIALER_MSI_CellCodes]
after update as
begin
SET NOCOUNT ON
declare @empId varchar(50), @recordId int, @changeID int, @info binary(128)
select @info = context_info(),@empId=replace(convert(varchar(255),@info),0x0,)
if coalesce(@empId,)= set @empId = SYSTEM_USER
More detail for trigger portion is in “Develop undo feature with trigger“.

Advertisements

Leave a Reply