Twitter Gplus LinkedIn RSS
formats

Empowering Nuget

Just a quick tip I found recently although I believe this has been around since the release of Nuget 2.0 last year.

I use SVN for version control and have a Global ignore pattern configured in my SVN client (shown below if anyone is interested)

*.o *.lo *.la #*# .*.rej *.rej .*~ *~ .#* .DS_Store thumbs.db Thumbs.db *.bak *.class *.exe *.dll *.mine *.obj *.ncb *.lib *.log *.idb *.pdb *.ilk *.msi* .res *.pch *.suo *.exp *.*~ *.~* ~*.* cvs CVS .CVS .cvs release Release debug Debug ignore Ignore bin Bin obj Obj *.csproj.user *.user *.generated.cs

As you can see I don’t like my repositories being bloated with compiled assemblies unless absolutely necessary, the one downside to this is that when I install a new Nuget package and commit none of the dependencies are uploaded to the repository. Actually I don’t have a problem with this in general but I needed a way of loading those missing packages easily if I was checking out a co-workers solution or even one of my solutions on a different development machine. The answer was surprising simple – a setting in the Visual Studio Tool Options dialog “Allow NuGet to download missing packages during build”.

Allow NuGet to download missing packages during build

Tick this box and a new Nuget MSBuild task is added to the solution that downloads any missing packages (at the required version!) automatically when the solution is built.

Nuget MSBuild

Headache solved.

 
No Comments  comments 
formats

Migrator.NET – Installation and Configuration Guide

Published on May 16, 2013 by in .NET, Technical

For many years I have managed my database version outside the scope of my software version, however I recently made the decision to ensure my projects can support Continuous Integration (CI). In order to effectively achieve this the database version needs to be controlled by tightly coupling each version with the code that utilizes it and, importantly, it must be possible to manage database schema changes via an automated build task.

My tool of choice for this task is Migrator.NET, a well established port of Rails ActiveRecord Migrations.

This is my first foray into using Migrator.NET and, as simple getting started guides seem a little thin on the ground, I thought I would document the installation and setup process which may be useful to others.

First up lets get hold of the latest version of Migrator.NET. I am a big fan of NuGet however I had some issues with using the NuGet package for Migrator.NET so for this guide lets go back to first principles and download the source from GitHub. The ZIP file for the latest source can be downloaded directly from Migrator.NET Source ZIP or if that URL changes you can browse to the GitHub repository directly GitHub Migrator.NET.

Once downloaded, extract the ZIP file and you should see the following file structure:

SolutionFiles

We need to build the project using the supplied build.bat file however before we run this edit the default.build file (using VS) and remove the following statement which prevents the build task from generating the Migrator.MSBuild.dll assembly (which we need):

buildedits

Save your changes to the default.build file and then run the build.bat file.

Once the build is complete lets go and check the output in explorer, there are 4 files that we need all located in the build folder:

RequiredFiles

Copy these 4 assemblies into a support folder for your Visual Studio project that is going to be utilizing Migrator .NET

Although I want my database versions organised I do want to keep the script classes separate to the rest of my code-base so I have added a new project to my existing solution purely for managing migrations. I have suffixed the project name with ‘.Migrations’ for clarity.

Now add a reference to the Migrator.Framework.dll assembly that you copied into your support folder:

MigrationsProject

Next up we need to create the msbuild file that will be run whenever we want to apply migration files to update or downgrade the database schema. I have called this Migrations.msbuild and had a lot of trouble getting this correct. I found most of the examples on the web didn’t work or had dependency issues. The simple structure below works for me and if you use this hopefully it will save a lot of time and effort:

MigrationsMsBuildFile

There are only two elements that you need to update:

  • Make sure that the Assembly path to Migrator.MSBuild.dll (relative to your project directory) is correct
  • Copy your database connection string from your web.config (or wherever you have this stored) and paste it into the ConnectionString attribute of the Migrate element.

