Fluent ADO.NET. An attempt!

Yes, I know there is NHibernate and other decent ORM solutions but, there are times when using an ORM is not an option. Still the ADO.NET API is VERY boring thing to use. Yes, you can make some SqlHelper classes that can help you but still many of the SqlHelpers lack the needed flexibility. So, I’ll try to make a Fluent wrapper for ADO.NET API.

What are we trying to achieve?

Our goal is to make usage of ADO.NET API more friendly and easier. So let’s start with some examples of our desired code usage.

Wouldn’t it be nice if we could use SqlCommand object in following way?

SqlCommand command = new SqlCommand();
SqlConnection connection = new SqlConnection();
command.UsingConnection(connection)
.ExecuteQuery("SELECT * FROM USERS WHERE ID=@id")
.AddParameter("@id",    1)
.AsDataTable();

or..

SqlCommand command = new SqlCommand();
SqlConnection connection = new SqlConnection();
command.UsingConnection(connection)
.ExecuteQuery("UPDATE Users SET Active=1 WHERE ID=@id")
.AddParameter("@id",1)
.ExecuteNonQuery();

Note: Before we go further with this fluent ADO.NET I need to say that there is already an open source project related to ADO.NET fluent interface. As a matter of fact I had a short discussion with coordinator of the project about some design aspects of the API. The design differences between the two approaches are related to supporting different database engines. You can read about this short discussion here.

How to achieve our goal?

To achieve usage of SqlCommand as shown above, we can go and write wrapper around SqlCommand object and that would definitely work. But what if I need to use Access as data source? In that case I wouldn’t be using the SqlCommand class, I would be using OleDbCommand instead. To avoid writing wrapper around all possible (Oracle,Postgre,MySql) databases, we can instead write wrapper around IDbCommand interface which all of the specific commands implement. This approach is rather good, but has some major drawback. Problem is that we would not be able to take advantage of specific features that are available on some of the IDbCommand implementations. One such property on SqlCommand object is "Notification", which is not present in IDbCommand interface.

To get around this problem we could make use of extension methods, which could be written in fluent approach and thus enable that specific features of IDbCommand implementations can be configured fluently. So let’s start the implementation.

The implementation

Please note that I will not be implementing whole API, but I’ll only show the concept.

Our wrapper class will be called FluentDbCommand, it is generic class that receives a IDbCommand as the type parameter. Here’s the code listing:

public class FluentDbCommand<T>
where T : IDbCommand
{
    T command = default(T);
    Func<T> commandConstructor = null;

    public T InnerCommand
    {
        get
        {
            return command;
        }
    }
    public FluentDbCommand()
    {
        // In case we don't provide any factory for creating command objects
        // try to build the object using reflection
        command = Activator.CreateInstance<T>();
    }
    public FluentDbCommand(Func<T> commandConstructor)
    {
        this.commandConstructor = commandConstructor;
        command = this.commandConstructor.Invoke();
    }

    public FluentDbCommand<T> AddParameter(string name, object value)
    {
        IDbDataParameter parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
        return this;
    }
    public FluentDbCommand<T> QueryToRun(string query)
    {
        command.CommandType = CommandType.Text;
        command.CommandText = query;
        return this;
    }
    public DataTable AsDatatable()
    {
        DataTable tblResults = new DataTable();
        tblResults.Load(command.ExecuteReader());
        return tblResults;
    }
    public FluentDbCommand<T> UseConnection(IDbConnection connection)
    {
        this.command.Connection = connection;
        return this;
    }
    public int ExecuteNonQuery()
    {
        return this.InnerCommand.ExecuteNonQuery();
    }
    public object ExecuteScalar()
    {
        return this.InnerCommand.ExecuteScalar();
    }
    public IDataReader ExecuteReader()
    {
        return this.InnerCommand.ExecuteReader();
    }
    public FluentDbCommand<T> RunStoredProcedure(string strProcedureName)
    {
        this.InnerCommand.CommandType = CommandType.StoredProcedure;
        this.InnerCommand.CommandText = strProcedureName;
        return this;
    }
}

And usage of the FluentDbCommand would be:

FluentDbCommand<OleDbCommand> oleCmd = new FluentDbCommand<OleDbCommand>();

DataTable result = oleCmd.UseConnection(connection)
                 .QueryToRun("SELECT * FROM USERS")
                 .AddParameter("@id", 1)
                 .AsDatatable();

We see that it’s rather simple to implement the fluent interface around IDbCommand, but as I said earlier, to support the specific features of concrete database engine we need to use extension methods.

For example to support NotificationAutoEnlist property of SqlCommand class we would need to write following extension method

public static class SQLServerSpecificExtensions
{
public static FluentDbCommand<SqlCommand> NotificationAutoEnlist(this FluentDbCommand<SqlCommand> command, bool value)
{
command.InnerCommand.NotificationAutoEnlist = true;
return command;
}
}

And that would enable us to use the FluentDbCommand in following way:

FluentDbCommand<SqlCommand> sqlCmd = new FluentDbCommand<SqlCommand>();

sqlCmd.UseConnection(connection)
            .QueryToRun("SELECT * FROM USERS WHERE ID=@id")
            .AddParameter("@id", 1)
            .NotificationAutoEnlist(true)
            .AsDatatable();

That would be it. This implementations is just a beginning of the full-blown wrapper, but it’s shows the concept very well.