Mapping SQL stored procedures to C# class methods with matching strong type parameters using T4 template

Blog post provides T4 template that generated strong typed C# code which maps to SQL stored procedures. It doesn't actually call those SPs, but rather prepares SQL queries to call stored procedures with various parameters. These provided T4 template queries are NPoco/PetaPoco compatible, so if you use a different DAL lib (maybe even simple .net Sql client), you'll have to adjust methods to generate and return different results (with .net Sql client it will likely be SqlCommand instance).

NPoco/PetaPoco DAL libraries use strings to provide database optimised queries and these are prone to typing errors which we aren't able to catch during compile time but rather during runtime, when effects can be much more devastating. That's why it would be great if we could somehow avoid these magic string values and just used C# code with string types and not make a mistake. We can't really avoid magic strings when writing direct TSQL but if we write stored procedures, we can do something about it.

We could self-write those C# calls that use stored procedures internally but that would just mean we moved typing errors to those methods, so this is not really a solution. Enter code generation with T4 Visual Studio templates. A t4 template will help us automate the process of writing C# methods while also making sure we get compile time errors when we change particular stored procedure's signature (sp name, parameter count or their types). So let's do just that.

How I mitigated the impossible SELECT INTO on Windows Azure

I'm the kind of developer that likes things under control by writing some stuff myself and keeping it as optimized as possible. This is especially true for database scripts. Sure we have these ORM tools these days (Entity Framework, NHibernate and such) but the calls that end up on the database side are many times unoptimized. Why? They have to work for all cases so sacrifices are made and performance usually suffers. That's why I like micro ORMs that make it super simple to translate database data to code (via object mapers) but still let you write optimized queries suited completely to your database.

The way that I upgrade my database requires me to backup existing data, drop the whole model, recreate it and restore data back to appropriate tables. Especially the backup strategy is problematic as it uses select into statements. Windows Azure requires all tables to have clustered indices prior to inserting data which makes it impossible to use select into statement as it has no control over clustered indices. How can we then automate backup scripts without creating those backup tables manually? With a bit of work, everything can be done.