1

I currently am developing a mvc 4 application. The client wants to have a change log, so that they can view what fields have been changed. It should show the old value and the new value, in case of an edit. If its a delete, then it should show what row was deleted.

Whats the best way to achieve something like this?

Just to clarify.. the changes will be the actual data in the database..

ie I might have a record of customer names and addresses.. the user might update the addresses.. I need the ability to see what the user changed.. old data and new data

Thanks...

user2206329
  • 2,792
  • 10
  • 54
  • 81

2 Answers2

2

If you want to get a list of the fields that have changed between your existing model in your database, and the model that is submitted when modified then you can do this in your Edit Post method of your controller:

IEnumerable<string> changedFields = Audit.GetPropertyDifferences(existingModel, newModel);

I've created a simple function that returns a bunch of strings showing the changed properties:

public static class Audit
{
    public static IEnumerable<string> GetPropertyDifferences<T>(this T obj1, T obj2)
    {
        PropertyInfo[] properties = typeof(T).GetProperties();
        List<string> changes = new List<string>();
        string name = string.Empty;

        foreach (PropertyInfo pi in properties)
        {
            object value1 = typeof(T).GetProperty(pi.Name).GetValue(obj1, null);
            object value2 = typeof(T).GetProperty(pi.Name).GetValue(obj2, null);
            DisplayNameAttribute attr = (DisplayNameAttribute)pi.GetCustomAttribute(typeof(DisplayNameAttribute));

            if (value1 != value2)
            {
                if (attr == null)
                {
                    name = pi.Name;
                }
                else
                {
                    name = attr.DisplayName;
                }
                if (value1 == null)
                {
                    changes.Add(string.Format("<li>{1} was added to {0}</li>", name, value2));
                }
                else if (value2 == null)
                {
                    changes.Add(string.Format("<li>{1} was removed from {0}</li>", name, value1));
                }
                else
                {
                    changes.Add(string.Format("<li>{0} changed from {1} to {2}</li>", name, value1, value2));
                }
            }
        }
        return changes;
    }

}

This code checks to see if a DisplayName attribute is set in the model, and uses that in place of the property name if it exists.

You can then display these changes or save them to the database like so:

if (changedFields.Count() != 0)
{
    foreach (string i in changedFields)
    {
        // Do something
    }
}
Evonet
  • 3,600
  • 4
  • 37
  • 83
  • This means you have to put this code everywhere you modify data. That's a huge maintenance nightmare. On top of that, it ignores that data can change from other sources, such from a data import, or even due to the results of some other process.. now you have to add code to those places if you have them as well. It's far more maintainable to do it at the point where the data changes in the db. – Erik Funkenbusch Oct 07 '13 at 05:27
  • Sure - you posted a db solution which the OP hasn't accepted, which is why I posted an alternate option. The GetPropertyDifferences function only needs to be written once, and then there is only a small amount of code for each controller on the key tables you want audited. You can then easily display notifications of changed fields on submit using TempData. – Evonet Oct 07 '13 at 05:52
1

This is typically done using triggers in the database. You create an audit table, and then create triggers that catch each change and record them.

An article talking about this is here:

http://www.codeproject.com/Articles/441498/Quick-SQL-Server-Auditing-Setup

More recent versions of SQL Server have auditing built-in, but only in the enterprise editions which are quite spendy.. most people don't have access to these features.

Another option, and I cannot speak to how well it works, are third party tools that claim to add auditing capability. For example, a simple google search found these:

http://krell-software.com/omniaudit/

https://renholdsoftware.com/SQLTableAudit

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • @TimMedora - As I said, Recent versions of SQL Server can do this, but it requires the enterprise version. This is true even in SQL 2012, at least if you want to capture what data changed. – Erik Funkenbusch Oct 06 '13 at 23:49
  • I was talking to someone and they said I should use entity frame work to do the change logs, as this doesn't depend on the actual database.(ie sqlserver).. is there any validity of that? – user2206329 Oct 06 '13 at 23:51
  • @user2206329 - Maybe. The problem is that using a software solution will not capture changes that are made manually to the database (by a user with SSMS, or some other tool that may change the database). Another problem is that software auditing might cause performance problems if your local record cache gets too large, as you now have to scan the entire cache looking for changes. Is your client likely to change database technology? Database level auditing is typically preferred for performance, robustness, and security reasons. – Erik Funkenbusch Oct 06 '13 at 23:54
  • @Mystere Man - The client will not (as of yet), but I just wanted to know the pros and cons and you have explained that quite well. So with the triggers on the tables. That means for each table I will need 3 triggers, and if I have 10 tables 30 triggers etc... – user2206329 Oct 07 '13 at 00:01
  • @user2206329 - it's usually sufficient to setup auditing only on key tables. If they want it on everything, then they have to understand the ramifications of that. – Erik Funkenbusch Oct 07 '13 at 04:49
  • @Mystete Man - Thanks for the info, yes I will have to explain that to my client. Much appreciated for your help. – user2206329 Oct 08 '13 at 00:49