With all of the talk about unit tests and test driven development, there is little talk about writing tests for stored procedures and other database code. I would argue that testing stored procs is just as important (if not more important) than testing .NET code because (a) you don’t recompile all of your SQL code when you change something and (b) bugs in stored procs can lead to really bad things like data corruption that are hard to fix.

Writing tests for database code is not rocket science. There are no SQL testing frameworks to learn (at least I don’t know of any), but that’s fine because you can do just fine with plain ol’ T-SQL.

Let’s say I have a database that looks like this:

create table Users
(
    UserId int not null primary key identity(1,1),
    Username varchar(255)
)

create table Roles
(
    RoleId int not null primary key identity(1,1),
    RoleName varchar(255)
)

create table UsersInRoles
(
    UserId int not null, -- foreign key to Users
    RoleId int not null, -- foreign key to Roles
)

And let’s say that we were going to write a stored proc that looks like this:

create procedure [dbo].[IsUserInRole]
(
    @UserId int,
    @RoleId int
)
as
begin

if exists (select 1 from UsersInRoles where UserId = @UserId and RoleId = @RoleId)
    return 1
return 0

end

We can easily write a test for this. The test might look like this:

create procedure [dbo].[unittest_IsUserInRole]
as
begin

begin transaction

-- set stuff up
declare @UserId int
declare @AnotherUserId int
declare @RoleId int
declare @AnotherRoleId int

insert into Users (Username) values ('test')
set @UserId = scope_identity()

insert into Users (Username) values ('another user')
set @AnotherUserId = scope_identity()

insert into Roles (RoleName) values ('test role')
set @RoleId = scope_identity()

insert into Roles (RoleName) values ('another test role')
set @AnotherRoleId = scope_identity()

insert into UsersInRoles (UserId, RoleId) values (@UserId, @RoleId)

-- test stuff
declare @result int

-- should return true if the user is in the role
exec @result = IsUserInRole @UserId, @RoleId
if @result <> 1
raiserror('Test failed: should return true if the user is in the role', 16, 1)

-- should return false if user is not in the role but is in another role
exec @result = IsUserInRole @UserId, @AnotherRoleId
if @result <> 0
raiserror('Test failed: should return false if user is not in the role but is in another role', 16, 1)

-- should return false if user is not in the role but another user is in the role
exec @result = IsUserInRole @AnotherUserId, @RoleId
if @result <> 0
raiserror('Test failed: should return false if user is not in the role but another user is in the role', 16, 1)

-- should return false if the user does not exist
declare @NonExistantUserId
select @NonExistantUserId = max(UserId) + 1 from Users
exec @result = IsUserInRole @NonExistantUserId , @RoleId
if @result <> 0
raiserror('Test failed: should return false if the user does not exist', 16, 1)

-- should return false if the role does not exist
declare @NonExistantRoleId
select @NonExistantRoleId = max(RoleId) + 1 from Roles
exec @result = IsUserInRole @UserId, @NonExistantRoleId
if @result <> 0
raiserror('Test failed: should return false if the role does not exist', 16, 1)

rollback transaction

end

Notice that I’m beginning a transaction at the beginning of the test procedure and I’m rolling it all back at the end. That way all of the test objects that I insert into the tables won’t be left around after the test is done. If one of my assertions fails, the test will throw an error (but everything will still get rolled back).

The last step is to call the test proc from a standard .NET unit test using your favorite testing framework, which might look like this:

[TestFixture]
public class When_checking_to_see_if_a_user_is_in_a_role
{
    [Test]
    public void Run_IsUserInRole_tests()
    {
        // call the test stored procedure here
    }
}

That’s it! Now you can have peace of mind when you write T-SQL code too!

The first time you see this, you think it’s a typo. But yes, === and !== are valid operators in JavaScript.

These operators do a strict comparison of equality by comparing both the type and the value. It’s easier to explain if I show you an example.

// This line will return true because both things mean 2
'2' == 2 

// This line will return false because on one side you have a string and
// on the other side you have a number.
'2' === 2 

Here’s another example:

var x = null;

if (x == undefined) // true
{
    // do something
}

if (x === undefined) // false!
{
    // do something
}

The first if statement evaluates to true because JavaScript says that null means the same as undefined, but the second if statement evaluates to false because null is not exactly the same as undefined.

Why would you care about this? Well, for the simple JavaScript that we write to manipulate stuff on a page of HTML, it probably doesn’t matter. But if you’re writing a complicated JavaScript library, this may be something that you might use. If anything, it’s good to know what it means so that you don’t freak out when you see it in someone else’s code.

