Introducing Fluent Ado.Net

The following code is the result of one night coding with a fair amount of beer, and the desire to solve an annoyance.  Enough said. 

My problem is this: I’m coding an application for .Net Compact Framework (.Net CF).  One thing you should know about coding the .net cf,  a number of standard items in the regular .Net Framework do not exist here.  For me, one of those is NHibernate (or almost any ORM for that matter).  No matter how much I would like to use NHibernate on .net cf, it really isn’t practical.  Actually, there probably is an ORM out there that works with .net cf and Sql Server CE (SubSonic probably works), but I have ‘other’ factors at play as well – like clients who tell me not to use an ORM for their project.

That means I’m back to plain-jane Ado.Net code.  It sucks.  There had to be a better way.  And truthfully, there was.  But even that sucked, just not as badly.   I figured there must be a good way to make database calls in C#, using some of the new .Net 3.5 features I love, to take some of the drudge work out of this mess.  After a couple of beers I had a solution.

My solution looks like this:

   1: public Customer ByID(Guid id)

   2: {

   3:     const string sql = "SELECT ID, FirstName, LastName, BirthDay, Height, Weight FROM Customer WHERE ID = @id";

   4:     return new FluentCommand<Customer>(sql)

   5:         .AddGuid("id", id)

   6:         .SetMap(reader => new Customer

   7:                           {

   8:                               ID = reader.GetGuid("ID"),

   9:                               FirstName = reader.GetString("FirstName"),

  10:                               LastName = reader.GetString("LastName"),

  11:                               Birthday = reader.GetDateTime("Birthday"),

  12:                               Height = reader.GetIntNullable("Height"),

  13:                               Weight = reader.GetIntNullable("Weight")

  14:                           }

  15:         )

  16:         .AsSingle();

  17: }

I created a fluent interface for making Ado.Net calls, wrapping up the setting of parameters, and retrieving values.  This solution sort-of mimics NHibernate criteria queries, Fluent NHibernate, and AutoMapper.

AddGuid sets the ‘id’ parameter in the sql query to the id field being passed into the method.

SetMap takes a function (I’m using a lambda expression) to convert data from the data reader to an actual object.

Reader is a wrapper data reader that allows you to access value by name instead of index.   There are ‘Nullable’ versions of all Get value type methods to handle null data.  So there is a GetInt and a GetIntNullable.

AsList returns the result in an IList.  There is also AsEnumerator, AsDataReader, AsSingle, AsScalar, and AsNonQuery (for updates,inserts, and deletes).

FAQ:

1. Why a Fluent Interface?   Because I thought the world needed one more.   Actually, I don’t think Fluent Interfaces are the end-all-be-all.  But they are very nice for particular problems.  I really like that they don’t interfere with my coding style.    Done correctly they are easy to read and guide you through an api.  Done incorrectly they are a wordy mess.  I think I’m somewhere in the middle with FluentAdo.Net

2. Why the SetMap?  Why didn’t you just throw in a few Expressions and be done with it?  Because Expressions do not exist in .Net CF, which was the prime target.

3. What about other database?  I end up switching databases every month or two, which is another reason for this approach.  There are 3 files to change to port to a different Ado.Net provider.  ConnectionFactory.cs, FluentCommand_Params.cs, and one line in FluentCommand.cs.

4. So how do I use this in my code?  I am distributing this as code, not a library.  Include ConnectionFactory.cs, DataReader.cs, FluentCommand.cs, and FluentCommand_Params.cs somewhere in your solution and go to town.

5.  Idiot!!! Why did you do xyz like that when there was clearly a better way!!!   Actually, that isn’t a question.  But, if you have suggestions, please check out the forum on CodePlex.com.  That is why they are there.

6.  Why don’t you call this an ORM?  Technically it could be called a low-tech ORM.  But real ORMs tend to do a lot more for you.  Like wrap SQL in a api, generate mapping files, cache data results, and provide further abstractions over Ado.Net.  All that Fluent Ado.Net is giving you is a paper thin wrapper.  If you want a real ORM go check out NHibernate, SubSonic, Entity Framework, or LLBLGen.

7. So where is the code?  Oops, almost forgot that part.  http://fluentado.codeplex.com/