Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C#

Implementing Audit Trail using Entity Framework - Part 2

Rate me:
Please Sign up or sign in to vote.
4.16/5 (15 votes)
1 Apr 2009CPOL3 min read 69.5K   1.4K   41   10
Implementing Audit Trail using Entity Framework's caching entries
Image 1

Click to enlarge image

Introduction 

In Part 1, I talked about creating audit trail objects using the object state entries. As I said earlier, in the second part I will talk about roll back feature of this audit trail. In the first part, I already described that to get the Rollback feature from that audit trail, we have to consider some issues. We have to maintain the order of entity graph while inserting and deleting. That means the root entity has been inserted before the children and during deletion, we have to reverse it. The most important issue is that we have to make sure that each audit trail entry has been inserted according to this order. So during insertion of object, we also need to consider these things: 

  1. Maintain insertion date-time to sort the audit trial
  2. Store the old data and new data
  3. Store the state of the data as audit action

If user chooses a certain time to roll back, we have to start with last audits and do the roll back action for each audit till a certain time. So now the question is what the roll back action will be. You can guess that it’s depending on the Audit action of each entity. During rollback:

  1. Inserted data will be deleted.
  2. Deleted data will be inserted.
  3. Modified data will be replaced by the old one.

If we see the conceptual model, our Entity set for Audit was:

Image 3

Here “RevisionStamp” holds the audit insertion date-time, Actions hold the audit action of the entity object (Insert/deleted/modified). Other properties describe themselves with their names.

So here “Deleted” and “Modified” objects will be rolled back with “Old data” property. And Inserted data will be rolled back with “New data” property.   

Using the Code

UserDateInput.JPG

So first, I am going to retrieve all the audits that happen after a specific date-time. This specific date-time is taken from the user. What we have to do is, rollback action for each audit till a specific date and we have to start with the last one (bottom to top). That is why we are going to sort the audit with “RevisionStamp” in a descending order and start doing rollback.

