CodeGen'ing a Data Access Layer with CodeSmith
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;
command.Parameters.Add(outputParameter);
command.ExecuteNonQuery();
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="database.name">
<do>
<property name="databaseWrapper.outputFile" value="${database.name}Database.g.cs"/>
<delete file="DatabaseFacade\DataMapper\${databaseWrapper.outputFile}" failonerror="false"/>
<codegen template="DatabaseFacade\DataMapper\DatabaseWrapper.cst" outputdir="DatabaseFacade\DataMapper" outputfile="${databaseWrapper.outputFile}">
<properties>
<property name="ConnectionString" value="user=${DB_Login};password=${DB_Password};server=${DB_Server};database=${database.name}"/>
</properties>
</codegen>
</do>
</foreach>
</target>
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.
About Newsletter
Daryl
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".
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.
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.
http://www.entityspaces.net
Scott's final point is where it's at, though: stop screwing around with boilerplate ADO.NET (or whatever) and start doing some business.
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?
Comments are closed.