Robert on Software Development

Solving the Problem of the Data Layer

On a warm May afternoon in 2005, I sat in a conference room with some very smart people. We were discussing things like reference architectures, SOA, Solaris containers, and code generators. As members of a skunkworks R&D team at the company, we had to produce, and we made some great things. But what I remember most from working in that group and attending that meeting is that while we looked way up into the clouds for the next big thing, our feet were on shaky ground: we rarely agreed on anything. For example, none of us agreed on how a data layer should be properly constructed in an enterprise application. One person thought the built-in .NET tools were best; those tools have since been deprecated. Another person liked the monolithic God Class approach, where one class provided everything. I thought the specialized ORM tool built by the company–a data layer builder of sorts–was the best choice, since it offered the most configuration and decoupling.

In my new role here in Iowa, I’m in a situation where the data layer in a production application is practically nonexistent. The core business logic exists in stored procedures, and stored procedure calls are crafted mainly in the code-behind class files for the various ASP.NET pages. There’s also some stored procedure and SQL code in a massive static class. This approach is a maintenance nightmare. As a consultant brought in to get the product under control, I have to find a way to standardize the data layer without making a negative impact on the product. Easier said than done.

Throwing out the stored procedure code appeals to the architect in me ("Let’s start all over!"), but the realist in me knows that that is a thing you should never do. Given that the code that uses the stored procedures in the ASP.NET project is repetitive, I figured that using ORM would best solve this problem. I chose SubSonic over other choices like NHibernate because SubSonic wraps the stored procedure calls in objects (it does the same for our tables and other database objects). Now all the features of our database, where the business logic tragically lives, are available to us in an object-oriented way. I can refactor the existing code instead of rewriting it, and preserve the stored procedures as they are. Calling a stored proc will no longer require string-heavy usage of SqlCommand. We can use DatabaseName.SPs.StoredProcName(typed parameters).

For example, say you have your web application configured to use SubSonic. Your database connection string is named PurchasingSystem. In your actual SQL Server database, you have a stored procedure called UpdateItemList and it takes two parameters, an integer identifying the product list to update and a string that is the name of the item. (Yeah yeah, not the best example in the world, but stick with me.)

The SubSonic-generated code will let you do this:

PurchasingSystem.SPs.UpdateItemList(1, "Gears of War 2");

That is so much simpler and easier to maintain than the alternative of using ADO.NET objects.

As the kids say, ActiveRecord and scaffolding FTW.