Question

Dear, I want to implement audit trail, i need your suggestion to how to go about it .below are my requirements: Audit information to be stored in Two tables: Audit Trail / AuditTrailDetail. AuditTable structure: AuditId, SysObjectId, ActionDateTime,ActionType,ObjectAsString AuditTrailDetail table struc: Id, AuditId, SysObjectFieldId, old value, new value Please note ,i am maitaining SysObject and SysObjectField tables separate.

I could do this in prev version by implementing IDataObjectEventWatcher n capturing OnDataObjectSetProperty, OnDataObjectRemove how can be capture this events now. i went through your sample "SimpleAuditModule", i could not understand much except that you are intercepting session.entitycreate,remove etc events. I need your suggestion how cna i modify it to meet above requirements.

Thanks HAN

Updated at 15.07.2010 8:48:08

Thanks Alex, I already went through that,but i wanted to modify it as per my requirements mentioned in prev post. i want to display audit trail detais field by field and not as a string like you have "EntityAsString". i wish if you can guide,how to achieve my requirements, meanhwhile let me dig more into your sample.

Thanks HAN

Updated at 19.07.2010 14:02:42

Dear alex, i have modified the audit trail according to my requirements explained in above post. i wish if u could go thru and advise.

Gender.aspx has: AddData() and UpdateData() are similar so it looks like:

private void UpdateDate(long id)
        {
            try
            {
                Gender gender = Query.SingleOrDefault<Gender>(long.Parse(txtCode.Text));
                using (var transactionScope = Transaction.Open())
                {
                    Transaction.Current.Session.Extensions.Set(this.DataContext);         
                    using (var region = Validation.Disable())
                    {
                        gender.NameEn = txtNameEn.Text;
                        gender.NameAr = txtNameAr.Text;
                        region.Complete();
                    }
                    transactionScope.Complete();
                }
                ResetControls();
            }
            catch (Exception)
            {                
                throw;
            }

        }

my delete method is like this:

try 
            {

                    Transaction.Current.Session.Extensions.Set(this.DataContext);
                    using (var region = Validation.Disable())
                    {
                        Gender gender = Query.Single<Gender>(Id);
                        if (gender == null) throw new Exception("Record not found");
                        else
                        {
                            gender.Remove();
                            region.Complete();
                        }
                    }             
            }

my auditmodule is like this:

