From Jeff Palermo in the comment section of Hammet's blog post:

I'm, at this point, very concerned that the average Microsoft developer will use ASP.NET MVC not to improve their application design, but merely because it's the new thing. I can see it now: before there was 1000 lines of code in Page_Load. Now there is 1000 lines of code in the controller action.

I found myself doing this today with Rhino Mocks:

[TearDown]

public void TearDown()

{

    mocks.ReplayAll();  // ensure replay was called

    mocks.VerifyAll();

}

I have a few tests in my fixture that don't use mocks, so forcing those tests to have mocks.ReplayAll() in them is just extra noise.  As it turns out calling Replay multiple times is safe, so tests that use mocks of course have already called ReplayAll, but tests that don't use the mocks can forgo having to call ReplayAll() just to have the tear down method run without error.

In case you didn't know, calling VerifyAll in RhinoMocks will throw an exception if you have previously called Replay.

I'm just finishing up building my first deployed WCF service, and over the past couple of months I've learned a lot about WCF - what works and what doesn't from a practical standpoint.  Unfortunately there are still a few areas in which I'm fuzzy.

I was originally treating my WCF proxies like ASP.NET 2.0 web service proxies.  Create a proxy and then let the GC handle it.  This was really useful because I was just injecting the service instance into each of my MonoRail controllers using Windsor.  Testing was nice and easy, and things looked good.

Until last week... I discovered a nasty bug in my web application.  The bug was, that under load, the WCF service would stop responding and timeout.  Come to find out that it's not only recommended to close your client side proxies, but required (at least when using Net TCP binding).

This turned my beautiful code with its inverted dependencies into a mess.  I had to start asking the container on every call for a new proxy since it was getting closed after every remote call.

IMyService svc = IoC.Resolve<IMyService>();
svc.SomeRemoteCall();
svc.close();

I had to do it this way because the remote calls were wrapped in a local service helper object that didn't have any idea about the unit of work, i.e. where the best place to put the Close() call.

When I have some time this week I plan on building a WCF proxy helper that will create a proxy unit of work on a per HTTP request basis.  When the HTTP request comes in, the proxy is created, and when the HTTP request finishes the helper will call Close() on the proxy.  This should allow me to remove the ugly static IoC dependency and use Windsor to inject the proxy instance.

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.

I thought this looked like an interesting approach to validating your SQL statements in a continuous integration environment.  Basically you execute the script with the NOEXEC option.

I'm not sure I would actually us it though since I think I would prefer to actually run the scripts and and then do some further processing, however maybe this is a good prebuild step?

I'm working on creating a SQL migration tool to help automate some of the drudgery in moving and upgrading databases.  Initially I'm only supporting SQL Server 2000 since this is what I primarily use.

The SQL scripts are being generated from the SQL entities (sort of like a generic SMO) via NVelocity templates.  I think the NVelocity approach is working very well because the C# code only needs to populate the top most object, like Table or Column, and the template can walk the hierarchy db objects as needed.

Right now I have Create/Drop table, Add/Drop/Alter column, Create/Drop index, Create/Drop foreign key complete.  This should be enough to modify a table for the most common scenarios.  Currently the committed code only has SQL script generation via the scripting API, so there's no intelligence currently backing it in regards to migration.  It only will write SQL scripts at this point.

The one thing I'm making sure to do, is to bulletproof the generated scripts so they can safely be run multiple times without error.  Here's an example template which safely adds a column to a table.

/****** Object:  Column [$column.Table.Schema].[$column.Table.Name].[$column.Name] $time ******/

IF NOT EXISTS
(
  SELECT * FROM [information_schema].[columns]
  WHERE table_name = '$column.Table.Name'
  AND table_schema = '$column.Table.Schema'
  AND column_name = '$column.Name'
)
BEGIN
  ALTER TABLE [$column.Table.Schema].[$column.Table.Name]
  ADD $SqlScriptHelper.WriteColumn($column)
END
GO

I chose to use NVelocity templates because of their flexibility and the ease of use for generating text documents.  NVelocity is also faster for script generation then SMO (and it actually works unlike SMO), and it gives me the flexibility to do things the non-SQL Server way which sometimes is inappropriate for production systems (change a column's nullability and you'll see what I mean).  One more benefit, is it allows me to customize the output just by modifying the template, no recompile necessary.

Keeping my SQL generation separate from SQL Server will also allow me to re-use this migration tool for other databases like MySql.  It could even potentially allow me to export scripts from SQL Server to MySql syntax since I have an abstraction layer over the database like an ORM would.

What would the minimum set of features be to create a SQL Schema Migration tool?

Add FK Contraints
Remove FK Contraints

Add Non-Clustered Indexes
Remove Non-Clustered Indexes

Add Tables
Remove Tables

Add Columns
Remove Columns

Add Sprocs
Remove Sprocs
Alter Sprocs

Add Views
Remove Views
Alter Views

LHS is a dependency of the RHS

Add dependency order (removal dependency order is the opposite):
Tables -> Columns -> FKs -> Views -> Sprocs

Are there other common scenarios I'm not think about?  Like changing the nullability of a column or adding/removing a default?