The above template can be downloaded here Migrations.msbuild (just remove the .txt extension and add to your project).

OK, nearly there, to make the whole process a little easier we are going to add a couple of External Tool options to Visual Studio, one to update the database to the latest schema version and one to update the database to a specific schema version. These should cover the main day to day use cases for Migrator.NET.

First up the option to “Migrate Up” to the latest version:

Within VS go to the Tools Menu and then click External Tools…

Tools

Click Add and enter the following information in the dialog:

  • Title: “Migrate Up” (or whatever you want)
  • Command: Path to your MSBuild.exe file (mine is located “C:\Windows\Microsoft.NET\Framework64\v4.0.30319\MSBuild.exe”)
  • Argument: $(ProjectFileName) /target:Migrate
  • Initial Directory: $(ProjectDir)

Note: Make sure you tick the “Use Output window” option

The dialog should look like this:

MigrateUp

Click apply when done and then Add again to add the option to Migrate to a specific Schema version, enter the following information in the dialog:

  • Title: “Migrate To” (or whatever you want)
  • Command: Path to your MSBuild.exe file (mine is located “C:\Windows\Microsoft.NET\Framework64\v4.0.30319\MSBuild.exe”)
  • Argument: $(ProjectFileName) /target:Migrate /p:To=-1
  • Initial Directory: $(ProjectDir)

Make sure you tick the “Use Output window” and the “Prompt for arguments” option – this will allow us to specify the value of “To” that gets passed into the MSBuild command line.

The dialog should look like this:

MigrateTo

Click Apply or OK when done, the two new menu options should appear in your Tools menu like so:

ToolsMenu

The last thing left to do is to create a test migration class that will allow is us to check that everything is hooked up properly. I am not going to go into the details of the syntax of Migrator.NET (that may be a separate topic in the future) so for now lets add a class to a Migrations Project called AddTableTest.cs

Decorate the class with the Migration attribute (you will need to add a using statement to include the Migrator.Framework namespace) and specify a version number. After talking with several colleagues and looking on the web a good approach here is to apply a time-stamped version number rather than using an arbitrary number sequence. This approach minimizes version number collisions with other team members working on the same project and also Migrator.NET will always apply any migrations strictly in the order they were created. The format I have chosen in is yyyyMMddHHmmss.

The class also needs to inherit from the Migration base class (also included in the Migration.Framework assembly).

Each migration script contains an Up and a Down method that tells Migrator.NET what to execute against the target database to move up to the current version specified in the attribute and to move down to the previous version (i.e. to undo the changes in the Up method).

As we have inherited from the Migration class these methods are available by overriding the base methods. I will add a simple statement to add a new table to the database in the Up method and remove it in the Down method. The class should look like this:

MigrationScript

Now all that’s left is to run our “Migrate Up” menu option and see what happens, it is a good idea to make sure you can see the output window when you try this as it will detail exactly what is being executed:

MigrationsTest

As you can see because this is the first time Migrator.NET has been run against our target database it knows that there are no existing Migrations applied (we will see how it knows shortly). You can also see the exact SQL that has been executed against our database which is handy when debugging any script issues.

Looking at the SQL more closely you will see reference to a SchemaInfo table. This is a new table that Migrator.NET creates in your target database to track its current schema version. Querying that table now yields the following results:

SchemaInfo

Of course one other thing to check is that our Test table was created:

NewTable

Success!

Now lets try a downgrade, click the “Migrate To” menu option, this time you will be prompted for a version number lets enter zero which takes the database back to its original state:

MigrateToTest

Click OK and then check the output window to see what happens:

OutputMigrateTo

We can clearly see the SQL being executed to remove the table and update the SchemaInfo table back to version zero.

That’s it! – Hopefully this guide was of some use and will allow you to easily start tracking your schema changes using Migrator.NET

 
No Comments  comments 
© Create Software Ltd 2013