Unit Testing SQL Providers in ASP.NET

I think I finally found a use for SQL Server Express Edition!  For the longest time I wondered why I would want to use this not-quite-the-full-thing version of SQL Server.  When you have an MSDN subscription you have SQL Server Developer Edition and it offers you all that you want and more.  The answer of it's value dawned on me today as I was working to advance our efforts around testing the provider model.

We have written some extensions to the Membership Provider that shipped with ASP.NET.  This included a few changes to the way password resets were handled.  Like a good development team we want to ensure that it worked as advertised and that is where testing plays a role and where SQL Server Express comes in. Now, before I get into it let me acknowledge that the ASP.NET provider model is not well suited to test with.  Matt Berther alludes to this in a brief discussion on the matter. I have had to use a bit of reflection to make it work.  But I am willing to pay the price.

The plan was to write a test fixture that tested the provider.  This test fixture will need its own copy of the database at run-time in order to be somewhat isolated from other concurrent tests.  This is the part where SQL Server Express comes into play with the User Instance feature.  The basic flow would be as follows:

• Prior to running the fixture tests we would create a database using the SQL scripts
• Run through each of the tests
• Leave the database in place in case the test fails and we want to do further investigation

Because our build service runs as a regular user we want to ensure that we can do this while not having to delegate any administrative privileges.  Using User Instances and the SQL Server Management Objects I took the scripts from the ASP.NET Membership Provider and did just that:

[ClassInitialize]
public static void ClassInitialize(TestContext testContext)
{
    // Create the test database
    DatabaseHelper.CreateDatabase("CommunityMembershipProviderTest", testContext.TestDeploymentDir);
    DatabaseHelper.ExecuteScript("CommunityMembershipProviderTest",
                                 Path.Combine(testContext.TestDeploymentDir, "InstallCommon.sql"));
    DatabaseHelper.ExecuteScript("CommunityMembershipProviderTest",
                                 Path.Combine(testContext.TestDeploymentDir, "InstallMembership.sql"));

    // Add connection string to configuration file
    DatabaseHelper.AddConnectionString("CommunityMembershipProviderTest", DatabaseHelper.BuildConnectionString("CommunityMembershipProviderTest"), DatabaseHelper.ProviderName);
    ConfigurationManager.RefreshSection("connectionStrings");
}

To enable reuse I encapsulated a number of the database bits into a separate database helper class:

static class DatabaseHelper
{
    #region Fields
    public const string ServerName = ".\\SQLEXPRESS";
    public const string ProviderName = "System.Data.SqlClient";
    #endregion

    #region Methods
    public static void AddConnectionString(string name, string connectionString, string providerName)
    {
        string configFile = Assembly.GetExecutingAssembly().CodeBase.Substring(8).Replace("/", "\\") + ".config";
        XmlDocument document = new XmlDocument();
        document.Load(configFile);

        XmlNode connectionStringNode = document.CreateElement("add");
        connectionStringNode.Attributes.Append(document.CreateAttribute("name"));
        connectionStringNode.Attributes.Append(document.CreateAttribute("connectionString"));
        connectionStringNode.Attributes.Append(document.CreateAttribute("providerName"));
        connectionStringNode.Attributes["name"].Value = name;
        connectionStringNode.Attributes["connectionString"].Value = connectionString;
        connectionStringNode.Attributes["providerName"].Value = providerName;

        XmlNode connectionStrings = document.SelectSingleNode("//connectionStrings");
        connectionStrings.AppendChild(connectionStringNode);

        document.Save(configFile);
    }

    public static string BuildConnectionString()
    {
        return BuildConnectionString(null);
    }

    public static string BuildConnectionString(string databaseName)
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        if (!string.IsNullOrEmpty(databaseName))
        {
            builder.InitialCatalog = databaseName;
        }
        builder.DataSource = DatabaseHelper.ServerName;
        builder.IntegratedSecurity = true;
        builder.UserInstance = true;
        return builder.ToString();
    }

    public static void CreateDatabase(string name, string folder)
    {
        ServerConnection connection = new ServerConnection();
        connection.ConnectionString = DatabaseHelper.BuildConnectionString();

        Server server = new Server(connection);

        if (server.Databases.Contains(name))
        {
            server.Databases[name].Drop();
        }

        string sqlCommand = string.Format(CultureInfo.InvariantCulture,
                                          "CREATE DATABASE {0} ON (NAME = {0}, FILENAME = '{1}\\{0}.mdf') LOG ON (NAME = {0}_LOG, FILENAME = '{1}\\{0}.ldf');",
                                          name, folder);

        server.ConnectionContext.ExecuteNonQuery(sqlCommand);
    }

    public static void ExecuteScript(string databaseName, string scriptFile)
    {
        ServerConnection connection = new ServerConnection();
        connection.ConnectionString = DatabaseHelper.BuildConnectionString();

        Server server = new Server(connection);

        string script = null;
        using (StreamReader reader = new StreamReader(scriptFile))
        {
            script = reader.ReadToEnd();
        }

        server.Databases[databaseName].ExecuteNonQuery(script);
    }

    public static void DropDatabase(string name)
    {
        ServerConnection connection = new ServerConnection();
        connection.ConnectionString = DatabaseHelper.BuildConnectionString();

        Server server = new Server(connection);
        if (server.Databases.Contains(name))
        {
            server.Databases[name].Drop();
        }
    }
    #endregion
}

(NOTE: I remove the input validation for brevity) 

Now the database helper gets me setup, but there is one more thing I hit when it came to running against the Membership provider in particular, and that is the Membership.Providers collection.  When you create a new MembershipUser it does a check against that collection to ensure the provider actually exists.  The problem is that the collection in question is read only.  Reflection to the rescue with this one as I dig behind the scenes and inject the provider directly into the hash table:

private static void InjectProvider(ProviderCollection collection, ProviderBase provider)
{
    typeof(ProviderCollection).GetField("_ReadOnly", BindingFlags.Instance | BindingFlags.NonPublic).SetValue(collection, false);

    Hashtable hashtable = (Hashtable)typeof(ProviderCollection).GetField("_Hashtable", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(collection);

    if (hashtable[provider.Name] == null)
    {
        hashtable.Add(provider.Name, provider);
    }
    else
    {
        hashtable[provider.Name] = provider;
    }
}

Normally I would refrain from such behaviour, but it's the only way I can see to make this work.  Running the unit tests now works like a charm:

[TestMethod]
public void CreateUserTest()
{
    CommunityMembershipProvider target = new CommunityMembershipProvider();
    NameValueCollection config = this.ProviderConfiguration;
    MembershipCreateStatus status;

    target.Initialize(config["applicationName"], config);
    ProviderHelper.InjectProvider(Security.Membership.Providers, target);

    target.CreateUser("colin", "p@ssw0rd", "colin@rockstarguys.com", "Knock knock?", "Who's there?", true, Guid.NewGuid(), out status);

    Assert.AreEqual(status, MembershipCreateStatus.Success);
}

I now have tested SQL-based providers! I have attached a sample with all of the bits strung together.