C#
public static bool RollBack(DateTime rollBackDate, string userName)
{
    using (TransactionScope transactionScope = 
	new TransactionScope(System.Transactions.TransactionScopeOption.Required, 
	TimeSpan.FromMinutes(30)))
    {
        AdventureWorksEntities context =new AdventureWorksEntities();
        try
        {
            if (context.Connection.State != ConnectionState.Open)
            {//Open the connection
                context.Connection.Open();
            }//end if 
            //find all audits to roll back
            IEnumerable<DBAudit> auditsToRollBack = DataAdapter.GetEntity
			<DBAudit>(context, a => a.RevisionStamp >= rollBackDate );
             if (null != auditsToRollBack && auditsToRollBack.Count() > 0)
            {//if any data found to roll back
                //Order by the audits by creation datetime
                IEnumerable<DBAudit> orderedAudits = 
		auditsToRollBack.OrderByDescending(a => a.RevisionStamp);
                 foreach (var audit in orderedAudits)
                {//iterate each audit 
                    AuditTrailHelper.DoRollBackAction(ref context, audit, userName);
                }//end foreach
                int i =context.SaveChanges();
                if (i > 0)
                {
                    transactionScope.Complete();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            // Explicitly dispose of the context, 
            // which closes the connection. 
            context.Dispose();
        }
    }
    return false;
}

In DoRollBackAction as I said before, “Deleted” and “Modified” objects will be rolled back with “Old data” property. And Inserted data will be rolled back with “New data” property.

As I stored old data and new data with XML Serialization while creating the Audit object, I have to deserialise that data into “EntityObject” and do the reverse action according to the audit action.

C#
private static void DoRollBackAction
   (ref AdventureWorksEntities context,  DBAudit auditInfo, string userName)
       {
           if (auditInfo.Actions == AuditTrailHelper.AuditActions.U.ToString() ||
       auditInfo.Actions == AuditTrailHelper.AuditActions.D.ToString())
           {//For action of Update and Delete
               //Deserialized old data from the XML
               object oldData = AuditTrailHelper.GetAuditObjectFromXML
               (auditInfo.OldData, auditInfo.TableName);

               if (oldData is EntityObject)
               {
                   EntityObject oldEntity = (EntityObject)oldData;
                   oldEntity.EntityKey = null;
                   //add in case of delete or edit the current one with old data
                   DataAdapter.EditEntity(ref context, oldEntity);
               }
           }
           else if (auditInfo.Actions == AuditTrailHelper.AuditActions.I.ToString())
           {//For Insert Action
               object newData = AuditTrailHelper.GetAuditObjectFromXML
               (auditInfo.NewData, auditInfo.TableName);

               if (newData is EntityObject)
               {
                   //Otherwise, delete the entity that has been inserted before
                   EntityObject newEntity = (EntityObject)newData;
                   newEntity.EntityKey = null;
                   EntityKey key = context.CreateEntityKey
               (newEntity.GetType().Name, newEntity);

                   object objToRemoved = null;
                   if (context.TryGetObjectByKey(key, out objToRemoved))
                   {//delete the entity
                       context.DeleteObject(objToRemoved);
                   }//end if
               }
           }
           //delete the audit
           context.DeleteObject(auditInfo);
       }
   }

After serializing the entity Object, we get the XML string like this:

XML
<SalesOrderDetail xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance 
			xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <EntityKey>
    <EntitySetName>SalesOrderDetail</EntitySetName>
    <EntityContainerName>AdventureWorksEntities</EntityContainerName>
  </EntityKey>
  <SalesOrderID>1</SalesOrderID>
  <SalesOrderDetailID>0</SalesOrderDetailID>
  <OrderQty>1</OrderQty>
  <UnitPrice>1898.0944</UnitPrice>
  <UnitPriceDiscount>0</UnitPriceDiscount>
  <LineTotal>0</LineTotal>
  <rowguid>6bfaa372-292a-4fd6-84d3-c4e900f09589</rowguid>
  <ModifiedDate>2009-03-26T16:16:41.9691358+06:00</ModifiedDate>
  <SalesOrderHeaderReference />
  <SpecialOfferProductReference>
    <EntityKey>
      <EntitySetName>SpecialOfferProduct</EntitySetName>
      <EntityContainerName>AdventureWorksEntities</EntityContainerName>
      <EntityKeyValues>
        <EntityKeyMember>
          <Key>SpecialOfferID</Key>
          <Value xsi:type="xsd:int">1</Value>
        </EntityKeyMember>
        <EntityKeyMember>
          <Key>ProductID</Key>
          <Value xsi:type="xsd:int">777</Value>
        </EntityKeyMember>
      </EntityKeyValues>
    </EntityKey>
  </SpecialOfferProductReference>
</SalesOrderDetail> 

I have to deserialize this XML string. During deserializing, I have created an XML document and an XML reader using that document. Using that reader, I deserialized the entity object. The method to deserialize the entityobject is:

C#
public static object GetAuditObjectFromXML(string ObjectInXML, string typeName)
{
    XDocument doc = XDocument.Parse(ObjectInXML);
    //Assuming doc is an XML document containing a 
    //serialized object and objType is a System.Type set to the type of the object.
    XmlReader reader = doc.CreateReader();
    //get the Type of entity object  
    Type entityType = Assembly.GetExecutingAssembly().GetType
			("ImplAuditTrailUsingEF." + typeName);
     XmlSerializer ser = new XmlSerializer(entityType);
    return ser.Deserialize(reader);
}

For inserting deleted object or to change back modified object, I wrote a single method to edit the object:

C#
EditEntity(ref AdventureWorksEntities context, EntityObject entity)

Is this method, I change the existence object and attach it to the container otherwise I have inserted the entityObject into the context. To save all of this change, we need an entry in Object state cache. 

C#
    public static void EditEntity
	(ref AdventureWorksEntities context, EntityObject entity)
    {
        // Define an ObjectStateEntry and EntityKey for the current object.
        EntityKey key;
        object originalItem;
        // Get the detached object's entity key.
        if (entity.EntityKey == null)
        {
            // Get the entity key of the updated object.
            key = context.CreateEntityKey(entity.GetType().Name, entity);
        }
        else
        {
            key = entity.EntityKey;
        }
        try
        {
            // Get the original item based on the entity key from the context
            // or from the database.
            if (context.TryGetObjectByKey(key, out originalItem))
            {//accept the changed property
                 // Call the ApplyPropertyChanges method to apply changes
                // from the updated item to the original version.
                context.ApplyPropertyChanges(
                    key.EntitySetName, entity);                    
            }
            else
            {//add the new entity
                context.AddObject(entity.GetType().Name, entity);
            }//end else
        }
        catch (System.Data.MissingPrimaryKeyException ex)
        {
            throw ex;
        }
        catch (System.Data.MappingException ex)
        {
            throw ex;
        }
        catch (System.Data.DataException ex)
        {
            throw ex;
        }
    }
}