In ASP.NET MVC, it’s common to have a situation where you post to one controller action and then redirect to another controller action. Let’s say that you want to pass values from the first controller method to the other. The problem is that in out-of-the-box ASP.NET MVC, there is no way to redirect to another action and pass a parameter into the action that you are redirecting to. Here is one way you can get around this problem:

public class AccountController : Controller
{
    [AcceptGet]
    public ActionResult Index(IndexViewModel model)
    {
        model.Message = (string) TempData["Message"];

        return View(model);
    }

    [AcceptPost]
    public ActionResult Save(SaveUpdateModel model)
    {
        // save the information
        TempData["Message"] = model.Message;
        return RedirectToAction("Index");
    }
}

The reason that you would do a redirect in this case is so that (a) you can reuse the code in the Index method and (b) you are redirecting to a GET operation, so if the user clicks Refresh in their browser, it won’t try to re-post the previous page.

I don’t really like how that code works, for various reasons:

• I’m using hardcoded strings (“Index” and “Message”). These might not get caught by refactoring tools if I want to change names, and I don’t get compile time checking on them.
• In the Index() method, I have to grab the value out of TempData. That works fine if you’re being redirected from the Save() action, but what if I’m being redirected from another action? What if I’m not being redirected from anywhere and the user is accessing the action directly? Ideally, when the Index() method is called, it should have everything that it needs in its parameters at the beginning of the method.

Personally, I don’t like to see TempData or ViewData anywhere in my controller methods. Don’t get me wrong, I need them and I’m going to use them, but I want it all done under the covers. I favor the “One Model In, One Model Out” pattern, where each controller action method takes in one model parameter (or none) and returns another model. Doing it this way allows me to avoid the use of magic strings and hashtables. I want my code to look like this:

[PassParametersDuringRedirect]
public class AccountController : Controller
{
    [AcceptGet]
    public ActionResult Index(IndexPresentationModel model)
    {
        return View(model);
    }

    [AcceptPost]
    public ActionResult Save(SaveUpdateModel model)
    {
        // save the information

        var presentationModel = new IndexPresentationModel();

        presentationModel.Message = model.Message;

        return this.RedirectToAction(c => c.Index(presentationModel));
    }
}

This code does exactly what I want. Now…

• I don’t have any magic strings.
• Everything is strongly typed.
• The parameter passed to the Index() method will always be complete. I don’t have to load anything out of TempData to get it to work.

One thing I love about ASP.NET MVC is that it is very extensible, so you can usually make it do what you want. This is the purpose of the MVCContrib project — people extending ASP.NET MVC to make it even better.

You may notice that in the code snippet above, I decorated my controller class with the [PassParametersDuringRedirect] attribute. Now, any parameters that I pass into RedirectToAction() will get passed into the action that I redirected to.

One slight problem… this new version of RedirectToAction() is an extension method on the Controller class, which means to call it, you would have to write “this.RedirectToAction()”. If you don’t want to have to write “this.” every time, you can just create a base controller class that has this method:

protected RedirectToRouteResult RedirectToAction(Expression> action) 
    where T : Controller
{
    return ControllerExtensions.RedirectToAction(this, action);
}

At some point, we all have to write some small apps. I’m talking about things like…

• Some small utility or diagnostic app
• Something to help with your deployment process
• Other small applications or websites (i.e. something that takes a month or less)

In these cases, we often throw good software design principles out the window. We say that we don’t need to write unit tests, we don’t need dependency injection, we can put data access code in our code-behinds, and things like that. Since it’s a small app, we think we can get away with it.

Just because your app didn’t take you long to write doesn’t mean that you get off easy. The consequences of your decisions just aren’t as severe, but that doesn’t mean that the pain is gone!

How many times have you written some small utility to help with something and then your boss sees it and really likes it, and then he asks you to add more functionality to it. Eventually you’ve spent a couple months on the app. If you cheated at the beginning, that code is going to hard to change and it’s going to start becoming more of a pain.

Maybe you’re writing a console app to help with your deployment process. Now this code better work, because if it doesn’t, your app might not deploy correctly. This is very important code! Doesn’t code this important warrant some extra attention (i.e. tests)?

Look, I’m not saying that every little app has to be this big, blown-out, enterprise application. I’m just saying that you should be careful when you cheat, because you don’t want that to come back to haunt you.

Many times you can take simple steps to make things easier to change. Take dependency injection, for example. It is really easy to set up an IoC container like StructureMap, it doesn’t overly complicate your code, and you don’t have to write tons of extra code to use it. But if you want to write tests for you code, dependency injection will make it a lot easier. You’re just putting code in better places.

Again, there are times to use dependency injection and there are times where it’s superfluous. There are times when unit testing is essential and times when you can get away with it (personally I always like writing tests if I can, that way I know my code works). But you need to be careful. Software is software, and many of the same development principles that apply to big apps still apply to small apps.

