Unit Test Database Code with COM+ (Enterprise Services)

I’ve been using NUnit for a couple years now and love it. However, most of my applications use databases extensively and most of the features within these applications have to do with managing the data in the database. This means I don’t have a lot of business logic for these operations, but I do have a lot of code that interacts with the database. I need unit test for that code. This is where COM+ comes in.

If you have talked to me about unit testing, I have probably mentioned using COM+ with unit tests to rollback any database transactions that occur within the context of the unit test. This means you can insert, update, delete and select data from within your unit test and when the test in completed, all changes will be rolled back. Since I’ve started using it, I can’t live without it. I don’t have any database dependencies with my tests, they are all self supporting. Plus, if you run the tests against a production database (on accident or to troubleshoot) the production data will not be modified.

Roy Osherove gives the details here.

One other tip I just learned that is associated with this method. You can read the “dirty” records within the database if you are stepping thru your COM+ unit tests by changing the transaction isolation level in Query Analyzer.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

Agile Management

 I’m sure you have seen the Agile Dilbert cartoon. I’ve been sent this cartoon from a couple people at work and now Javier is looking for my comments…So here goes.

The comic highlights that management doesn’t seem to understand the business impact of agile methods. There is a strong, embedded culture in management for fixed feature, fixed budget, and fixed timeline projects. In most organizations, this culture comes from the budget process. The budget process is similar to the waterfall method for software development. You define your objectives for the following year (scope), determine the hard costs to achieve those objectives (costs), track the budget through out the year(deadline) and tie a bonus for staying within budget.

What happens at the end of the year? If there is money left in the budget, managers find a way to allocate the money so they won’t lose the budget next year. Not very efficient! If they are short on money either move money from another budget, or find some ways to cut some costs last minute.

I think management would improve by implementing a more agile process. What if they supplement the long term objectives with short term goals (iterations), track progress along with costs (burn down) and have the option to change or cancel the objective if the business value isn’t found.

We are a long ways away from this happening, but there is hope. In Jack Welch’s new book, Winning, he talks about changing the fixed budget culture within G.E. to an “Operations Plan” culture. The operations plan is a yearly plan that outlines goals and costs for the company with flexibility to change over time. This frees managers to make decisions that are good for the business, not just good for their bonus.

VS.NET SQL Projects

On my current project, we’ve been using the SQL Project that’s included with VS.NET. I did this with some hesitation. I wanted the scripts to be integrated with the IDE, but I’ve never had any luck with managing SQL scripts. I had 3 issues:

1. The command file that VS.NET generates never executed my scripts in the correct order. If you execute a stored procedure that has a column that doesn’t exist, you’ll get an error
   
2. How do you manage scripts that should only be run once? You don’t want to add the same field twice.


3. Keeping code synchronized with the database changes

After some research and trial and error we fixed the first issue. I didn’t realize (since it isn’t documented anywhere that I could find) that the command file uses the script’s file extension to determine the order of scripts in the .cmd file. So it is important to name your files with the correct file extension.

The “Rules”:

1. Table files with the .tab file extension
   
2. Stored procedures with .prc
   
3. Finally .sql files for all data modifications

The second problem, we fixed with a simple rule: “All scripts must be able to be run multiple times”. What this rule means is that if a script is going to add a column to the table, the script needs to check if the columns already exist before running. This rule also makes it possible to integrate our SQL changes into our nightly build process. This is great because we validate our scripts every night; schema changes, data, and security settings.

Third, we simply created a folder for each version of the software. Under each version folder we added a folder for each database. The database folders allow us to right click and “Create Command File…”.

How do you handle your database changes? I’m always interested in suggestions.