If you're developing rich web clients using Asp.net MVC on the back-end, you've probably come across this functionality that can be described with these conditions:
This is something very simple, but very useful. Something Microsoft should've put in Visual Studio in the first place long ago. When I develop data-aware applications I usually end up writing database scripts to make it easy to (re)deploy and version control my database model. I usually write several files just to make things controllable and manageable. But deploying them is a different task altogether. And a tedious one as well. I have to fire up SQL Management Studio and then run all those scripts in correct order. Since this is done rather frequently during application development it seems like a very good candidate for automation or simplification. So let's do just that.
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.
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.
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:
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.
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_GetAllPerson_SaveRelatedDataIf 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: }
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.
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: }
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:
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. */
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.