.NET Musings

Last month, I got into a discussion with Matt Brewer and some fellow CinArch folks about T-SQL and testing.  I know some of the hard core managed code developers feel that all T-SQL is evil incarnate, but I strongly disagree.  True, CRUD should be handled by an ORM, but what about business logic that is set based?   As an architect, I believe that there are conditions where business logic belongs in the database layer.  But I am also a huge believer in Test Driven Development (TDD).  The end result of my conversation with Matt et al was my determination to figure out if there is a practical way to effectively TDD T-SQL (or at least TED T-SQL). (TED = Test Eventual Development – not the best choice, but at least unit testing gets done, eventually.)

So who else to call but the guys over at Cincinnati SQL Server Users Group – Chris Barth, Drew Marston, and Matt Rigling - to handle the database end of the discussion.  I presented to them how we (on the managed code side) use mocking tools It took a couple of sessions, but we came up with a pretty good prototype on how to test set based T-SQL.  It wasn’t completely without friction, and there are some pre-requisites, but we proved that it can be very successful to TDD T-SQL.

Pre-Requisites

SQL Server

• SQL Server 2005+ (We did all of our prototyping against SQL Server 2005)
• Blank copy of the database (correct with all relationships, indexes, etc)

Managed Code (C#/VB.Net)

• ORM Layer (we used NHibernate, but I presume EF, AR, etc. would work just as well)
• This is critical.  As each test will need to add the correct data records required, there must be an easy way.
• Unit Testing Framework that seamlessly handles Rollbacks (I use Gallio/MbUnit 3 since Rollback is handled through an attribute on each test)
• You could code up your own transactions, but that gets into too much friction
• .Net 3.5 (Ok, not required, but LINQ to Objects came in extremely handy for testing the records pre/post processing.)

Process

The basic steps are the same: Red/Green/Refactor.  The main difference for T-SQL developers is the definition of a Unit.  The main difference for C# developers is how we get to Green, and also how we refactor.

SQL Server

One issue that came up in our discussion was the definitions.  As Eric Evans strongly states, you must have a Ubiquitous Language, and the definition of Unit came up.  Managed code developers define a Unit of Code by following the Separation of Concerns (SOC), Don’t Repeat Yourself (DRY), Single Responsibility (SR), and You Ain’t Gonna Need It (YAGNI) principals.  T-SQL Developers (and even myself when I do T-SQL development) seem to think more in terms of functionality.

We had to decide what a Unit was so we could then test it.  We decided to follow the Managed Code TDD thought process and break the T-SQL code down into individual procedures and functions that follow the SOC/DRY/YAGNI/SR principals.  This is a path I had been moving down anyway in my T-SQL development, but applying the same principals to serve code as I do C# made me feel a whole lot better about the process.  And, this made for very different T-SQL! And, we decided that we would truly write the T-SQL in a TDD manner, so we had to get on with the managed code.

Managed Code (C#/VB.Net)

Prior to starting with our application test development, we had to handle some infrastructure tasks.  We wrote (test first) code to:

• Call Stored Procedures, passing in parameters dynamically
• Validate sets by making sure:
• The set only contained the number of records it should, and
• The set contained all of the values it should
• Insert records into the database
• This was already done in my projects since I use NHibernate, and already had tests wrapped around that CRUD code

Once you have the plumbing in place, the steps are simple:

1. Add [Rollback] to your test
2. Insert the data needed to setup the test
3. Execute the System Under Test
4. Pull back the data from the database
5. Assert that the action/inaction on the data is what the test specified

Pair Programming with Different PCs?

Once we got the plumbing done, the process was surprisingly fluid.  I wrote a test (in C#), Chris wrote the SQL.  It was kinda like pair programming, except we were using two different laptops.  As a SetUp task, I added the data I needed into the database, ran the Sytem Under Test, and then the Rollback attribute in MbUnit cleaned everything up for me.  We would get the test to green, then add additional Use Cases to make sure we had those covered, and once we got to green again, we moved on.

The speed of execution was better than I expected, and the tidiness (note I didn’t say elegance) of the solution was much better than expected.  Several of the issues that I have run into with existing projects (like how to setup all of the fringe cases) seem to be handled with this process.

Yes, I said “seem to be handled” because we developed a Prototype.  Still need to prove out the concepts in the real world.  But, I certainly plan on using this process when I have T-SQL code that needs to be developed, and will continue to refine the process as we do more of this.

I expect that will be a talk or too generated from this material that I will be adding to my repertoire.  Maybe for the SQL Server track at Central Ohio Day of .Net?

Happy coding!