Monday, October 7, 2013

More TFS woes

Disclaimer: TFS is not my VCS of choice. I use it because that is what the client site where I'm working uses.

I'd heard about it before but only just ran into it myself today. I'm referring to the TFS character limit which I've come to learn is actually a Windows character limit that is a holdover from older APIs. NTFS actually supports filenames in excess of 32000 characters yet windows APIs, and thus any tool built on top of those APIs, still has a limit of 260 characters. Apparently Microsoft doesn't intend to fix it (or didn't in 2011). If that's not technical debt then I don't know what is.

They have started addressing it to some degree in TFS 2012 with support for up to 400 character paths but on the server only.

Friday, April 5, 2013

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....



Threading Woes

Until recently I've gotten away with only looking at or making minor tweaks to VB.Net code. I've now been tasked with modifying a web service that involved reviving some cached values in a background thread so the user wasn't left waiting for the task to finish. If the values were cached the service responded well but once the cache expired there was a long wait (minutes) for any request that came in while the cache was rebuilt. The solution, until a replacement service is completed, was to preemptively rebuild the cache in a thread prior to expiry. The solution involved locks to ensure only one thread is rebuilding the cache at once and thus prevent cycles spent needlessly rebuilding the cache multiple times. Locks in VB are created using SyncLock which is like lock in C#. The challenging bit for me (and I now know way more about VB than I ever intended to) was that the Static keyword in VB does not work the way you would expect coming from a C# perspective (or any other perspective I've encountered for that matter) and can only be applied to local variables and properties. It turns out what I really wanted was a Private Shared ReadOnly object although it took me some time and plenty of tinkering to come to that conclusion.

Also, despite the great thread debugging tools available in Visual Studio, print statements proved invaluable in actually seeing what order things were happening in and seeing when the locks were actually working or not. Sometimes the simplest approach is the most effective.