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!"
Hosting By
Seems to me that if one could pay off their mortgage early (earlier than 30 years) it would be a Good Thing (tm). The argument that the tax write off we (American's) get for paying mortgage interest is specious. You get a $0.35 tax break for the privilege of paying the mortgage company $1.00. How is that useful? If I pay $10,000 in a year in just interest, I get to (this is a simplification, really I adjust my gross) write off $3,500 in tax, but I'm STILL out $6,500. That $6,500 could be working elsewhere.
I did like 