Scott Hanselman

CodeGen'ing a Data Access Layer with CodeSmith

April 12, '06 Comments [11] Posted in Nant | Tools
Sponsored By

I mentioned CodeSmith in my podcast post earlier, and we'll talk about it on next week's show, but as a nice coincedence Brian Windheim, one of our architects, posted how he was using Code Generation on one of our internal Corillian (das)Blogs.

Here's what he had to say (some parts redacted):

Brian: I’ve become a huge fan of code generation for data access and object mapping.  The [blank] team has been using a generated data access layer to interface with the [blank] database for a long time now, and the reward has been tremendous.  Without trying to sell it too much, here’s what the client code looks like, inside a hand-coded product API method:

FooThing ReadFooThing(long fooID)


   using (SomeDatabase db = new SomeDatabase())


      return FooThing.CreateFromDataReader(db.ReadFooThing(fooID));



In the above code, the FooThing and SomeDatabase types are 100% generated code.  Methods are generated for every stored procedure, and multiple sproc calls can be used inside the same using block, as the SqlConnection is maintained for the undisposed life of the SomeDatabase instance.  Classes with single-record and multiple-record column-to-property converters are generated for each table in the database as well.  The codegen implementation will work on just about any database (it is not team-specific), and if you adhere to a few basic rules with your sproc names, the generated code will be very nice.

Some history: last summer I built a CodeSmith template to do the codegen, based on sample code from CodeSmith and some trial and error.  Start to finish was under three hours, and I had no CodeSmith experience before, other than poking around in the [Corillian Voyager SDK’s CodeGen templates].  There has since been some minor maintenance to it, but the overall time commitment has been exceedingly small.  And the benefits?  Here’s a start:

1. Compile-time failures when a stored procedure interface has changed and the application code hasn’t.
2. Type safety for sproc inputs.
3. Automatic column mapping from sproc result sets to strongly typed domain objects.
4. Automatic type mapping from SQL to CLR types and vice-versa.  If the sproc takes in a smallint, you won’t get away with shoving a System.Int64 in there.
5. Automatic mapping of SQL OUT and INOUT params to method return types.
6. Awareness of DbNull and CLR value type collisions.

So what does the generated stored procedure wrapper code look like?  Here’s a sample for a read-single-record sproc:

public IDataReader ReadFooThinglong fooID)


   SqlCommand command = new SqlCommand("dbo.ReadFooThing", this._connection);

   command.CommandType = CommandType.StoredProcedure;

   command.Parameters.Add(new SqlParameter("@FooID", fooID));

   return command.ExecuteReader();


… and another sample for a create-new-record sproc, which returns the ID of the new record:

public long CreateFooThing(int batchID, long accountID, string checkNumber, decimal amount, string currencyCode,

  DateTime issuedDate, DateTime someDate, string reason, string payee)


   SqlCommand command = new SqlCommand("dbo.CreateFooThing", this._connection);

   command.CommandType = CommandType.StoredProcedure;

   command.Parameters.Add(new SqlParameter("@BatchID", batchID));

   command.Parameters.Add(new SqlParameter("@AccountID", accountID));

   command.Parameters.Add(new SqlParameter("@CheckNumber", (checkNumber == null) ? System.DBNull.Value : (object) checkNumber));

   command.Parameters.Add(new SqlParameter("@Amount", amount));

   command.Parameters.Add(new SqlParameter("@CurrencyCode", (currencyCode == null) ? System.DBNull.Value : (object) currencyCode));

   command.Parameters.Add(new SqlParameter("@IssuedDate", (issuedDate == DateTime.MinValue) ? System.DBNull.Value : (object) issuedDate));

   command.Parameters.Add(new SqlParameter("@SomeDate", (someDate == DateTime.MinValue) ? System.DBNull.Value : (object) someDate));

   command.Parameters.Add(new SqlParameter("@Reason", (reason == null) ? System.DBNull.Value : (object) reason));

   command.Parameters.Add(new SqlParameter("@Payee", (payee == null) ? System.DBNull.Value : (object) payee));

   SqlParameter outputParameter = null;

   outputParameter = new SqlParameter("@FooID", new long());

   outputParameter.Direction = ParameterDirection.Output;



   return (long) outputParameter.Value;


The generated domain types that correspond to tables are rather big, so I won’t include them here.

I use the code generator in our builds in the following manner:

1. Drop the existing database.
2. Deploy the database (schema + sprocs).
3. Run the code generator to produce SomeDatabase.g.cs.
4. Compile SomeDatabase.g.cs into assembly Corillian.SomeDatabase.Facade.dll.
5. Compile assemblies dependent upon the above.

There are some simple algorithms that I use to determine whether a stored procedure is a read-single, read-multiple, create-new, or something else entirely.  I leave the discovery of this as an exercise to the reader.

My nant build target looks something like the following.  I re-used the [Voyager SDK’s CodeSmith "codegen" task] to kick everything off.  Note that all I need is a connection string …

