Sometimes it's just much harder than it should be

So here's what I want to do:

Call MsBuild from MsTest, so I can test a custom MsBuild task. The MsBuild task works on the database, so I need to control the database from the test too. I've got some unit testing in place. What I want now is one or two integration tests to check it hangs together.

Here's how it worked out:

I need to use Process to call the msbuild.exe. I want the error code and the command line output back so I can check them in my asserts...fiddle around...google....there:


public static int CreateProcess(string filename, string arguments, out string output)
{
    Process process = new Process();
    process.StartInfo.CreateNoWindow = true;
    process.StartInfo.UseShellExecute = false;
    process.StartInfo.RedirectStandardOutput = true;
    process.StartInfo.WorkingDirectory = AssemblyDirectory();
    process.StartInfo.FileName = filename;
    process.StartInfo.Arguments = arguments;  

    StringBuilder sb = new StringBuilder();
    process.OutputDataReceived += (sender, args) => sb.AppendLine(args.Data);
    process.Start();
    process.BeginOutputReadLine();
    process.WaitForExit();

    output = sb.ToString();
    return process.ExitCode;
}

Call my task...my tasks not there...I've got the relative path wrong...no it's right....scratch head...what's the location of the executing assembly? Google, google. There's three methods....try them out...OK...got it...still not working....where actually is the dll? Cripes what it doing there?
```
static public string AssemblyDirectory()
{
    string codeBase = Assembly.GetExecutingAssembly().CodeBase;
    UriBuilder uri = new UriBuilder(codeBase);
    string path = Uri.UnescapeDataString(uri.Path);
    return Path.GetDirectoryName(path);
}
```

MsTest is copying the test dll and putting it in another directory....hmm....I need to get my task put there. Make the task an embedded resource and write it out in the test....OK....embedded resource....it's not finding it...got the namespace wrong...why does it need the namespace?...arse....change my code to scan all the embedded resources for my file with a bit of LINQ.


public static void WriteResourceToFile(string filename)
{
    WriteResourceToFile(Assembly.GetExecutingAssembly(), filename);
}

public static void WriteResourceToFile(Assembly assembly, string filename)
{
    string[] fullyQualifiedNames = assembly.GetManifestResourceNames();
    string resourceName = fullyQualifiedNames.FirstOrDefault(s => s.Contains(filename));
    if (resourceName == null)
    {
        throw new ArgumentException(filename + " could not be found.  Ensure that the Build Action in properties is set to Embedded Resource.");
    }
    Stream stream = assembly.GetManifestResourceStream(resourceName);
    StreamToFile(filename, stream);
}

Got the embedded resource.....as a stream....I hate streams....how do I change it to a file?....can't find a neat way of doing this....google....this guy makes an XmlDocument from the stream then saves the XmlDocument. OK, its rubbish but it'll do.
Still not working...need to get the task definition file there too....another embedded resource...refactor...done. It works I can call an MsBuild task from MsTest.
Add a simple test. Phew. Check-in.
Make a database from the test....that looks a bit hard. How about add the database manually, but set it up right before the test? OK. Right. Need to create a table in the database for the test, got a script for the table somewhere, got it. Need to call the script....hmm. Well I've got a method which will execute an SQL string, so I'll just write the SQL in-code and use that. OK tweak the method to accept a connection string, rather than a list of database parameters...hey this is almost like real work....I needed to make that modification later. OK call the method....its internal.....make it public. Right, that's the test set up done.
```
public static void ExecuteNonQuery(string connectionString, string sqlScript, int commandTimeout)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(sqlScript.ToString()))
        {
            connection.Open();
            command.CommandTimeout = commandTimeout;
            command.Connection = connection;
            command.ExecuteNonQuery();
        }
    }
}
```
The test itself....the MsBuild task scans a directory for SQL scripts, then executes them, the filename of the executed scripts gets written into the table. OK so I need to put my test SQL script in a known location. Embedded resource again...bollocks its not an XML file so my method won't work....back to google...someone must have written a stream to file once, surely...ah. Jon Skeet in a newsgroup with the answer...there. Refactor the code to get rid of that skanky XML document thing...that's better. Tested. Woop.
```
public static void StreamToFile(string filename, Stream stream)
{
    using (Stream output = File.Create(filename))
    {
        byte[] buffer = new byte[32 * 1024];
        int read;  

        while ((read = stream.Read(buffer, 0, buffer.Length)) > 0)
        {
            output.Write(buffer, 0, read);
        }
    }
}
```
Well almost, the test can only run once. I need to reset the database before the test is run again.....I saw in another of our projects a script for cleaning a database...lets look at that. Hmm...looks quite good...that should work. It's an SQL script, right wop the whole lot into code and call it with our execute SQL string method.
OK. The next test. I need to make sure that any existing SQL scripts are deleted so they don't interfere with the next test. Delete the SQL files in a directory....I've done this before....google, google. A bit of LINQ might make this neater, there:
```
Directory.GetFiles(".", "*.sql").ToList().ForEach(f => File.Delete(f));
```
Right. I'm set. A bit more real work. Check in.
OK. Now with all this machinery is in place its not actually too hard to add a test. We've got another MsBuild task to compare databases. A colleague was saying he wanted to tweak the task. I could just put a couple of tests in place, to show him how the tests work. OK....done...not to hard....code review. Generally positive, but he points out it would be better if the test actually created the databases. Rather than requiring this manual set up. He's right, but I've had enough. Check in and work on something else.

