Fluent Ado.Net and API design

Some people say that public speaking is the scariest thing you can do.  I say it is releasing code into the wild.  I don’t even want to guess at how many projects never get released because of nerves.  But I still released Fluent Ado.net for some reason.  And I’d have to say, public speaking is easy in comparison.

My hard part is API design.  By its nature, API design is an opinionated process.  A good API does not just grant you access to functionality, but it guides you the correct outcome.  It encourages good behavior, and doesn’t hinder bad behavior too much.   This isn’t just fluent APIs, but all APIs. 

That was part of the goal for Fluent Ado.Net.  Encourage the use of parameterized queries and a domain driven style of development.  I tried to do this be reducing the amount of code required to create parameterized queries, and make mapping the result set to an object easy to do with SetMap.    So now a standard paramertized query can look like this:

   1: public IList<Customer> GetCustomerByWeight(int weight)

   2: {

   3:     const string sql =

   4:         @"SELECT ID, FirstName, LastName, BirthDay, Height, Weight FROM Customer WHERE Weight > @weight";

   5:     return new FluentCommand<Customer>(sql)

   6:         .AddInt("weight", weight)

   7:         .SetMap(reader => new Customer

   8:            {

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

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

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

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

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

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

  15:            })

  16:         .AsList();

  17: }

I also discourage bad behavior, like using Stored Procedures.  The default path for FluentCommand is to execute a SQL statement.  But at the same time, you  modify the constructor and you can call a stored procedure.  I don’t consider that really a hindrance.  One method.  You can handle that.

   1: public IList<Customer> GetCustomerByWeightSP(int weight)

   2: {

   3:     const string sql =

   4:         @"dbo.GetCustomerbyWeightSP";

   5:     return new FluentCommand<Customer>(sql, CommandType.StoredProcedure)

   6:         .AddInt("weight", weight)

   7:         .SetMap(reader => new Customer

   8:            {

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

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

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

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

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

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

  15:            })

  16:         .AsList();

  17: }

One of my hardest considerations was on SetMap actually.  It had to be easy to use and allow for code sharing.  A delegate was an obvious choice, but delegates always seem to cause head scratching whenever I introduce them as a topic.   But I wanted to be able to share mapping code between multiple method calls.  So here is an implementation where SetMap is passed a separate function:

   1: public IList<Customer> GetCustomerByWeight(int weight)

   2: {

   3:     const string sql =

   4:         @"SELECT ID, FirstName, LastName, BirthDay, Height, Weight FROM Customer WHERE Weight > @weight";

   5:     return new FluentCommand<Customer>(sql)

   6:         .AddInt("weight", weight)

   7:         .SetMap(Customer_Map)

   8:         .AsList();

   9: }

  10:  

  11: private Customer Customer_Map(DataReader reader)

  12: {

  13:     return new Customer

  14:            {

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

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

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

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

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

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

  21:            };

  22: }

But that wasn’t the hard part actually.  The hard part was deciding if I actually needed SetMap at all.  SetMap is only needed if you use AsList, AsEnumerator, or AsSingle.  You don’t need it for AsScalar, AsNonQuery, or AsDataReader.  So when I was talking about guidance, I could have made SetMap a parameter for each of those methods.  In the end I allow for both…I just think using the SetMap method is prettier and easier to read.  I also could have gotten funky with Attributes and esoteric LINQ expressions, but considering the .net cf constraint, plus teachability of the system I discarded that idea rather quickly. 

One of the other design goals was to be an effective wrapper around Ado.Net.  Currently if you use raw Ado.Net you have to specify your provider.  SqlCommand, SqlCeCommand, OledbCommand, OdbcCommand, etc.  I wanted one API that you could use without having to deal with the specific provider. 

So invariably, the first patch I received (thank you Joel, I’m not bad mouthing you, I used a lot of your patch) added Sql Server, and split up the class names to differentiate between Sql Server and Sql Server CE.  If you look at different check-in versions, you will see that I was struggling with this as well.  The design is to make adding support for a database a trivial affair with a good upside in usage.  The downside of this approach is that in a particular class you can effectively reference one database system.  I consider that a feature actually.

What is left to do?  Very few open source projects have adequate documentation.  I’m struggling with that as well.  But I am working on that.  I also plan on expanding out the database support to MySql, Oledb, Odbc, Sybase Anywhere, and Sybase Ultralight (if you haven’t heard of it, consider yourself lucky).  In the end though, even if no one else uses this code, I will be.