Announcing SimpleScriptRunner - a very basic migrations tool

Just thought I'd share a very basic tool called SimpleScriptRunner. Its basically just something for running a directory of numbered SQL scripts against a SQL Server database for migrations, using the syntax

SimpleScriptRunner.exe <server> <database> <path to sql scripts>

I like to call it from a post build event in a VS project like so:

SimpleScriptRunner .\sqlexpress $(SolutionName)Db $(ProjectPath) 

If the database doesn't already exist, it will create it. The scripts are run in numerical order (you need to call them something like 0001 - First script.sql), and any scripts that have been already executed against the database are skipped*. 

I had been using MigratorDotNet for my migrations, and its probably a better tool as it can target databases other than SQL Server, but if you're using a lot of MS SQL Server, you might find this handy.

You can find it here on google code.

*actually if you modify the most recent script, it will re-execute it. This is a good thing when you are developing.

LinqtoDBMLRunner improvements

I've made a couple of improvements. Generally you want to make a couple of changes to an element in the xml file once you've selected it. The Apply extension method allows you to chain changes together so you don't need to keep reselecting objects e.g.

root.Table("People")
    .Apply(table => table.Member = "Persons")
    .Apply(table => table.Type
                        .Apply(type => type.Name = "Person")
    );

The next improvement is a method to automatically recognise single table inheritance from the database, if, of course, you stick to the convention.

Image you have a table defined with the following SQL. The underscores in the column names are used to indicate that the column should belong to a subclass.

CREATE TABLE [dbo].[Device](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DeviceType] [int] NOT NULL,
    [Mouse_NumberOfButtons] [int] NULL,
    [Keyboard_NumberOfKeys] [int] NULL,
    [Keyboard_WirelessKeyboard_Frequency] [decimal](19, 5) NULL,
)

You can then add the line below to your DbmlRunner script

root.ExtrapolateSubclasses();

This will rearrange your Dbml mappings to give the correct inheritance, as in the diagram below

This lets you very quickly and easily add new subclasses without having to do any real dbml scripting. A word of warning. It generates the inheritance modifier value for the subclass based on the order the columns appear in the system. If you remove a subclass and its columns once you have data in the system, you'll probably want to manually script the inheritance modifier values yourself. I might make it run off a named enum in the future, theres an idea...

Try-catching in a single line of code

Do you like reducing your line count at the expense of readability to others? Then you'll like this!

public static class FuncExtensions
{
    public static TResult Catch<TExc, TResult>(this Func<TResult> func, Func<TExc, TResult> handleException) where TExc : Exception
    {
        try
        {
            return func();
        }
        catch (TExc ex)
        {
            return handleException(ex);
        }
    }
}

This lets you write things like:

int x = new Func<int>(DoSomething).Catch((NullReferenceException e) => -1);

instead of

int y;
try
{
    y = DoSomething();
}
catch (NullReferenceException ex)
{
    y = -1;
}

You can of course write overloads for Funcs that take more than one parameter.

Edit: I've written a version that lets you chain catches but I'm not sure if its better or not...

int x = new Func<int>(DoSomething)
    .Catch((NullReferenceException e) => -1)
    .Catch((NotImplementedException ex) => -2)();

storing ViewState in memcached - the ultimate post

The HttpModule for storing memcached in the ViewState is dead - I discovered that you can far more easily intercept ViewState using a custom PageAdapter. Please go to http://code.google.com/p/memcached-viewstate/ for frictionless memcached viewstate goodness! Hopefully this is the end of the matter :)

Introducing LinqToDbmlRunner

I've created a tool based on my previous post. This should allow scripting of dbml changes in a team environment.

The project is hosted at http://code.google.com/p/linqtodbmlrunner/ Enjoy!

Scripting changes to Linq To SQL DBML files

I've been playing with Linq To SQL and have been getting very bothered by the method MS expect us to use to develop the mappings. You can't regenerate your dbml schema without overwriting the changes you made in the designer. Not only this, but your changes won't merge well with changes made by other developers on your team when it comes to check-in time as they are all in xml files.

The process that is required for safe, team development that I've come up with is this:

1. Update database schema (using something like migratordotnet)
2. Generate just the dbml file from schema using sqlmetal.exe
3. Run a change script of some kind against the dbml file to update it to include your custom mappings.
4. Generate the classes from the dbml file using sqlmetal.exe

Step 3 is the missing link. You could use xslt, xpath or Linq to XML. You could even generate change scripts using the designer and MS XML Diff. But the easiest thing to use is a Linq To XSD program and put it in the post build events for a project that contains the generated XSD file as an embedded resource. Linq To XSD gives you typed control over the dbml file in C#.

This lets you write something like this in your script:

var dbml = XRoot.Load(@"..\..\GdwDb.dbml");
foreach (var table in dbml.Database.Table)
{
foreach (var column in table.Type.Column.Where(c => Char.IsLower(c.Name[0]))
{
column.Name = column.Name.ToUpper()[0] + column.Name.Substring(1);
}
}
dbml.Save(@"..\..\GdwDb.dbml");

This snippet will update all camelCase field names to PascalCase ones. Of course you can do anything, like creating inheritance mappings, removing prefixes etc. One you write a couple of helper methods you should be away.

I would have thought a similar process could be used for NHibernate and Entity Framework, although I know some people prefer to work backwards from code to database.

You can download the latest version of Linq To XSD from here and download the Linq classes I generated for the Dbml xsd here. Include that in a project with a reference to Microsoft.Xml.Schema.Linq.DLL and you can create your own mapping update script.

Info on using SqlMetal to generate the dbml file, and the appropriate classes can be found on Ben Hall's blog here.

I'll upload a demo project later, as this could all be a little confusing.

Storing ViewState in memcached part 2

If you have access to the source code of the web app, you can also create inherit from Page and add the following two methods. I prefer the HttpModule myself, but this is pretty convenient. You'll still need to set up the memcached settings in web.config

protected override void SavePageStateToPersistenceMedium(object state)
{
var key = Guid.NewGuid().ToString();
BeIT.MemCached.MemcachedClient.GetInstance("ViewStateCache").Add(key, state);
base.SavePageStateToPersistenceMedium(new Pair("Key", key));
}
protected override object LoadPageStateFromPersistenceMedium()
{
var pair = (Pair)base.LoadPageStateFromPersistenceMedium();
return BeIT.MemCached.MemcachedClient.GetInstance("ViewStateCache").Add(key, pair.Second);
}