Every year, thousands of software development projects are developed. In most of these applications (especially line-of-business applications) you will see a lot of the same UI patterns:

• When the value in one dropdown changes, the list in another dropdown needs to change
• Two list boxes with arrow buttons that allow you move items between the lists
• A grid with add/edit/delete buttons that allow you to manipulate the list

Most applications have other common characteristics, like:

• The concept of a User that has to log in, reset their password, and change their password
• Some concept of security roles
• Loading objects from a database by ID

Every project also has housekeeping tasks, like:

• Setting up automated builds
• Copying files to a QA site after a build
• Coming up with a way to create a development copy of the database

The problem is that on every project, it seems that way too much time is spent getting this stuff to work. Why??

These are solved problems. We’ve all solved them before. But we end up re-implementing the same stuff over and over.

This is a problem. Making an AJAX call to update the list in a child dropdown when the parent dropdown changes is not rocket science. I don’t want to write this code ever again. I want to write stuff like this:

State: <%= this.Select(model => model.State)
                      .Options(Model.StateOptions, opt => opt.Id, opt => opt.StateCode)
                      .UpdateOptionsFor(model => model.County)
                      .WithAjaxCallTo(“/state/GetCountiesInState”) %>
County: <%= this.Select(model => model.County) %>

(I’m using the MvcContrib HTML Helpers here.)

You write the code to implement this fluent interface once, and you don’t ever have to write it again. That’s the way that it should be. Every time you do something, you should be trying to find a better way to do it so that you can do it better next time.

A lot of people have already done lots of work for you, and you should take advantage of it. I’m talking about things like this:

• Using the built-in ASP.NET Membership stuff instead of rolling your own code to do Forms Authentication
• Using Rails scaffolding to create your website
• Using styling and controls from jQuery UI
• Using convention-based MVC frameworks (e.g. Rails or ASP.NET MVC + MVCContrib)
• Using the auto-mapping capabilities of Fluent NHibernate instead of writing mapping files or CRUD stored procedures
• Use S#arp Architecture as the framework for your application

Every developer should have a toolbox of code that he/she can draw from so that you can avoid being a plumber and spend your time doing valuable things, like writing business logic, designing user interfaces, and other things that you are being paid to do.

As if I needed any more convincing, I ran into another situation where TDD saved me from a lot of pain.

On my current project, we have a lot of client-side Ajax-y goodness. The feature that I was working on was dragging something from one part of the page and dropping it on another part of the page. I was using jQuery UI’s Draggables and Droppables to do this. I got most of the way done, and then realized that jQuery UI draggable/droppable support does not work completely in IE because of some IE bugs that they apparently have not worked around yet. (A newer version of the jQuery UI has come out since I wrote this so it’s probably fixed now.)

Luckily, I wrote all my JavaScript the TDD way, so all of the UI concerns were separated out from the code that has the logic in it. So I was easily able to pull out jQuery’s drag and drop and replaced it with Yahoo UI’s drag and drop in only 4 hours (and I’ve never used YUI before). The only code I needed to change was the JavaScript that was in the ASPX page (this is all of the untestable stuff that wires stuff up to actual DOM objects). All of the underpinning logic (some of which still uses jQuery, as does the rest of the site) all works the same as it did before.

If TDD hadn’t led me into well-designed, object-oriented JavaScript, doing this would have taken a lot longer than 4 hours. In fact, it probably would’ve been really difficult to do without just rewriting most of what I had done. But this is one of the reasons that we write tests first — it gives a code based is easy to change and easy to maintain. I experienced that first-hand today.

I’ll be speaking at the Central Ohio .NET Developers Group this Thursday, Feb. 26 at 6pm… I’ll be talking about Unit Testing JavaScript using Test Driven Development.  We’ll go through what TDD is and how it can help you and show you how to write JavaScript tests against a sample application using QUnit.  Hope to see you there! (Oh, and there’s free pizza too!)

CONDG meets at the Microsoft building at Polaris… the address is 8800 Lyra Dr., Columbus, OH 43240.

The next time you are getting lazy about not writing your tests first and you need someone to keep you in line, you have someone that you can turn to!

On my current project we will be using QUnit to writing tests for our JavaScript.  What would be really nice is if our CI build could run JavaScript unit tests.

Well, it can!  It only takes a few magic potions and sacrificing a few small animals.

Luckily someone had done this before and blogged about it so I pretty much just had to follow instructions.  Here’s the long and short of it:

• We had to get WatiN, which will be used to create an instance of Internet Explorer that will run our HTML page with the QUnit tests in it.
• We had to get the IterativeTest addin for NUnit.  This will create one NUnit test for each QUnit test.