public sealed class AuditTrailModule : IModule
    {
        private class AuditTrailInfo
        {
            public HashSet<Key> CreatedEntities = new HashSet<Key>();
            public HashSet<Key> ChangedEntities = new HashSet<Key>();
            public HashSet<Key> RemovedEntities = new HashSet<Key>();
            public HashSet<AuditTrailDetailInfo> PropCollection = new HashSet<AuditTrailDetailInfo>();            
        }

        private class AuditTrailDetailInfo
        {
            public Key EntityKey;
            public Entity Entity;
            public FieldInfo FieldInfo;
            public string OldValue;
            public string NewValue;
            public bool Changed;            
        }

private void Session_EntityFieldValueSetCompleted(object sender, EntityFieldValueSetCompletedEventArgs e)
        {
            try
            {
                var entity = e.Entity;
                if (entity is AuditTrail || entity is AuditTrailDetail || entity is SysObject || entity is SysObjectField)
                    return; 
                SysObject sysObject = Query.All<SysObject>().Where(so => so.ObjectTypeId == e.Entity.GetType().GUID).First();
                if (sysObject.IsAuditable)
                {
                    var session = entity.Session;
                    if (session.IsDisconnected)
                        return;
                    var info = session.Extensions.Get<AuditTrailInfo>();                    
                    if (!info.ChangedEntities.Contains(entity.Key))                        
                        info.ChangedEntities.Add(entity.Key);

                    var detailInfo = new AuditTrailDetailInfo();
                    detailInfo.EntityKey = entity.Key;
                    detailInfo.Entity = entity;
                    detailInfo.FieldInfo = e.Field;
                    if (e.OldValue == null)
                        detailInfo.OldValue = "";
                    else
                        detailInfo.OldValue = e.OldValue.ToString();

                    if(e.NewValue == null)
                        detailInfo.NewValue = "";
                    else
                        detailInfo.NewValue = e.NewValue.ToString();

                    if (detailInfo.OldValue == detailInfo.NewValue)                    
                        detailInfo.Changed = false;
                    else
                        detailInfo.Changed = true;

                    info.PropCollection.Add(detailInfo);
                }

            }
            catch (Exception ex)
            {
                // Must not affect on operation!
                throw ex;
                //return;
            }
        }

private void TransactionOpened(object sender, TransactionEventArgs e)
        {
            var transaction = e.Transaction;
            if (transaction.IsNested)
                return;
            var session = transaction.Session;
            if (session.IsDisconnected)
                return;
            var info = new AuditTrailInfo();
            session.Extensions.Set(info);
        }

        private void TransactionCommitting(object sender, TransactionEventArgs e)
        {            
            var transaction = e.Transaction;
            if (transaction.IsNested)
                return;
            var session = transaction.Session;
            if (session.IsDisconnected)
                return;
            var info = session.Extensions.Get<AuditTrailInfo>();
            if (info.ChangedEntities.Count == 0)
                return;

            var batches = info.ChangedEntities.Batch(); // To avoid further GCs, if set is large

            foreach (var changedEntities in batches)
            {
                changedEntities
                  .Prefetch<AuditTrail, Key>(key => key)
                  .Run();
                foreach (var key in changedEntities)
                {                                      
                    var propCollections = info.PropCollection.Where(atdi => atdi.EntityKey == key && atdi.Changed==true);
                    if (propCollections.Count() > 0) 
                    {
                        var entity = Query.SingleOrDefault(key);
                        bool isRemoved = entity.IsRemoved();
                        //bool isRemoved = info.RemovedEntities.Contains(key);
                        bool isCreated = info.CreatedEntities.Contains(key);
                        if (isRemoved && isCreated)
                            continue; // Nothing really happened ;)

                        var auditTrail = new AuditTrail();
                        var dx = session.Extensions.Get<DataContext>();
                        auditTrail.SysUser = Query.SingleOrDefault<SysUser>(dx.UserId);
                        auditTrail.SysObject = Query.All<SysObject>().Where(so => so.ObjectTypeId == entity.GetType().GUID).FirstOrDefault();

                        auditTrail.WorkstationName = auditTrail.SysUser.LastLogInHostName;

                        auditTrail.ActionType =
                          isRemoved ? ActionType.Removed : // Order is important here!
                          isCreated ? ActionType.Created :
                                      ActionType.Changed;
                        auditTrail.SysObjectString = isRemoved ? key.ToString() : entity.ToString();
                        auditTrail.EntityKey = key;
                        auditTrail.ActionDateTime = DateTime.Now;

                        foreach (var auditTrailInfo in propCollections)
                        {
                            var auditTrailDetail = new AuditTrailDetail();
                            auditTrailDetail.AuditTrail = auditTrail;
                            auditTrailDetail.SysObjectField = Query.All<SysObjectField>()
                                                            .Where(sof => sof.SysObject == auditTrail.SysObject &&
                                                                   sof.Code == auditTrailInfo.FieldInfo.Name).FirstOrDefault();
                            if (auditTrailInfo.OldValue == null)
                                    auditTrailDetail.OldValue = "";
                            else
                                auditTrailDetail.OldValue = auditTrailInfo.OldValue.ToString();

                            if (auditTrailInfo.NewValue == null)
                                auditTrailDetail.NewValue = "";
                            else
                            auditTrailDetail.NewValue = auditTrailInfo.NewValue.ToString();
                        }
                    }

                }
            }
        }

        private void EntityEvent(object sender, EntityEventArgs e, bool created)
        {
            try
            {
                var entity = e.Entity;
                if (entity is AuditTrail || entity is AuditTrailDetail || entity is SysObject || entity is SysObjectField)
                    return; // Avoding recursion ;) 
                SysObject sysObject = Query.All<SysObject>().Where(so => so.ObjectTypeId == e.Entity.GetType().GUID).First();
                if (sysObject.IsAuditable) 
                {
                    var session = entity.Session;
                    if (session.IsDisconnected)
                        return;
                    var info = session.Extensions.Get<AuditTrailInfo>();
                    if (entity.IsRemoved)
                    {

                        var auditTrail = new AuditTrail();
                        var dx = session.Extensions.Get<DataContext>();
                        auditTrail.SysUser = Query.SingleOrDefault<SysUser>(dx.UserId);
                        auditTrail.SysObject = Query.All<SysObject>().Where(so => so.ObjectTypeId == entity.GetType().GUID).FirstOrDefault();
                        if (auditTrail.SysObject.GetType().GetInterface("IHaveEmployee") != null)
                        {
                            //auditTrail.EmployeeCode = entity.GetProperty<Gender>("Employee").ToString();
                        }
                        auditTrail.WorkstationName = auditTrail.SysUser.LastLogInHostName;
                        auditTrail.ActionType = ActionType.Removed; // Order is important here!                          
                        auditTrail.SysObjectString = entity.Key.ToString();
                        auditTrail.EntityKey = entity.Key;
                        auditTrail.ActionDateTime = DateTime.Now;
                    }
                    else 
                    {

                        if (info.ChangedEntities.Contains(entity.Key))
                            return;
                        info.ChangedEntities.Add(entity.Key); 
                        if(created)
                            info.CreatedEntities.Add(entity.Key);

                    }                        
                }

            }
            catch(Exception ex)
            {               
                throw ex;                
            }
        }

i know it would be diff for you to go through it but any help or suggestion will be much appreciated . thanks han

Updated at 20.07.2010 6:55:02

Alex,you are too good. i havent seen such support till date. understanding other's code and then suggesting needs lot of patience. anyways you got me right.my Webforms needs datacontext and BLL also needs it, so i had to write this class in separate assembly. Can i remove the need of this extra assembly. It looks like this:

public class DataContext
    {
        public long UserId
        {
            get;
            set;
        }

        public DataContext(long userId) 
        {
            UserId = userId;
        }
    }

i got some idea from your suggestion, though its not 100% clear to me. i am not creating session on my own. i am jsut opening a new transaction.. .if needed i can create a new session in my CRUD methods. actually i am still not clear when should we open a new session , or new transaction.. i would request you to go thru the audit module also. especially the Entityremovecompleted event and transactioncommitting event, i had to bring logic for any remove entity in entityremove event becuase in transactionscommiting event t gives me error that entity removed excpetion..i have writtine logic for storing field value changes which might be of intereset to some other deevlopers..

thanks HAN

Updated at 20.07.2010 8:29:15

Dear Alex, i have modfied my DataContext class as u said, it looks like this:

public class DataContext
    {
        public long UserId
        {
            get;
            set;
        }

        public DataContext(long userId) 
        {
            UserId = userId;
        }

        public static DataContext AttachTo(Session session,long userId) 
        {
            DataContext dx = new DataContext(userId);
            session.Extensions.Set(dx);
            return dx;            
        }

        public static DataContext Get(Session session) // may return null
        {            
            return session.Extensions.Get<DataContext>(); 
        }

        public static DataContext Demand(Session session)  // throws exception instead of returning null
        {
            if (session.Extensions.Get<DataContext>() == null)
                throw new Exception("DataContext not found");
            else
                return session.Extensions.Get<DataContext>(); 
        }

    }

and i have modifeid my UpdateData() method like this:

private void UpdateDate(long id)
        {
            try
            {
                Gender gender = Query.SingleOrDefault<Gender>(long.Parse(txtCode.Text));
                using (var session = Xtensive.Storage.Session.Open(DomainBuilder.Domain)) 
                {
                    DataContext.AttachTo(session,this.UserId);                  
                    using (var transactionScope = Transaction.Open())
                    {                        
                        using (var region = Validation.Disable())
                        {
                            gender.NameEn = txtNameEn.Text;
                            gender.NameAr = txtNameAr.Text;
                            region.Complete();
                        }
                        transactionScope.Complete();
                    }
                    ResetControls();
                }                
            }
            catch (Exception)
            {                
                throw;
            }

        }

but now this updateData() func is throwing me an exception "An attempt to automatically activate Session 'Default, #17' inside Session 'Default, #18' (Session switching) is blocked" what am i doing wrong here. and what is the correct way to do. pls suggest. thanks HAN

Updated at 20.07.2010 9:00:33

ok i understood that i should remove Gender gender = Query.SingleOrDefault<gender>(long.Parse(txtCode.Text)); and call it inside the using loop, after i create session. because entity is created with different session. and when i create inside the usign loop then it uses the same session to create Gender entity. but now it gives me Timeout error,

[OperationTimeoutException: Error 'OperationTimeout' while executing query 'UPDATE [dbo].[HBM_COM_Gender] SET [NameEn] = @p1_0 WHERE ([HBM_COM_Gender].[Id] = @p1_1); SELECT TOP 1 [a].[Id], [a].[TypeId], [a].[NameEn], [a].[NameAr], [a].[ObjectTypeId], [a].[ObjectName], [a].[ObjectFullName], [a].[IsLinkable], [a].[HasGrid], [a].[IsAuditable], [a].[MenuPath] FROM [dbo].[HBM_ADM_SysObject] [a] WHERE ([a].[ObjectTypeId] = @p0_0) ORDER BY [a].[Id] ASC; [p1_0='66';p1_1='514';p0_0='f37a14af-832e-3fa2-8c04-f744bb67e66d']'. Original message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.]

but when i move to my old logic, where i am passing datacontext to transaction current session eveything was working well before. i just changed few things in Gender UpdateData() form then why audit trail is givign error.

Updated at 20.07.2010 9:38:21

well when i commented the statement "using (var session = Xtensive.Storage.Session.Open(DomainBuilder.Domain))" everything worked fine. it did not give me any timeout error.

so basically i have modified this code :

using (var session = Xtensive.Storage.Session.Open(DomainBuilder.Domain))
                {
                    using (var transactionScope = Transaction.Open())
                    {
                        using (var region = Validation.Disable())
                        {
                            DataContext.AttachTo(session, this.UserId);
                            Gender gender = Query.SingleOrDefault<Gender>(long.Parse(txtCode.Text));
                            gender.NameEn = txtNameEn.Text;
                            gender.NameAr = txtNameAr.Text;
                            region.Complete();
                        }
                        transactionScope.Complete();
                    }
                    ResetControls();
                }

to this code:

//using (var session = Xtensive.Storage.Session.Open(DomainBuilder.Domain))
                //{
                    using (var transactionScope = Transaction.Open())
                    {
                        using (var region = Validation.Disable())
                        {
                            DataContext.AttachTo(Transaction.Current.Session, this.UserId);
                            Gender gender = Query.SingleOrDefault<Gender>(long.Parse(txtCode.Text));
                            gender.NameEn = txtNameEn.Text;
                            gender.NameAr = txtNameAr.Text;
                            region.Complete();
                        }
                        transactionScope.Complete();
                    }
                    ResetControls();
                //}

i dont understand why using Session statemnet is giving timeout error. for sure i am missing some information, pls help why it is behaving like this. thanks

Updated at 20.07.2010 13:38:45

Thanks for your replies. it has given me really good idea about the implementation but few things are not clear. 1. how to use the same session or transaction. 2. is it necessary to always open a session before your begin transaction. i have seen your samples doing using (Session.Open(domain)) { using (var transactionScope = Transaction.Open()){} }

thanks HAN

This thread was imported from our support forum. The original discussion may contain more detailed answer.

Accepted Answer

1) You should modify entity event handler so that all the info you need is extracted from EntityEventArgs there. It's here: http://goo.gl/JVOl 2) Note that this single handler traps 3 events in this sample: http://goo.gl/ap7a

