I recently read a blog post titled Automating Database Script Execution by Thomas Weller.  I agree with many of his points concerning databases.  Most applications today are basically useless without a database.  That said, there is nothing worse than working with a project where the database is a nightmare to deal with.  You spend a lot of wasted time just trying to get to the point where can actually start developing and doing actual work.

As a developer you absolutely must be able to install a local version of the database with relative ease.  If you cannot do that you are dead in the water.

With that said, I always have two simple rules for projects that I work on:

1) Database creation scripts must be kept under source control

2) You must be able to install the database with a single click (OK, maybe a double click)

The need for the database to be installed with a single click is vital – especially for new project development where the data model is changing frequently and closely mimics the domain model.  It is also crucial for automated integration tests.  Continuous build tools must be able to install a fresh version of the database before kicking off the integration tests.

The script order for installing the database should always be as follows:

1) Create tables

2) Create foreign keys

3) Create integrity constraints

4) Create functions, views, and stored procedures

5) Insert static data (for lookup tables, etc)

6) Insert test data

The above order is very important.  I was recently on a project where the static data was installed before the foreign keys were created.  When I asked the owner of such logic why he was doing it that way he said, “Because that way I know that the data won’t violate the foreign keys.”   Sure enough, when I switched the order of the scripts I was bombarded with a plethora of foreign key violations.  Stuff like that makes me want to vomit. 

The best part of all is that it is easy to do.  My preferred approach uses MSBuild.   The script for performing the database install is simply:

<Project ToolsVersion="3.5" DefaultTargets="InstallDatabases" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

	<UsingTask AssemblyFile="$(CommonLib)\Build.Tasks.dll" TaskName="Build.Tasks.DatabaseBulkInstall" />
	
	<Target Name="InstallDatabases">
		
		<DatabaseBulkInstall 		
			Directory="$(MSBuildProjectDirectory)" 
			ConnectionStringFilePath="$(ConnectionStringFilePath)" />
	
	</Target>

</Project>

This script can then be installed via a .bat file that contains the following command line:

"%windir%\Microsoft.NET\Framework\v3.5\MSBuild.exe" Install.proj /target:InstallDatabases
pause

The “DatabaseBulkInstall” tasks is really quite straightforward as well.  It iterates through the current directory and assumes that any immediate child directories contain database installation scripts (.sql files) and that the name of the directory is the name of the database.  This actually allows us to install many databases with a single click.  The task itself simply uses SqlCommands to execute the scripts.

One important note is that it completely (and forcefully) drops and recreates the database before running the scripts.  The code for doing that is:

StringBuilder sql = new StringBuilder();

sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = '{0}'");
sql.AppendLine("USE [master]");
sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') ALTER DATABASE [{0}] SET SINGLE_USER ");
sql.AppendLine("USE [master]");
sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') DROP DATABASE [{0}]");

sql.AppendLine("CREATE DATABASE [{0}]");

Execute(connection, string.Format(sql.ToString(), DatabaseName));

The fact that the above script forcefully drops the database allows us to avoid having to deal with any linger database connections that would normally prevent us from dropping the database.  After all, we are only allowed one click.

The situation is simple – you want to send a message to a remote MSMQ. To do so you might write some code that looks something like:

using (var queue = new MessageQueue(@"FormatName:Direct=TCP:192.168.2.157\Private$\TheQueue"))
{	
	var message = new Message(shipment);
	message.Formatter = new BinaryMessageFormatter();
	queue.Send(message, MessageQueueTransactionType.Single);	
}

This code works fine... most of the time. The problem is that, for remote queues, MSMQ sends messages in a "fire and forget" type of mode by default, i.e. if the message gets there, great! If not, oh damn well. This was not apparent to me at first. In workflow situations where you cannot let things "slip through the cracks" it is critical that you receive acknowledgement of delivery. For example, consider the following pseudo-code:

using (var transaction = new TransactionScope())
{
    // Perform numerous database operations here

    // Send message to remote queue here

    transaction.Complete();
}

What if the send operation to the remote queue does not succeed? Well, in a workflow with state tracking, we absolutely must rollback all of the database operations and leave the system in the exact same state that existed before we attempted the send operation. The only way we can do this is to know whether the message arrived in the remote queue. Thankfully MSMQ provides a fairly easy way to accomplish this through the use of an Administrative Queue. The purpose of this queue is to provide a "callback" location for the target queue to send an acknowledgement message. This message is uniquely identified by its Message ID property. The high-level steps for guaranteed delivery are:

1) Create a message and define an Administrative Queue 
2) Send the message 
3) Wait a little 
4) Check the Administrative Queue for the message's unique ID

The code to do this is:

using (var queue = new MessageQueue(@"FormatName:Direct=TCP:192.168.2.157\Private$\TheQueue"))
{
	var message = new Message(shipment);
	message.Formatter = new BinaryMessageFormatter();
	message.AdministrationQueue = new MessageQueue(@"FormatName:Direct=TCP:192.168.2.148\Private$\AdminQueue");
	message.AcknowledgeType = AcknowledgeTypes.PositiveArrival;
	queue.Send(message, MessageQueueTransactionType.Single);

	Thread.Sleep(100);

	bool acknowledged = ReceiveAcknowledgment(message.Id, @".\Private$\AdminQueue");

	if (!acknowledged)
	{
	   throw new InvalidOperationException("Acknowledgement was not received");
	}
}

Yes, the above code uses a Thread.Sleep call. I realize that this is somewhat of an anti-pattern but it is called for here as you have no way of gauging network latency.  The best you can really do is supply a sleep time that is relative to your queue architecture, i.e. if your queues are on an Intranet then you can probably get away with 100 ms (or less).  If you’re using queues over the Internet then you will probably need a longer wait time.  This is the price you pay for guaranteed delivery acknowledgement – a small one in my opinion.