That is all for implementing rollback mechanism in our audit trail. Now all we have to do is just call the “RollBack” method and give it a specific date to roll back.

C#
private void btnRollBack_Click(object sender, RoutedEventArgs e)
{
    if (AuditTrailHelper.RollBack(dtpRollBackDate.SelectedDate.Value, "Admin"))
    {
        MessageBox.Show("Successfully Roll Backed!!");
    }
}

That’s all for implementation of Audit trial with entity framework. Here I have deleted the audits which have been rolled back. Definitely each rollback operation is also a DB operation and I did an audit for each rollback operation.   

Is the sample project, I have used the free version of Xeed. You may need to download that Library, otherwise you can replace it with the WPF toolkit. It has just been used for getting a date-time from user to rollback. 

History

  • 1st April, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader PracticePRO Software Systems Inc
United States United States
In my childhood, my uncle has shown me how to see the cloud in a close look and I understand that one can draw some elements of the Earth in the sky-canvas if he/she wants to. After that the cloud becomes closer to me and It teaches me one thing that, a deeper-look to something will give you some clues to draw your imagination. You can able to see that one which you have build-up in your mind.

Years past, I have started my career as a software engineer and has been looking for passion in my coding and development which I should be to enjoy my profession and has started asking myself- 'am I doing any engineering here?!' Is my code becoming that thing which I have designed in my mind? So to find that answer I have tried that old solution here... I have decided to come closer to my code and start analyzing them. And it is really working for me and at least it gives me the confidence that I can build something that I really want to. I can draw my thinking there through my code and can build-up my vision that I have designed in my mind. It also helps me to think out of the box, solve each problems by making blocks and make me careful on each steps.

• Morshed's Technical Blog site: http://morshedanwar.wordpress.com/

• Morshed's Technical articles those are published in Codeproject site: http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=2992452

• Morshed's Linkedin profile: http://www.linkedin.com/in/morshedanwar

• Morshed's Facebook Profile : http://www.facebook.com/morshed.pulok

Beside all these I like to do - photography and music. Here is my Flickr photos : http://www.flickr.com/photos/morshed_anwar/

Comments and Discussions

 
QuestionSequence contains no elements Pin
Member 831715423-Apr-13 18:41
Member 831715423-Apr-13 18:41 
AnswerRe: Sequence contains no elements Pin
Morshed Anwar24-Apr-13 5:49
professionalMorshed Anwar24-Apr-13 5:49 
GeneralRe: Sequence contains no elements Pin
Member 831715424-Apr-13 21:19
Member 831715424-Apr-13 21:19 
GeneralRe: Sequence contains no elements Pin
Morshed Anwar25-Apr-13 0:17
professionalMorshed Anwar25-Apr-13 0:17 
GeneralRe: Sequence contains no elements Pin
Member 831715425-Apr-13 2:06
Member 831715425-Apr-13 2:06 
GeneralRe: Sequence contains no elements Pin
Morshed Anwar25-Apr-13 3:22
professionalMorshed Anwar25-Apr-13 3:22 
GeneralMy vote of 5 Pin
Tridip Bhattacharjee10-May-12 20:31
professionalTridip Bhattacharjee10-May-12 20:31 
QuestionRetrieve change data for a specific record. Pin
jabit22-Jun-11 8:30
jabit22-Jun-11 8:30 
GeneralGood work if you are doing audit from the front end Pin
Donsw3-May-09 11:32
Donsw3-May-09 11:32 
GeneralRe: Good work if you are doing audit from the front end Pin
Morshed Anwar3-May-09 23:56
professionalMorshed Anwar3-May-09 23:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.