Auditing DB actions using MVC 4 and Entity Framework

This is a post from sometime last year that for some reason I never published.

This post describes an approach for an ASP.Net MVC application with a database-first EF Model.  I was using MVC 4 and EF 4.3.  The project this was for had some requirements and standards that have to be met about the degree of auditing on at the DB level. Simply overriding the EF SaveChanges() method to catch all actions will not suffice as the DB would likely be accessed by other means outside of the MVC/EF application. So table based triggers was the way to go but how to pass the username through to the DB?

The approach described below builds of off concepts outlined in the following articles:

1. http://lgsong.blogspot.ca/2012/01/contextinfo-and-entity-framework.html This article gives the general idea of what I'm doing here.  
2. http://jmdority.wordpress.com/2011/07/20/using-entity-framework-4-1-dbcontext-change-tracking-for-audit-logging/ See this article if you're using a Code-First approach (this was not an option for us).
3. http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx The code in this article was full of syntax errors and was missing spaces and required a little clean up in order to work with it but the ideas are good.  I adapted his approach of dynamically adding triggers to all the tables to work with the Audit table schema we were using.

The high level description of the approach is as follows:

1. Create Audit_Log and Audit_Comment tables in your DB
2. Add a stored procedure that takes a username and a comment string and inserts them into the comment table saving the generated comment_id and username in the database CONTEXT_INFO.
3. Add insert, update and delete triggers to each table that will first extract the comment_id and username from the ContextInfo() and then use those when inserting the audit details into the Audit_Log table.

Other Suggestions

Use an XML column to reduce the number of inserts.
Instead of storing before and after values in each audit entry row, the before values can be retrieved from the previous entry (inserts are also audited) although this makes queries on the audit data slightly complicated. Could also store only the columns that changed in the XML....

jQuery UI Tabs issue in MVC 4

I was trying to set up the jQuery UI Tabs sample in an ASP.Net MVC 4 application and got the following error in the console:

Uncaught TypeError: Object #<Object> has no method 'tabs'

It turned out that jQuery was not yet loaded at the time the .tabs() method was being called.  Scripts must be loaded in the correct order or the code they're referencing may not be available.  I had the bit in the document ready block but was still having a problem. The problem in the MVC case seemed to be the mix of using both MVC bundles and script tags. There is likely a proper way to do it if I were to read more about bundles and when they're loaded, etc. 

I decided to put all the script resources into bundles and loaded them that way and I left the bit of code calling $('#tabs').tabs() in a script tag. Still no luck. So I deleted that script tag, loaded my page, made sure all scripts were available and then in the chrome javascript console I entered $('#tabs').tabs() and BAM, the tabs appeared.  This proved that the script fragments were not being run in the order I had expected so I put the code in a separate js file and loaded it in a bundle as well and everything worked.

So if you're using Bundles, it appears your best bet is to use them across the board and not mix and match with inline script tags.

Playing with ASP.Net MVC 4

For an intro to MVC 4 have a look at this article.  I'm enjoying the scaffolding and the Razor templates (which were released with MVC 3 but hadn't played with them yet) and the Code First approach to using Entity Framework.

TDD in MVC applications

When developing a web application using MVC there are some components of the application that are not easily tested and can't be tested using automated unit tests. Since automated unit tests are the TDD bread and butter it follows that you can't use Test Driven Development for every aspect of your application. Our team has mentioned that it is difficult to know when to use TDD so I'm hoping to provide some guidance to help in this area. Any comments or suggestions about other methods are welcome.

Libraries: "That code should be in the Model!"

I think it's pretty simple really. Basically anything that will be used as a library function should have a unit test written for it and anything that should have a unit test written could be developed using TDD. In MVC, you would use TDD to develop Model code. If there is any code in the controller that contains some business logic that could be pulled out and made into a library function then that code should be in the model.

Any other aspect of the application would need to be tested using acceptance testing methods.

Design Patterns vs. Architectural Patterns

After discussing MVC and it's use (or non-use, actually) in some of our apps it became apparent that a bigger problem existed in the design of these applications. Now most of our apps are used in house and were developed initially by a group of bygone developers and as part of the ongoing task of trying to enhance and adapt them we are reminded of the benefits of a strong attention and adherence to good design.

Despite being written in an Object Oriented language, much of the functionality in these applications is accessed through static methods and the model classes are nothing more than containers. When model objects tend to be simple containers we get this confusion about what the controller actually is. If all appropriate code were actually in the model classes the division would be a little clearer. LINQ makes things a little easier because it creates a bunch of your model for you and you can build on it using partial classes and such. The problem persists in LINQ apps though where if developers don't follw good OO design we can have all these static methods adding another layer of complexity to everything.

There is an article on implementing MVC in ASP.Net that outlines it pretty well. The part I don't like is that the model is not object oriented but uses static methods. It is still a good reference though. So in the context of ASP.NET, the way I see it is that the code behind is the controller and basically everything else that is not an aspx page is the model.

Both Design Patterns and Architectural Patterns will improve the quality of code when applied. It follows that using them together will yield even better results.