OK. I've got the end of a Friday afternoon free. Creating the databases is still needling me. Lets give it a try. OK drop and create a database. We've got a script for this. Paste it into code, and call it with ExecuteNonQuery. Good. Right. Use it.


public static void DropAndCreateDatabase(string databaseName, string masterConnectionString)
{
    string dropAndCreateDatabase =
@"IF EXISTS(select * from sysdatabases where name = 'DatabaseName')
BEGIN
    ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE DatabaseName
END
CREATE DATABASE DatabaseName
ALTER DATABASE DatabaseName
    SET RECOVERY SIMPLE
";
    dropAndCreateDatabase = dropAndCreateDatabase.Replace("DatabaseName", databaseName);
    DBHelper.ExecuteNonQuery(masterConnectionString, dropAndCreateDatabase, 30);
}

OK, the tests which use integrated security are OK. But the database compare test use an SQL user. I'll have to make a login and a user and associate them. Hmmm no scripts for that, google, google, google. There we go. I rock.
Ooo. A bit of investigation has shown up a bug in the compare databases MsBuild task. You don't get an error if the databases just don't exist. Fix that.
Run all of the test and check in. Fuck, when I run them all together some of them fail. The tests don't depend on each other....but....but... I'm getting some error about connection closed.....they do use a single database and MsTest runs the tests in parallel. Make MsTest run test in serial...google....OrderedTest.... a UI's opened up and it doesn't make any sense....anything else...google, google, google.....fuck. fuck. fuck. I'm stuck. There's that bug fix I really want too. Shelve it. Go home.
Couple of week passed. Yesterday I promised I'd get this cock problem with the tests sorted. Right bite the bullet and do this ordered test thing, get it over with, and change it to NUnit next time we work on it. OK this weird UI...try harder...it still doesn't make any sense. Google. Ahhh. Make MsTests run one at a time using cunning locking thing. There we go. Nice.
```
public static class LockClass
{
    public static object LockObject = new object();
}  

[TestInitialize()]
public void PerTestSetup()
{
    Monitor.Enter(LockClass.LockObject);
}

[TestCleanup()]
public void PerTestTearDown()
{
    Monitor.Exit(LockClass.LockObject);
}
```
Hmm well I think its fixed some of the failures, bit it still doesn't work...Google the error.....Shared memory should try TCP/IP...I can force that in the connection string....now it doesn't work at all...enable TCP/IP in the SQL configuration manager....I'm sure I've already done that....take a look anyway....jeeps there are 32-bit and 64-bit versions....one of them doesn't have tcp enabled but the others do...change it....a different error message...progress. Google....stuff about connection pooling....I can switch off connection pooling in the connection string. Finally.
```
string connectionString = "Server=(local);Database=MsBuildTest;Trusted_Connection=True;Pooling=false;";
```
Code review....great....Check in....oh dear the build taking a long time....timed out. I bet something's wrong with the create database thing....the lock thing and create method are both in test setup so the locks hanging around and stopping a proper failure. Fix. That's "better" only 1 min to a build error now, and I get a message.
Hmm, drop login doesn't work. The build server runs SQL Server 2000 (we have to support some old stuff). Right skype someone who knows about this antediluvian stuff. OK change it to sp_droplogin. Check in again...this time its create login...no surprise I s'pose....change it to sp_addlogin. Run locally... fails....the password policy in SQL 2005+ requires a strong password....but that's not what I want....how do I set CHECK_POLICY off in 2000? google, google, google. I can't.

OK, what about this. In order to drop and create the login in both SQL Server 2000 and 2005, make two versions of the script and choose which one to run based on the version? Google get SQL version. Fiddle, got it. Nearly there, I need to make sure the script will still compile in SQL 2000 even though it doesn't have DROP LOGIN, so I need to EXEC these commands as a string...run local, OK...check in.


public static void DropAndCreateLogin(string login, string masterConnectionString)
{
    string dropAndCreateLogin =
@"IF (SELECT LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR),1)) = '8'
BEGIN
    IF EXISTS(select * from syslogins where name = 'LoginName')
    BEGIN
        EXEC sp_droplogin LoginName
    END
    EXEC sp_addlogin LoginName, LoginName
END
ELSE
BEGIN
    IF EXISTS(select * from syslogins where name = 'LoginName')
    BEGIN
        EXEC('DROP LOGIN LoginName')
    END
    EXEC('CREATE LOGIN LoginName WITH PASSWORD = ''LoginName'', CHECK_POLICY = OFF')
END
";
    dropAndCreateLogin = dropAndCreateLogin.Replace("LoginName", login);
    DBHelper.ExecuteNonQuery(masterConnectionString, dropAndCreateLogin, 30);
}

OK. The builds got further but now its failing on the create user...google...OK. Use stored procedures to create user and make the user db_owner. This is it folks. Check in. Woop. The green bar. Job Done.


public static void AddUserAndMakeDbOwner(string login, string connectionString)
{
    string userAndRole =
@"EXEC sp_grantdbaccess 'LoginName'
EXEC sp_addrolemember db_owner, LoginName
";
    userAndRole = userAndRole.Replace("LoginName", login);
    DBHelper.ExecuteNonQuery(connectionString, userAndRole, 30);
}

Cripes. Often when your coding, the problems your having are entirely you own fault. The codes crap and the architecture's shoddy. That's not the case here. My suffering is entirely due to the tools and libraries I'm using. That, and the desire to actually test my code. If you've coded you've had days like this too, where it seems like the computer is actively obstructing your progress. It's kind of like complexity, but not really the same thing. The computer puts obstacles your the way, which cause you to waste time and write extra code. Rather than complexity its more like obstructisity.