T4 template to generate BLToolkit compliant stored procedure calls

Developing data aware applications used to be quite a pain before various helper libraries emerged. At first we used Enterprise Library from Microsoft that made things much simpler and standardized. In all the years of development it became very complex or even too complex for usual everyday projects. Anyway. Lately we started using more popular OR/M tools and libraries that go even further and provide a really transparent abstraction layer between our logic and the actual data store (whichever it may be).

Choosing DAL layer technologies isn't always straight forward. You have to choose wisely and think of the scenarios you'll use in your application. The same thing happened to me this last time when I was at the same decision point. Yet again.

I've used Entity Framework on two past projects (using EF Extensions along with it to make it simpler to integrate custom occasional stored procedures). Contrary to popular belief I was quite pleased with it even though we had to resort to not so seldom hacks to make it snappy. Usually provided by stored procedure calls and result materialization. Nonetheless it worked as expected. We still didn't have to write any entity classes, connection lifetime was auto-managed in entity context as well. Entity Framework FTW so to speak. On the downside I should as well mention that speed is not its strength. You use SQL Profiler quite often to analyse those all but easy to grasp spaghetti T-SQL queries. But things have greatly improved after EF4 was released. Visual Studio integration was of course one of its best strengths that gives you the all familiar drag and click UI without writing XML files. Even though it wasn't fully working in version 1.

Custom complex stored procedures aren't OR/M friendly

But this time I knew this application will be much heavier on the database side with much more complex processes and larger amounts of data. All these will most probably be better processed by stored procedures in the database itself. So I ruled out Entity Framework completely because I would be writing lots of custom materializers and such. But what should one choose instead? Most of these days OR/M libraries don't support custom stored procedures quite well, so I had to rule out LINQ to SQL, NHibernate, Subsonic and similar as well. I'd use Fluent NHibernate anyway since I prefer syntax checked code over XML configuration.

BLToolkit library to the rescue

So I resorted to semi-biased ORMBattle.NET website that lists most popular OR/M libraries along with their measured benchmarks. My main requirements for my DAL layer library were:

• support for all kinds of custom stored procedures and
• automatic result materialization and of course
• it had to be free

After a while (and some checking) I've decided to give BLToolkit a try since it seems it provides exactly what I require. I agree it's not an actual OR/M library, but that makes it even faster. I was thinking of writing a few of my own generic calls that would do just what I need, but I had to to follow the DRY principle. Ok, BLToolkit it is then.

The good thing is that BLToolkit is fast and provides data materialization. But the bad thing is that you may end up with lots of magic strings. This is where T4 comes into play. I wanted to avoid writing stored procedures' names and parameters using plain strings, because that's very prone to human errors. Not to mention tedious work and updates when something gets changed on the DB side.

Database side

To make things more generic and structured I had to stick to certain syntax rules on the database side. I didn't want to end up with a single class that has as many methods as there are stored procedures in the database. Certain stored procedures are related to certain common tables, that are usually reflected in some middle layer entity class. For instance if we have a table Person we will have several stored procedures that will manipulate data around this table and related ones as well. But basically these stored procedures will be somehow related to the Person database table. We usually name stored procedures that way. At least most of the time:

• Person_GetAll
• Person_SaveRelatedData
• etc.

So I shall make this a rule. Part before underscore is related to class name, part afterwards will provide method name. Perfect.

The usual stored procedure call in BLToolkit

If you haven't used BLToolkit before, let me get you up to speed and show you some code, that makes it easy to understand how to execute a parametrised stored procedure call.

   1:  using (var db = new DbManager())

   2:  {

   3:      return db

   4:          .SetSpCommand(

   5:              "Person_SaveWithRelations",

   6:              db.Parameter("@Name", name),

   7:              db.Parameter("@Email", email),

   8:              db.Parameter("@Birth", birth),

   9:              db.Parameter("@ExternalID", exId))

  10:          .ExecuteObject<Person>();

  11:  }

This represents some code within data repository. Stored procedure populates certain tables (most notably Person table) and then executes a SELECT statement as well to return the newly created record (with relations if necessary).

As you may see, this is a very easy and comfortable way of calling stored procedures (can easily call regular queries as well) and having results materialized along the way. But. The problem lies right there in front of our eyes. Magic strings.

What if we make a typo?
What if we slightly rename a stored procedure?
What if we add a few others?
What if we rename a parameter or its type?
But the main one is what if we're part of a larger team where each developer's writing a separate part of application and not everyone is tedious or/and communicative?

The answer to all these questions is use automation. So let's automate generation of our stored procedure calls with strong typed parameters. The most obvious way of doing this is by using Text Transformation Template Toolkit or in short T4 because it's very well integrated into Visual Studio and works really well. If we use extensions like Tangible T4 editor we get nice code highlighting and intellisense support along the way as well.

Stored procedure call after using T4 template

What we'd like to achieve in the end is to get previous code to be called this way:

   1:  using (var db = new DataManager())

   2:  {

   3:      return db

   4:          .Person

   5:          .SaveWithRelations(

   6:              name,

   7:              email,

   8:              birth,

   9:              exId

  10:          )

  11:          .ExecuteObject<Person>();

  12:  }

So we can see there're no magic strings any more and we get code intellisense as well, so we don't have to check store procedure names or check parameter names either. The best part is that when anyone else changes parameters or renames a stored procedure we get compile time errors. Code generation FTW!

Gimme gimme gimme code

You can download T4 template from Google code site. There's a comment header at the top of the template where you'll find all instructions how to use the template. For now all stored procedures are treated the same. These will probably be next extensions to this template:

• Support for output parameters
• Handling multi result set stored procedures - there will have to be some naming convention to distinguish these and template will either have to provide some sort of support or skip generation
• etc.

I'm attaching template comment head here so you can provide some input whether it's readable (just add a comment to this post):

   1:  /* BLToolkit stored procedure calls T4 generator          */

   2:  /* ------------------------------------------------------ */

   3:  /* 1. Fill in variables below this comment head           */

   4:  /* 2. Stored procedures must use this notation:           */

   5:  /*    ie. "Person_SaveWithRelations"                      */

   6:  /*    which will generate:                                */

   7:  /*    - a class PersonClass                               */

   8:  /*    - a property DataManager.Person of type PersonClass */

   9:  /*    - an instance method Person.SaveWithRelations()     */

  10:  /*    - method will have strong typed parameters          */

  11:  /* 3. Save template and use code                          */

  12:   

  13:  // set your preferred DbManager inherited class name (ie. class DataManager: DbManager { ... })

  14:  string className = "DataManager";

  15:  // provide namespace of your class

  16:  string useNamespace = "ApplicationName.Data.General";

  17:  // provide DB connection name (from .config file) that will be used by your DB manager

  18:  string dbConnectionName = "DefaultDatabaseConnection";

  19:   

  20:  // provide DB connection string that will be used by this generator

  21:  string connection = "Data Source=.;Initial Catalog=somedb;User ID=someuser;Password=somepassword";

  22:   

  23:  /* That's it. Save this file now. */

Provide some feedback

If you have any additional ideas how to enhance or change this template I'm willing to consider your ideas. You can easily provide code snippets that would make it even better. Anyway. I hope you'll use it and that you'll like it.