<target name="codegenDatabaseWrappers">
 <property name="databaseList" value="SomeDatabase"/>
 <echo message="databaseList = ${databaseList}"/>
 <foreach item="String" delim="," in="${databaseList}" property="">
   <property name="databaseWrapper.outputFile" value="${}Database.g.cs"/>
   <delete file="DatabaseFacade\DataMapper\${databaseWrapper.outputFile}" failonerror="false"/>
   <codegen template="DatabaseFacade\DataMapper\DatabaseWrapper.cst" outputdir="DatabaseFacade\DataMapper" outputfile="${databaseWrapper.outputFile}">
     <property name="ConnectionString" value="user=${DB_Login};password=${DB_Password};server=${DB_Server};database=${}"/>

Scott: Certainly there are dozens (hundreds?) of ways to generate a Database Access Layer (DAL) as there's lots of opinions as to how they should look and what's the best style. The point here is that if your database is nice and regular and needs CRUD (Create, Read, Update, Delete) then there's really no reason you shouldn't be able to generate your DAL (and often even sprocs) from either your sprocs, tables and/or views. You'll lose nothing and gain so much more time, especially in a Continuous Integration environment.

File Attachment: DatabaseWrapper.cst.txt (14 KB)

Unrelated Quote of the Day: "I can't be racist, I drive a Prius!"

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb
Thursday, 13 April 2006 01:20:11 UTC
I have been living and breathing generated code for the past year, my team of 4 is currently supporting and adding to about 1,500,000 lines of code, and there would be no way without generated code and good strong coding patterns. I looked at code smith, but decided to roll our own. We gen the sprocs, the DAL and a "standard" Biz layer. Amazing productivity! With some clever SQL and use of reflection we rarely have to touch the DAL.
Thursday, 13 April 2006 01:32:58 UTC
I've been a fan of code generation in many forms since 1997. That year I worked for a company working on the US Olympic commitee Nagano Winter games web site. We need to generate Web Content from a template, now in hind-site that's all old school stuff, but back then... not too many choices for a potentially high volume site and very little if you wanted the content template based and pre-compiled.

Since then I've written three generations of DB->XML->OOP code gens in both Java and C#. Its really saved us a ton of time getting O-R mappings done and gives you a jump start into solving the business problem. That said, generalized solutions usually have a failing when performance tuning comes into play, so you need to be watchful of that.

Now think of what you can do with CodeGen and .NET 2.0 partial classes and Generics. I think there are shall we say "Interesting Possibilities".
Thursday, 13 April 2006 01:48:07 UTC

We base everything off the patterns in our database design and I feel our code generator actually took less time to build than it would have taken to setup code smith. I think code smith could have done it, but with learning curve of code smith and the fact that I inherited a sproc generator that I just extended, Code smith just did not make sense.

Thursday, 13 April 2006 01:56:03 UTC
I guess my code generator was easy to implement because it fits only my patterns only, where code smith allows for n patterns.

I also wanted code that required virtually no upkeep. So I think there is more to generation than the ability to generate, the patterns are much more important than the ability to generate code. You have to know what you want to generate before you should generate anything.
Thursday, 13 April 2006 02:31:55 UTC
CodeSmith is great, but Microsoft's DSLs are where it's at. Personally, I think the idea of designing a database, then generating an object model from it is backwards. A much better approach IMO is to generate your object model _and_ data model from schema. In the case of DSLs, you create a designer to help developers visually design the schema. Using templates, you can generate all the .NET and SQL you want.
Thursday, 13 April 2006 14:08:00 UTC
Check out, it's one of the nicest DAL generators out there.
Ryan Roberts
Thursday, 13 April 2006 14:49:55 UTC
Here is one I am working on, and I currently use for my day job.
Thursday, 13 April 2006 15:50:31 UTC
Daniel: Generating your data model and object model from schema is interesting, and may work well if you are in a green-field development (new development) situation. What happens when you are working against a legacy stored procedure interface on an existing database? This is where you can take serious advantage of code generation from database metadata.

Scott's final point is where it's at, though: stop screwing around with boilerplate ADO.NET (or whatever) and start doing some business.
Brian Windheim
Friday, 14 April 2006 16:45:30 UTC

May be its a dumb question. I am very curious about the above code. What happens to the client code if your CreateFooThing signature changes? Say you added or removed couple of columns from the database and storedproc.

Are you not breaking the client code? How this plays out in a continuous integration scenarios? How do you propagate those changes to the client code?

Kiran Chand
Friday, 14 April 2006 17:34:49 UTC
LLBLGen is da bomb. If there's a complaint its that, because it wants to keep everything strongly-typed, it uses typed ICollections and not DataSets, and it doesn't currently work well with ObjectDataSource and the ASP.NET 2.0 controls. The next version, due Real Soon Now, will address this.
Friday, 14 April 2006 23:20:02 UTC
Kiran: When the CreateFooThing signature changes, the client code breaks (especially in a continuous integration environment). That's a huge benefit. Catch the signature mismatch at *compile* time, not *runtime*.
Brian Windheim
Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.