In my spare time I've been working on a utility for generating SQL scripts and XML data documents.  The utility currently only supports SQL Server 2000/2005, but could easily support any other database that has an OLEDB provider via the MyMeta library.  The main goal of this tool was to produce scripts to be checked into source control, and to generate differential scripts between databases for use in db upgrades (migrations).

First and foremost, if you have a migration tool ala RoR ActiveRecord in your environment - use it.  A migrator written in a DSL is going to be much quicker to get going... and since you're working at a higher abstraction level it allows you to target many different databases with the same DSL code.  There are a few out there, so take a look.

My first goal was to get a baseline script of our databases.  There are quite a few tools out there for this, but I really wanted the ability to tweak the output of my scripts just so.  I really like scripts that don't error when you run them multiple times, so this generally requires an IF EXISTS before any action.  To get things formatted and written the way I wanted them, I ended up using NVelocity along with a few templates.

Secondly, I wanted to use XML to store my data.  I like XML files better than SQL scripts for several reasons:

1. XML files are easier to edit than a SQL script with a bunch of insert statements.
2. Its faster to bulk load XML data then run a bunch of SQL inserts (using SQLXmlBulkLoad).
3. I can edit the XML data in a tabular fashion inside Visual Studio by switching to DataGrid view.
4. Its easier to produce a diff between a table and an XML document than a SQL script and a table.
5. XML is meant to transfer data between systems, so why not use it?

To load XML data into SQL Server I created a wrapper around the SQLXmlBulkLoad COM component.  It works good enough for what I need, however it does make some assumptions about the way things are structured, its not quite as general use as BulkXml. 

The one advantage my implementation has is that when you run the loader, it generates the XSD that the SQL Server bulk load component requires at runtime based off the target table, so there's no need for you to hand write an XSD or worry about checking it into source control.

Here's how you would use SqlMigration to transfer data from one db to another (assuming the target db table is empty):

This creates an XML file for all the data in the customer table on my local machine:
sneal.sqlmigration.console /dir=c:\mydb /xmldata /tables=Customer /server=localhost /db=AdventureWorks

This applies the XML file just generated to my prodsql1 SQL Server.
sneal.sqlmigration.console /server=prodsql1 /db=AdventureWorks /execute=c:\mydb\data\dbo.Customer.xml

Pretty straight forward.  I'm not sure I like the command line syntax since you could potentially run an execute and script command at the same time.  I'm thinking of creating a separate console application for loading data and running scripts, or at least providing a command line switch that sets the mode to either input or output only.

I would like an MSBuild target wrapped around this, but I haven't needed that yet.  Another thing lacking is the ability to point it at a database and say "script everything."  That feature I know I'm going to need here very shortly.  Hopefully I'll have some time to fix the DDL migration portion of the tool, right now only migration (diff) SQL data scripts are supported.

As always, the code can be checked out from Google code via SVN (VS 2005).  Eventually when the tool is a little more mature I'll post a binary.

After some research I discovered the MyMeta API used by MyGeneration for DB meta data is now open source under the very liberal BSD license!  That sure makes my life a lot easier since it has a much more fully featured API than I started to build.  Even better it supports multiple RDMS out of the box: SqlLite, MySql, SQL Server, Access, Oracle etc.

This means that I can programmatically walk a database definition using C# in a generic way.  The API is somewhat similar to SMO, but primarily uses OLEDB to pull: DB, table, column, view, index, and sproc definitions from the different providers.

What surprised me is that the MyMeta API has interface definitions for everything; interface definitions that have the same names and properties as the interface definitions I had already created in SQLMigration.  I only had to change the namespace using statements in a few places and add a reference to MyMeta.dll to start using it with my existing code.

I haven't fully tested this out, but assuming MyMeta works as advertised, it should cut back development time significantly.

I finally got one of the harder unit tests for my SqlMigration tool passing.  The interesting thing is that I'm using NHibernate to populate my object graph of objects that describe the database.  I'm just using standard SQL queries that hit the INFORAMTION_SCHEMA tables.  Here's one of the tests:

[Test]

public void ShouldReturnAddressForeignKeys()

{

    ITable address = session.Get<Table>("Address");

    ITable state = session.Get<Table>("StateProvince");

    Assert.IsNotNull(address, "No address table returned");

    Assert.IsNotNull(state, "No state table returned");

    Assert.IsNotNull(address.Columns, "No address columns returned");

    Assert.AreEqual("FK_Address_StateProvince_StateProvinceID",

        address.Columns[4].ForeignKey.Name, "Incorrect FK name");

    Assert.AreEqual(address.Columns[4],

        address.Columns[4].ForeignKey.ForeignKeyColumn, "ForeignKeyColumn StateProvinceID is incorrect");

    Assert.AreEqual(state.Columns[0],

        address.Columns[4].ForeignKey.PrimaryKeyColumn, "PrimaryKeyColumn StateProvinceID is incorrect");

}

Unfortunately I just realized that you can have a compound primary key so I really ought to change ForeignKey.PrimaryKeyColumn to a list ForeignKey.PrimaryKeyColumn[0]...

Using NHibernate for this kind of work probably isn't the easiest solution, but once the mappings are right NHibernate takes care of correctly populating the object graph which isn't trivial because of all the bidirectional associations.  I've definitely learned a lot about NHibernate mappings and SQL Server's INFORMATION_SCHEMA views. NHibernate really is flexible and powerful and NHibernate makes loading a specific object from the database very easy and fluent.  The only "code" I had to write were the XML mapping files besides building a SessionFactory. 

It does seem that using NHibernate to directly query SQL Server is too time consuming development wise especially when other people have already figure this stuff out. If there were am easy way to generate NHibernate mapping files at runtime for a DB I could probably use NHibernate's rich meta data model instead of, or in addition too.  Another possibility is to use MyGeneration's data model or even SMO to populate my entities.

In some ways it seems like I'm reinventing SMO, but at least I have the source code to my version of SMO and my version doesn't have a hard dependency on SQL Server even if SMO has a lot more functionality.