Am I right that you use DataContext to bind such info as UserId to Session?

If so, why don't you isolate all the code related to it? It can be e.g. in DataContext itself:

void DataContext.AttachTo(Session session);
static DataContext DataContext.Get(Session session); // may return null
static DataContext DataContext.Demand(Session session); // throws exception instead of returning null

Further, I'd try to attach it automatically when I create / acquire a session.

If you create Session by your own, i.e. using Session.Open, wrap this call to some method attaching DataContext to newly created Session
If you use our SessionManager (in web application), either set its SessionProvider delegate to your own (its ProvideSession() method is default implementation of this provider), or inherit from it and override its ProvideSession() method.

Timeout happens because you're getting application-level deadlock on SQL Server: the code executed by the same thread controls two Sessions (and transactions) racing for the same resource. Obviously, if some resource is locked for the first Session, an attempt to read it in second one will lead to such application-level deadlock:

the thread will wait for query result in Session 2
SQL Server doesn't returns this result, since it must wait for lock release first
but lock is actually acquired for Session 1, which is controlled by the same (waiting) thread.

That's the case you had. Unfortunately, I was unable to read all the code, but the fact you simultaneously control two Sessions by the same thread means this is the case you have with very high probability.

And about Session switching check: see http://code.google.com/p/dataobjectsdot ... ail?id=406

Use either manual activation (using (secondSession.Activate()) { ...secondSession is active here... }) or deactivation (using (Session.Deactivate()) { ...any session can be safely activated here... }) to deal with this.

Concerning your logging code: I strongly recommend you to commit all the audit log info inside the same transaction (and, consequently, same Session).

If you'll do this in different one, a) there is no guarantee it will be committed b) if you don't use snapshot isolation (it looks like this is true), there is a chance of getting deadlocks (even if you'll use another thread) c) if you'll try to commit audit records before completion of original transaction, there is a chance that only audit records will be committed, but main transaction will be rolled back.

So in short, such approach is more complex + to handle this safely, you need distributed transactions. So use the same transaction.

1) By binding to Session events. The simplest way to do this is to bind to Domain.SessionOpen event, and from its handler bind to all Session events.

2) Session is connection analogue, so yes, you must always open a Session. When a transaction is opened, it is always bound to the current Session. See "Current Session, Session activation" chapter in Manual, it explains how\when current Session is activated and deactivated. Chapter from v4.2 Manual (probably, a bit outdated) is here.