The "ReceiveAcknowledgment" helper method is defined as follows:

private static bool ReceiveAcknowledgment(string messageId, string queuePath)
{
    var queue = new MessageQueue(queuePath);
    queue.MessageReadPropertyFilter.CorrelationId = true;
    queue.MessageReadPropertyFilter.Acknowledgment = true;

    while (queue.PeekByCorrelationId(messageId) != null)
	{
		Message message = queue.ReceiveByCorrelationId(messageId);
		return true;
	}

    return false;
}

Like most developers, I often find myself dealing with code that references DateTime.Now. For example consider this code that retrieves items relative to the current date/time:

var command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetItems";
command.Parameters.Add(new SqlParameter("TargetDate", DateTime.Now));

It would be hard to develop an integration test for this code since it references DateTime.Now. What if I wanted to test the scenario where items with a future date/time value needed to be retrieved, i.e. how will the system behave six months from now? A simple, but very effective, way to deal with this is to add the following class:

public class CoreDateTime
{
    static CoreDateTime()
    {
        Executor = () => DateTime.Now;
    }

    internal static Func Executor
    {
        get;
        set;
    }

    public static DateTime Now
    {
        get
        {
            return Executor();
        }
    }
}

The key thing to note here is that the "Executor" property is simply a Func that wraps a call to the normal DateTime.Now. This means that CoreDateTime.Now will behave exactly like DateTime.Now.

You will also notice that the "Executor" property uses the internal access modifier. This allows testing assemblies to inject a Func of their choosing. You can expose this property to test assemblies using the System.Runtime.CompilerServices.InternalsVisibleTo assembly attribute and then update it with code like:

CoreDateTime.Executor = () => DateTime.Parse("5/10/2013 10:15 AM");

You can also have successive calls to CoreDateTime.Now be evaluated against a fixed point in time. This allows you to easily jump to any point of time in the past or future. This code might look like:

var init = DateTime.Now;
CoreDateTime.Executor = () => dateTimePicker1.Value.AddSeconds((DateTime.Now - init).TotalSeconds);                

Happy time travels!

Unit Testing ASP.NET? ASP.NET unit testing has never been this easy.

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch will be giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator, a unit test tool and Ivonna, the Isolator add-on for ASP.NET unit testing, for a bargain price.

Typemock Isolator is a leading .NET unit testing tool (C# and VB.NET) for many ‘hard to test’ technologies such as SharePoint, ASP.NET, MVC, WCF, WPF, Silverlight and more. Note that for unit testing Silverlight there is an open source Isolator add-on called SilverUnit.

The first 60 bloggers who will blog this text in their blog and tell us about it, will get a Free Isolator ASP.NET Bundle license (Typemock Isolator + Ivonna). If you post this in an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

Also 8 bloggers will get an additional 2 licenses (each) to give away to their readers / friends.

Go ahead, click the following link for more information on how to get your free license.

Managing connection strings, app settings, log4net settings, SMTP settings, etc between developers and environments can be a major pain in the ass.  I’ve used numerous approaches for this in the past but none of them completely satisfied me.  That has, finally, come to an end.

The approach I have laid out here will allow developers to have their own personal settings and not have to worry about modifying any .config files each time they perform an update from source control.   This approach will also allow you to easily manage your environment specific settings via your Build server.

Step 1 – Environmental Variable

Each developer must create an environmental variable that points to the root for their local working directory.

Step 2 – Project Config Directory

Create a Config directory under the development directory’s root.  Within this Config directory each developer should have a subdirectory for his/her machine name.  So, for example:

- Local working directory for project: D:\Projects\SomeProject\

- Environment variable named MyRoot maps to D:\Projects\SomeProject\

- Project config path: D:\Projects\SomeProject\Config\

- Personal config path: D:\Projects\SomeProject\Config\BEAU-PC\

- The above path for my personal configuration should then be resolvable via: %MyRoot%\Config\BEAU-PC\

Step 3 – Project .targets File

This .targets file can contain the properties, targets, etc that are common to all projects.  You will need, at least, the below target which will copy the .config files found in your personal config path to a directory named “Config” in the output assembly’s bin directory:

   
   <Target Name="AfterBuild" Condition="Exists('$(MyRoot)\Config\$(ComputerName)\')">
	
	<ItemGroup>
		<ConfigFiles Include="$(MyRoot)\Config\$(ComputerName)\*.config" />
	</ItemGroup>
   
	<Copy SourceFiles="@(ConfigFiles)" DestinationFolder="$(TargetDir)\Config\" />
		
   </Target>

Note: the above target must be placed inside the normal MSBuild project structure.

Step 4 – Modify Projects

You must now modify all .csproj (or .vbproj) files so that they import the above .targets file.  This is as simple as opening the project file in a text editor and finding the Microsoft.CSharp.targets import and placing your import directly after it (if it’s not after it then it will not fire, as the “AfterBuild” target logic is defined in the Microsoft.CSharp.targets file).

<Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
<Import Project="$(MyRoot)\SomeProject.targets" />

Step 5 – Application Configuration

Within your app.config (or web.config) any machine/environment specific settings can now be stored in separate .config files found in the machine specific directory mentioned above.  You can then use the configSource attribute to reference these settings.  For example:

  <appSettings configSource="Config\AppSettings.config" />
  <connectionStrings configSource="Config\ConnectionStrings.config" />

You should note that, for web applications, the configSource paths are evaluated relative to the web root and not the bin directory.  This means your configSource paths should look like:

bin\Config\ConnectionStrings.config

Hopefully this makes your life easier.