You can get information about the current user who is logged in using Thread.CurrentPrincipal. The problem with this is that it’s difficult to work with it in tests. It forces you to set the current principal in your test setup so that your code will work correctly, and this can be difficult if not impossible in situations where you’re using Windows Authentication (if you’re using Forms Authentication, it’s not quite as painful, but it’s still a pain).

I don’t like to actually have calls to Thread.CurrentPrincipal scattered throughout my code. It makes my code less readable. Usually if I’m checking the principal, I want to know what user is logged in. That leads to code like this:

var currentUser = null;
var principal = Thread.CurrentPrincipal as MyCustomPrincipal;
if (principal != null)
    currentUser = ((MyCustomPrincipal) Thread.CurrentPrincipal).User;

Ick. I don’t want to write this over and over throughout my code. What if I had a class called PrincipalFactory that dealt with everything having to do with the principal? My class could look like this:

public class PrincipalFactory : IPrincipalFactory
{
    public MyCustomPrincipal GetCurrent()
    {
        return Thread.CurrentPrincipal as MyCustomPrincipal;
    }

    public User GetCurrentUser()
    {
        var principal = GetCurrent();
        return (principal != null ? principal.User : null);
    }

    public void SetCurrentUser(User user)
    {
        Thread.CurrentPrincipal = new MyCustomPrincipal(user);
    }
}

Now when I want to know who the current user is, I can write…

var currentUser = _principalFactory.GetCurrentUser();

Isn’t that much easier on the eyes?

It’s much easier to test too. I always use dependency injection and StructureMap, which also makes testing easier. Let’s say I had a class like this:

public class AccountController : Controller
{
    private IPrincipalFactory _principalFactory;

    public AccountController(IPrincipalFactory principalFactory)
    {
        _principalFactory = principalFactory;
    }

    [Authorize]
    public ActionResult Index()
    {
        var model = new IndexViewModel();
        model.WelcomeMessage = "Hello, " + _principalFactory.GetCurrentUser().Username;
        return View(model);
    }
}

In my test code, I’m going to use Rhino Mocks to stub out the IPrincipalFactory. So the test will essentially pretend that a user named “Jon” is the current user and then we’ll make sure that the Index() method does the right thing.

using System;
using NUnit.Framework;
using Rhino.Mocks;

[TestFixture]
public class When_displaying_the_index_view()
{
    [Test]
    public void Should_display_the_welcome_message_for_the_current_user()
    {
        // Arrange
        var principalFactory = MockRepository.GenerateStub();
        principalFactory.Stub(pf => pf.GetCurrentUser()).Return(new User { Username = "Jon" });

        var controller = new AccountController(principalFactory);

        // Act
        controller.Index();

        // Assert
        Assert.IsTrue(((IndexViewModel)_controller.ViewData.Model).WelcomeMessage, Is.EqualTo("Welcome, Jon"));
    }
}

This is much easier and cleaner than having to deal with Thread.CurrentPrincipal all over the place. If you didn’t hide Thread.CurrentPrincipal inside the PrincipalFactory class, you could still test it by setting Thread.CurrentPrincipal in the beginning of your test. I’ve been on projects that did it this way, and inevitably someone forgets to set Thread.CurrentPrincipal back to what it was before the test, or an exception happens and somehow the code that sets it back to the original value doesn’t run, and now tests will have different outcomes depending on what order you run them and which ones you run. This is usually a real pain to debug because you don’t know which test is causing the problem. Not fun.

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!