Google analytics script

Latest jQuery CDN with code tiggling.

Wednesday, 3 November 2010

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                          */
  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";
  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";
  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.


  1. I found your article very interested, can you tell me did you work with mvc and bltoolkit together?
    I am planning to try it but i don't know where to start, i am using EF and i am very satisfied.
    Is it worthed to try BLtoolkit?
    Do you have some tutorial or documentation to point me?

  2. @goran:Yes I'm using BLToolkit on my current project that's done completely using MVC and is integrated on Sharepoint 2010.

    I used EF on two previous projects but I was rather disappointed by the hacks I had used in my code to make it perform at least to some degree better. I needed much faster DAL layer than EF provides. Anyway. I gave BLToolkit a try since it's more to the metal and performs really well.

    But it's a rather complex library that can do lots of things but lacks documentation so using it is a real PITA. I know I'm never going to use it again. I may use EF in the future, but BLToolkit no.

    I'm more inclined in using light ORM-ish tools like Dapper, Massive or PetaPoco. Especially the latter is the one I'm likely to use in the future. I'll probably integrate my DB logic using T4 as I did here, to make it more usable than writing TSQL queries by hand and to have intellisense and compile-time checking. Because it turns out that most of the time I just need a fast wrapper with a fast and capable object mapper. PetaPoco provides just that. Dapper for instance is used on since it's also written by them.

    Unless you speak Russian I'd rather talk you out of BLToolkit. Try making a pilot project and use BLToolkit on it and one of the three I mentioned earlier. Then decide which one provides what you could use.

    But otherwise BLToolkit is fast (even though the other tree are most likely faster). Because it's not really an ORM tool either. :)

  3. Thanks for such a fast response and for your advices!
    I must say i have never heard for Dapper, Massive or PetaPoco, in my first mvc projects i usualy created db so i use db first aproach and it wasn' so large DB.
    Now after college i get real job and "real" database so i have to choose my tool to create usefull application.
    I have seen EF has DatabaseContext generator, and that is something i can use, but as you say I attend to integrate some t4 for my own purposes.
    I am currently reading and finding out more about t4, so any good tip here is more than welcome :)

    Also you sad that you integrate mvc in sharepoint, sharepoint is a mistery to me so if i can ask what kind of application it is?
    I will somehow test and try bltoolkit with mvc and also these others you mentioned.

  4. @goran: There's plenty documentation on T4. And MVC doesn't have anything to do with DAL. You can choose whatever you prefer. MVC doesn't imply and DAL technology.

    Maybe EF is suitable in your case which largely depends on the project you're doing.

    My project is about human resource planning and is integrated into Sharepoint since client uses Sharepoint heavily for other things as well. It was their requirement (otherwise I wouldn't use it). And since I love MVC over WebForms I took the pain of integrating it into it, so my life is now much much easier.

    And BLToolkit was chosen over EF since I have very complex background processing involved in the application that does huge data manipulations and calculations. But apart from these scarce facts I can't talk about other details.

  5. Thanks one more time for those comments, i must say my db is large but calculation on data isn't much complicated, creating sums and multiplication for some data and that's it. I think yours are much much more complicated :)
    What do ypu think about EF with Repository Pattern, I am getting pretty good with this one by the way.

  6. I am trying to run the suggested templte in my code and I get this error; has anyone seen this error before?
    Error 5 Running transformation: System.IndexOutOfRangeException: Index was outside the bounds of the array.
    at Microsoft.VisualStudio.TextTemplatingAAE2B892B2228F8E5A2F117332B32EE9.GeneratedTextTransformation.TransformText() in c:\SRC-DEV\T4Sample\T4Sample\ 144
    at Microsoft.VisualStudio.TextTemplating.TransformationRunner.RunTransformation(TemplateProcessingSession session, String source, ITextTemplatingEngineHost host, String& result) 1 1

    1. I faced same issue of indexoutofrangeexception? How did you solve it?


    2. To both of you guys. The best way would be to debug T4 template while it generates the file and observe execution and actual variable values. See Scott Hanselman's blog post how to do just that.


This is a fully moderated comments section. All spam comments are marked as spam without exception. Don't even try.