In the code, I added an IterativeTest (a test decorated with the [IterativeTest] attribute) that will run all of the QUnit tests.  Since we all use ReSharper and ReSharper doesn’t recognize the [IterativeTest] attribute, I also added a normal NUnit [Test] that will run all of the QUnit tests inside one test and fail if any of the QUnit tests fail.  So we should have all of our bases covered.

One of the common complaints that I’ve been hearing about our industry is that our industry lacks quality in general.  The evidence of this is this never ending cycle of rewriting software over and over because it is unmaintainable, buggy, etc.  Many people would say that this is sometimes a result of developers throwing code together quickly without taking the time to think out their design, write unit tests, or manually test their code. 

Thankfully the pendulum seems to be swinging and people are starting to expect higher quality from themselves and from the people that they work with.  More and more people are jumping on the TDD bandwagon (at least the people I work with are). 

However, speed is still very important — that is, the amount of time that it takes a developer to complete a task or the amount of time that it takes for a team to complete a project.  The fact of the matter is that we all have deadlines for our projects (well, maybe not if you’re working on open source projects).  Hopefully those deadlines are realistic, but they are deadlines that we have to meet.  So quality is definitely important, but speed cannot be ignored.

It seems to me that there is a stigma against developers who say that they work fast.  It seems that if you walk into an interview for a developer position and you say something like, “I’m capable of getting a lot of work done in a short amount of time,” you might as well keep looking because the interviewer will often times assume that you write crappy code and don’t care about quality.

But what if you are able to work quickly and not compromise on quality?  That would be the best of both worlds!  Just like we should all strive to write better quality software, we should also strive to get it done in as quickly as possible (without compromising on quality, of course).  Here are some ways that you can do this:

Become a keyboard ninja

They put those keyboard shortcuts in Visual Studio for a reason, you know.  If you’re writing .NET code you’re probably spending most of your day using Visual Studio (or another IDE if you’re using another language).  So why wouldn’t you take time to learn the keyboard shortcuts?  If you are using ReSharper (and you all should be), there are even more keyboard shortcuts to help you.

When I got my first job, I worked some guys whose goal was to never use the mouse.  These guys knew the keyboard shortcuts for everything, and they sure could get around fast.  Watch somebody who uses the mouse a lot, and watch how long it takes to stop typing, grab the mouse, drag and click, and go back to the keyboard.  It totally takes you out of your rhythm.

If you want to learn to use the mouse less, it really isn’t that hard.  Go print out the keyboard shortcuts for Visual Studio or ReSharper and tape them up at your desk.  Then anytime you reach for the mouse to do something in a menu or a toolbar, look on your cheat sheet and find the keyboard shortcut.  Highlight it on your sheet so that it’s easy to find next time.  You’ll be a keyboard ninja in no time.

Create easy-to-use frameworks

I’ve been on projects where it was really difficult to do simple tasks because the application’s framework put so much burden on the developer because it was way too over-complicated.  Sometimes things have to be complicated, but many times they don’t have to be.  When I’m designing frameworks, I will spend more time on writing framework code if it will make life easier for the developer using it.  Frameworks should help developers get things done, not hinder them.  The success of your project may hinge on this.

Use code generation tools

If you find that you’re doing a repetitive task over and over again, take the time to set up some code generation templates to help you generate that code.  There are many ways to do this:

• Code generation tools like MyGeneration
• Visual Studio code snippets
• ReSharper Live Templates (code snippets on steroids)
• Visual Studio file templates

Don’t get stuck writing plumbing code over and over again.  Usually there is some repeatable pattern that you can recreate using a some kind of code generation tools.  Every developer should be at least somewhat familiar with these kinds of things.

Control distractions

There are lots of things that can distract you while you’re writing code, like meetings, emails, IM, Twitter, etc.  Sometimes you can’t always control these (e.g. meetings) but the rest are under your control.  The world is not going to end if you can’t check your email, send IMs, follow Twitter, etc. for a day.  I’m not saying that you should always shut those off.  I’m just saying that we all need to be careful that they don’t become a distraction.  It’s really easy to get caught up in discussions on Twitter, but remember, you’re being paid to produce, not discuss things on Twitter.  (If you are getting paid to participate in discussions on Twitter, then lucky you!)

Buy ReSharper

I think I’ve already mentioned ReSharper several times now.  It’s only $199 for a personal license.  I don’t know how I wrote code for so long without it.  My company bought a license for everyone on my team and it was the best money they ever spent.  Some of the refactoring tools and keyboard shortcuts are really useful if you’re doing TDD and creating a lot of interfaces.