Implementing Audit Trail using Entity Framework : Part -2
In its part 1, I have talked about creating audit trail objects using the object state entries. As I said before In Second Part I will talk about roll back feature of this audit trail. In first part, I have said 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 insertion and deletion. That means root entity has been inserted before the children and during deletion we have to make it reverse. The most important issue is that we have to make sure that each audit trail entry has been inserted according this order. Other issues
1. We have to maintain insertion date-time to sort the audit trial.
2. Store the old data and new data.
3. Store the state 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 the certain time. So now the question is arises that what will be roll back action. 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 replaced by the old one.
If we see to conceptual model, our Entity set for Audit was-
Here “RevisionStamp” holds the audit insertion date-time, Actions holds the audit action of the entity object (Insert/deleted/modified).Other properties describe themselves with their names.
So here “Deleted” and “Modified” object will be roll backed with “Old data” property. And Inserted data will be roll backed with “New data” property.
So First I am going to retrieve all the audits those happen after a specific date-time. This specific date-time is taken from the user. What we have to do, do rollback action for each audit till to specific date and we have to start with 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-
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 creational 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” object will be roll backed with “Old data” property. And Inserted data will be roll backed with “New data” property.
As I stored old data and new data with XML Serialization while creating Audit object, I have to desterilize those data into “EntityObject” and do the reverse action according to the audit action.
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 –
<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 deserialized this xml string.During deserializing, I have created a XML document and create an XML reader using that document.Using that reader I deserialized the entity object. The method to deserialize the entityobject is –
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(); Type entityType = Assembly.GetExecutingAssembly().GetType("ImplAuditTrailUsingEF." + typeName); XmlSerializer ser = new XmlSerializer(entityType); return ser.Deserialize(reader); }
For inserting deleted object or change back modifed object I wrote a single method to edit the object – “EditEntity(ref AdventureWorksEntities context, EntityObject entity)”.Is this method I change the existance object and attch to the container otherwise I have inserted the entityObject into the context.To save all of this change we must need a entry in Object state cache.
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; } }
Now all we have to do is just call the “RollBack” method and give it a specific date to roll back.
private void btnRollBack_Click(object sender, RoutedEventArgs e) { AuditTrailHelper.RollBack(dtpRollBackDate.SelectedDate.Value, "Admin"); }
That’s all for implementation of Audit trial with entity framework. Here I deleted the audits which has been rolling backed. Definitely each Roll Back operation is also a db operation and I do audit for each roll back operation.
You can get the SourceCode and the article also in codeproject site - http://www.codeproject.com/KB/database/ImplAudingTrailUsingEFP-2.aspx
Hello,
Can you tell me what happens after you have implemented this model and then you have to make a change to the underlying table (i.e. you drop a column, add a column, change a column)? Since the XML that is persisted will now be different then your object won’t there be errors when you try to open up an EntityObject?
Thanks.
Steve Heidebrink
July 25, 2011