Fluent Database Access

Jörgen Andersson

4.88/5 (33 votes)

Oct 6, 2014

CPOL

5 min read

70941

3380

Fluent database access

Introduction

I've always thought that connecting to a database is unnecessary verbose.
If we take a look at a schoolbook example:

SqlConnection Connection = default(SqlConnection);
SqlCommand Command = default(SqlCommand);
try
{
    Connection = new SqlConnection("MyConnectionString");
    Command = Connection.CreateCommand();
    Command.CommandText = "Select * From MyTable";
    Connection.Open();
    return Command.ExecuteReader();
}
finally
{
    if (Command != null)
        Command.Dispose();
    if (Connection != null)
        Connection.Dispose();
}

Dim Connection As SqlConnection
Dim Command As SqlCommand
Try
    Connection = New SqlConnection("MyConnectionString")
    Command = Connection.CreateCommand
    Command.CommandText = "Select * From MyTable"
    Connection.Open()
    Return Command.ExecuteReader
Finally
    If Not IsNothing(Command) Then Command.Dispose()
    If Not IsNothing(Connection) Then Connection.Dispose()
End Try

That's lot of repetitive code for every time I want to do something with the database.
I'm almost always using one and the same Connection, the same code for creating the Command and opening the connection.
And in 99% of the cases, I'm disposing the command and closing the Connection afterwards.

I also like fluent interfaces, so what if the same code could look like this instead:

DbAccess.GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader();

 GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader

Or even better:

return MyDB.GetConnection.GetCommand("Select * From MyTable").GetDataReader();

Return MyDB.GetCommand("Select * From MyTable").GetDataReader

So I decided to create that code.

The Connection

So what design requirements should I have besides being fluent?
Well, first of, I'd like it to be database agnostic, and secondly I want to use the Connectionstrings section in the config file so I can change database without recompiling.
And I also want to use ADO.NET so that I'm reasonably sure it will have support from many Db vendors, and stays working when the next update to the framework comes.
That's all.

So to avoid inventing the wheel again, I decided to use the DbProviderFactories Class.

So let's start with a Shared Class and add a function that returns a DbProviderFactory, if it's installed.

private readonly static DataTable FactoriesTable = DbProviderFactories.GetFactoryClasses();

private static DbProviderFactory GetFactory(ConnectionStringSettings ConnectionStringSetting)
{
    foreach (DataRow row in FactoriesTable.Rows)
    {
        if (Convert.ToString(row["InvariantName"]) == ConnectionStringSetting.ProviderName)
        {
            return DbProviderFactories.GetFactory(ConnectionStringSetting.ProviderName);
        }
    }
    return null;
}

Private ReadOnly FactoriesTable As DataTable = DbProviderFactories.GetFactoryClasses()

Private Function GetFactory(ConnectionStringSetting As ConnectionStringSettings) As DbProviderFactory
    For Each row As DataRow In FactoriesTable.Rows
        If CStr(row("InvariantName")) = ConnectionStringSetting.ProviderName Then
            Return DbProviderFactories.GetFactory(ConnectionStringSetting.ProviderName)
        End If
    Next
    Return Nothing
End Function

And use this function to create a DbConnection:

private static DbConnection CreateDbConnection(ConnectionStringSettings ConnectionStringSetting)
{
    DbProviderFactory Factory = GetFactory(ConnectionStringSetting);
    if ((Factory != null))
    {
        DbConnection Connection = Factory.CreateConnection();
        if ((Connection != null))
        {
            Connection.ConnectionString = ConnectionStringSetting.ConnectionString;
            return Connection;
        }
    }
    return null;
}

Private Function CreateDbConnection(ConnectionStringSetting As ConnectionStringSettings) As DbConnection
    Dim Factory As DbProviderFactory = GetFactory(ConnectionStringSetting)
    If Not IsNothing(Factory) Then
        Dim Connection As DbConnection = Factory.CreateConnection()
        If Not IsNothing(Connection) Then
            Connection.ConnectionString = ConnectionStringSetting.ConnectionString
            Return Connection
        End If
    End If
    Return Nothing
End Function

Now we have the base for a couple of methods that returns a DbConnection.

public static DbConnection GetDbConnection()
{
    DbConnection Connection = null;
    foreach (ConnectionStringSettings ConnectionStringSetting in ConfigurationManager.ConnectionStrings)
    {
        Connection = CreateDbConnection(ConnectionStringSetting);
        if ((Connection != null))
        {
            return Connection;
        }
    }
    throw new DataException("Check the ConnectionString Section in the ConfigFile");
}

public static DbConnection GetDbConnection(string ConnectionName)
{
    foreach (ConnectionStringSettings ConnectionStringSetting in ConfigurationManager.ConnectionStrings)
    {
        if (ConnectionName == ConnectionStringSetting.Name)
        {
            return CreateDbConnection(ConnectionStringSetting);
        }
    }
    throw new DataException(string.Format("Check the ConnectionString Section 
            in the ConfigFile if a connectionString named {0} exists", ConnectionName));
}

Public Function GetDbConnection() As DbConnection
    Dim Connection As DbConnection = Nothing
    For Each ConnectionStringSetting As ConnectionStringSettings _
                 In ConfigurationManager.ConnectionStrings
        Connection = CreateDbConnection(ConnectionStringSetting)
        If Not IsNothing(Connection) Then
            Return Connection
        End If
    Next
    Throw New DataException("Check the ConnectionString Section in the ConfigFile")
End Function

Public Function GetDbConnection(ConnectionName As String) As DbConnection
    For Each ConnectionStringSetting As ConnectionStringSettings _
                         In ConfigurationManager.ConnectionStrings
        If ConnectionName = ConnectionStringSetting.Name Then
            Return CreateDbConnection(ConnectionStringSetting)
        End If
    Next
    Throw New DataException(string.Format("Check the ConnectionString Section in the _
                 ConfigFile if a connectionString named {0} exists", ConnectionName))
End Function

In most of the cases, I only use one database, so the function simply returns a DbConnection for the first ConnectionString that has an installed Provider.
But often enough, you want to name the connection, so there's an overload for that too.

The Command

The creator of a DbCommand doesn't take any parameters, so we need to wrap that up in a function too.

public static DbCommand GetCommand(this DbConnection Connection,
    string CommandText,
    IEnumerable<DbParameter> Parameters = null,
    CommandType CommandType = System.Data.CommandType.Text)
{
    if (Connection == null) { throw new ArgumentNullException("Connection is null"); }
    if (string.IsNullOrWhiteSpace(CommandText)) 
             { throw new ArgumentNullException("CommandText is null"); }

    DbCommand Command = Connection.CreateCommand();
    Command.CommandText = CommandText;
    Command.CommandType = CommandType;
    if ((Parameters != null))
    {
        foreach (DbParameter Parameter in Parameters)
        {
            Command.Parameters.Add(Parameter);
        }
    }
    return Command;
}

<Extension> _
Public Function GetCommand(Connection As DbConnection,
                           CommandText As String,
                           Optional Parameters As IEnumerable(Of DbParameter) = Nothing,
                           Optional CommandType As CommandType = System.Data.CommandType.Text) _
                                         As DbCommand

    If Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")
    If String.IsNullOrWhiteSpace(CommandText) _
               Then Throw New ArgumentNullException("CommandText is null")

    Dim Command As DbCommand = Connection.CreateCommand()
    Command.CommandText = CommandText
    Command.CommandType = CommandType
    If (Parameters IsNot Nothing) Then
        For Each Parameter As DbParameter In Parameters
            Command.Parameters.Add(Parameter)
        Next
    End If
    Return Command
End Function

Executing the Command

Normally, you would simply take the Command and ExecuteReader or ExecuteNonQuery, but we need to clean up after ourselves so those need to be wrapped too.

public static DbDataReader GetDataReader(this DbCommand Command, 
CommandBehavior CommandBehavior = CommandBehavior.CloseConnection | CommandBehavior.KeyInfo)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
        DbConnection Connection = Command.Connection;
        if (Connection.State != ConnectionState.Open)
        {
            Connection.Open();
        }
        return Command.ExecuteReader(CommandBehavior);
    }
}

public static DataTable GetDataTable(this DbCommand Command, String TableName = "")
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Command.Connection is null"); }

    using (DbConnection Connection = Command.Connection)
    using (Command)
    {
        DbProviderFactory Factory = DbProviderFactories.GetFactory(Connection);
        using (DbDataAdapter Adapter = Factory.CreateDataAdapter())
        {
            Adapter.SelectCommand = Command;
            DataTable dt = new DataTable(TableName);
            Adapter.Fill(dt);
            return dt;
        }
    }
}

public static object GetScalar(this DbCommand Command)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
         using (DbConnection Connection = Command.Connection)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            return Command.ExecuteScalar();
        }
    }
}

public static T GetScalar<T>(this DbCommand Command)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
        using (DbConnection Connection = Command.Connection)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            object Value = Command.ExecuteScalar();
            if ((object.ReferenceEquals(Value, DBNull.Value)) || (Value == null))
            {
                return default(T);
            }
            else if (object.ReferenceEquals(typeof(T), Value.GetType()) || 
                               typeof(T).IsAssignableFrom(Value.GetType()))
            {
                return (T)Value;
            }
            else if (typeof(T).IsGenericType && 
                   typeof(T).GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                return (T)Convert.ChangeType(Value, typeof(T).GetGenericArguments()[0]);
            }
            else
            {
                return (T)Convert.ChangeType(Value, typeof(T));
            }
        }
    }
}

public static int GetNonQuery(this DbCommand Command)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
        using (DbConnection Connection = Command.Connection)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            return Command.ExecuteNonQuery();
        }
    }
}

<Extension>
    Public Function GetDataReader(Command As DbCommand, _
          Optional CommandBehavior As CommandBehavior = DirectCast(CommandBehavior.CloseConnection + _
          CommandBehavior.KeyInfo, CommandBehavior)) As DbDataReader
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Dim Connection As DbConnection = Command.Connection
    
        If Connection.State <> ConnectionState.Open Then
            Connection.Open()
        End If
        Return Command.ExecuteReader(CommandBehavior)
    End Using
End Function

<Extension>
Public Function GetDataTable(Command As DbCommand, Optional TableName As [String] = "") As DataTable
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Command.Connection is null")

    Using Connection As DbConnection = Command.Connection
        Using Command
            Dim Factory As DbProviderFactory = DbProviderFactories.GetFactory(Connection)
            Using Adapter As DbDataAdapter = Factory.CreateDataAdapter()
                Adapter.SelectCommand = Command
                Dim dt As New DataTable(TableName)
                Adapter.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

<Extension>
Public Function GetScalar(Command As DbCommand) As Object
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Using Connection As DbConnection = Command.Connection
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            Return Command.ExecuteScalar()
        End Using
    End Using
End Function

<Extension>
Public Function GetScalar(Of T)(Command As DbCommand) As T
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Using Connection As DbConnection = Command.Connection
            If Connection.State <> ConnectionState.Open Then Connection.Open()

            Dim Value As Object = Command.ExecuteScalar()
            If (Object.ReferenceEquals(Value, DBNull.Value)) OrElse (Value Is Nothing) Then
                Return Nothing
            ElseIf Object.ReferenceEquals(GetType(T), Value.[GetType]()) _
                   OrElse GetType(T).IsAssignableFrom(Value.[GetType]()) Then
                Return DirectCast(Value, T)
            ElseIf GetType(T).IsGenericType AndAlso _
               GetType(T).GetGenericTypeDefinition() = GetType(Nullable(Of )) Then
                Return DirectCast(Convert.ChangeType(Value, GetType(T).GetGenericArguments()(0)), T)
            Else
                Return DirectCast(Convert.ChangeType(Value, GetType(T)), T)
            End If
        End Using
    End Using
End Function

<Extension>
Public Function GetNonQuery(Command As DbCommand) As Integer
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Using Connection As DbConnection = Command.Connection
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            Return Command.ExecuteNonQuery()
        End Using
    End Using
End Function

GetNonQuery? Isn't that a really silly name?
Well yes, but I've used the "Get" prefix for all other methods so far, and I like to be consistent. And ExecuteNonQuery does indeed return a value, so it's not entirely bad.
Please feel free to rename the methods if you feel like it, but "ExecuteNonQueryAndTerminateResourcesAndConnections" felt a bit silly too.

Anyway, now we've come as far that we can execute a datareader in one line while disposing all resources.

Return DbAccess.GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader();

Return GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader

But If Don't Like to Use a String Parameter for My databaseconnection?

Then, we need to add another function to this library:

private static DbConnection CreateDbConnection(ConnectionStringSettings ConnectionStringSetting)
{
    DbProviderFactory Factory = GetFactory(ConnectionStringSetting);
    if ((Factory != null))
    {
        DbConnection Connection = Factory.CreateConnection();
        if ((Connection != null))
        {
            Connection.ConnectionString = ConnectionStringSetting.ConnectionString;
            return Connection;
        }
    }
    return null;
}

Public Function GetDbConnection(ConnectionStringSetting As ConnectionStringSettings) As DbConnection
    Dim Connection As DbConnection = CreateDbConnection(ConnectionStringSetting)
    If Connection Is Nothing Then
        Throw New DataException("Provider not installed")
    Else
        Return Connection
    End If
End Function

Now we have the possibility to add a small function that adds Intellisense to the Connectionstrings. But note that this needs to be done per project.

public static class NorthWind
{
    public static SqlConnection GetConnection()
    {
        return SqlAccess.GetSQLConnection("Northwnd");
    }
}

Public Module MyConnections
    Function Northwind() As SqlConnection
        Return GetSQLConnection("Northwnd")
    End Function
End Module

This starts to look good.

return Northwnd.GetDbConnection.GetCommand("Select * From MyTable").GetDataReader();

Return Northwnd.GetCommand("Select * From MyTable").GetDataReader

What about Transactions?

Transactions are meant to be executed in a block, that's the whole point with them.
But let's add a couple of methods that make life a little bit easier.

public static DbCommand GetCommand(this DbTransaction Transaction, 
    string CommandText, 
    IEnumerable<DbParameter> Parameters = null, 
    CommandType CommandType = System.Data.CommandType.Text)
{
    if (Transaction == null) { throw new ArgumentNullException("Transaction is null"); }
    if (string.IsNullOrWhiteSpace(CommandText)) 
            { throw new ArgumentNullException("CommandText is null"); }

    DbCommand Command = Transaction.Connection.CreateCommand();
    Command.CommandText = CommandText;
    Command.CommandType = CommandType;
    if ((Parameters != null))
    {
        foreach (DbParameter Parameter in Parameters)
        {
            Command.Parameters.Add(Parameter);
        }
    }
    return Command;
}

Public Function GetCommand(Transaction As DbTransaction,
                           CommandText As String,
                           Optional Parameters As IEnumerable(Of DbParameter) = Nothing,
                           Optional CommandType As CommandType = System.Data.CommandType.Text) As DbCommand
    If Transaction Is Nothing Then Throw New ArgumentNullException("Transaction is null")
    If String.IsNullOrWhiteSpace(CommandText) Then Throw New ArgumentNullException("CommandText is null")

    Dim Command As DbCommand = Transaction.Connection.CreateCommand()
    Command.CommandText = CommandText
    Command.CommandType = CommandType
    If (Parameters IsNot Nothing) Then
        For Each Parameter As DbParameter In Parameters
            Command.Parameters.Add(Parameter)
        Next
    End If
    Return Command
End Function

Now we can execute a block of commands together like this:

using (DbConnection Connection = GetDbConnection())
{
    Connection.Open();
    using (DbTransaction Transaction = Connection.BeginTransaction())
    {
        try
        {
            using (DbCommand FirstCommand = Transaction.GetCommand_
                 ("INSERT INTO MyTable1 (Column1,Column2) VALUES (1,2)"))
            {
                FirstCommand.ExecuteNonQuery();
            }

            using (DbCommand LastCommand = Transaction.GetCommand_
                 ("INSERT INTO MyTable2 (ColumnA,ColumnB) VALUES ('X','Y')"))
            {
                LastCommand.ExecuteNonQuery();
            }

            Transaction.Commit();
        }
        catch (Exception ex)
        {
            Transaction.Rollback();
            throw;
        }
    }
}

Using Connection As DbConnection = GetDbConnection()
    Connection.Open()
    Using Transaction As DbTransaction = Connection.BeginTransaction()

        Try
            Using FirstCommand As DbCommand = Transaction.GetCommand_
                    ("INSERT INTO MyTable1 (Column1,Column2) VALUES (1,2)")
                FirstCommand.ExecuteNonQuery()
            End Using

            Using LastCommand As DbCommand = Transaction.GetCommand_
                    ("INSERT INTO MyTable2 (ColumnA,ColumnB) VALUES ('X','Y')")
                LastCommand.ExecuteNonQuery()
            End Using

            Transaction.Commit()
        Catch ex As Exception
            Transaction.Rollback()
            Throw
        End Try
    End Using
End Using

I don't see any way to make it less verbose if you want to dispose of all resources.
Also note that I didn't use GetNonQuery as it would have closed the connection prematurely.
But still a bit cleaner.

And What about DbParameters?

Bugger.
Here's the big problem with using DbCommands. Both DbCommand and DbParameter are abstract Classes.
So since the type of a DbParameter instance cannot be inferred from the usage, we have to either specify it or use Command.CreateParameter.

DbCommand Command = GetDbConnection().GetCommand("MyCommandText");
DbParameter Parameter = Command.CreateParameter();
Parameter.ParameterName = "PName";
Parameter.Value = 5;
Command.Parameters.Add(Parameter);
Command.GetNonQuery();

Dim Command as DbCommand = GetDbConnection.GetCommand("MyCommandText")
Dim Parameter as DbParameter = Command.CreateParameter
Parameter.ParameterName = "PName"
Parameter.Value = 5
Command.Parameters.Add(Parameter)
Command.GetNonQuery()

This totally not what I wanted. It's very verbose.

So what about specifying the type? Let's add an overload to GetCommand.

SqlParameter[] Parameters = { new SqlParameter { ParameterName = "P1",Value = 2 } };
DbCommand Command = GetSQLConnection().GetCommand("MyCommandText", Parameters);

Dim Parameters As SqlParameter() = {New SqlParameter With {.ParameterName = "P1", .Value = 2}}
Dim Command As DbCommand = GetSQLConnection.GetCommand("MyCommandText", Parameters)

This works fine, but only if the DbCommand actually is of the type SqlCommand.
So with this method, it's quite possible to create what might become bugs later in the application's life. And this also goes quite against the point of using a DbProviderFactory.

So instead, I had to make a wrapper for Parameters as well.
Here's one example of how it looks like, there are several overloads.

public static DbCommand AddDbParameter
  (this DbCommand Command, string ParameterName, DbType DbType, object Value)
{
    DbParameter Parameter = Command.CreateParameter();
    Parameter.ParameterName = ParameterName;
    Parameter.DbType = DbType;
    Parameter.Value = Value;
    Command.Parameters.Add(Parameter);
    return Command;
}

<extension>
Function AddDbParameter(Command As DbCommand, ParameterName As String, DbType As DbType, Value As Object) As DbCommand
    Dim Parameter As DbParameter = Command.CreateParameter()
    Parameter.ParameterName = ParameterName
    Parameter.DbType = DbType
    Parameter.Value = Value
    Command.Parameters.Add(Parameter)
    Return Command
End Function</extension>

There are of course some drawbacks with this approach.
The biggest one in my opinion is that the parameters are only positional, not named.
You also cannot use database specific Types.

So for those cases when a DbProviderFactory just isn't enough, I have also created classes for SqlAccess and OracleAccess. They are also available as downloads.
And it's very easy to build it out to other Providers as well.

Anything Else We Can Do to Simplify?

Yes, why not add some functions to skip GetCommand.
Like this:

    public static DbDataReader GetDataReader(this DbConnection Connection, 
        string CommandText, 
        CommandType CommandType = System.Data.CommandType.Text, 
        IEnumerable<DbParameter> Parameters = null)
    {
        DbCommand Command = GetCommand(Connection, CommandText, CommandType, Parameters);
        return GetDataReader(Command);
    }

<Extension>Public Function GetDataReader(Connection As DbConnection,
                              CommandText As String,
                              Optional CommandType As CommandType = System.Data.CommandType.Text,
                              Optional Parameters As IEnumerable(Of DbParameter) = Nothing) _
                                     As DbDataReader
    Dim Command As DbCommand = GetCommand(Connection, CommandText, CommandType, Parameters)
    Return GetDataReader(Command)
End Function

which can be used like this:

MyDB.GetConnection.GetDataReader("Select * From MyTable");

MyDB.GetDataReader("Select * From MyTable")

Similar functions have been added to GetScalar and GetNonQuery.

Points of Interest

Keep in mind that GetConnection() takes the first usable ConnectionString it finds in your config file.
If you have a local install of SqlServer, this first connectionstring might be "LocalSqlServer" in Machine.Config.

The code in this article also works very fine together with the code in my other article, A propertymapping Extension for DataReaders[^].
Use them together like this:

MyDB.GetConnection.GetDataReader("Select * From MyTable").AsEnumerable<MyClass>();

MyDB.GetDataReader("Select * From MyTable").AsEnumerable(of MyClass)

or:

MyDB.GetConnection.GetDataReader("Select * From MyTable").ToList<MyClass>();

MyDB.GetDataReader("Select * From MyTable").ToList(of MyClass)

History

6^th October, 2014: v1.0 First release
18^th February, 2014: v1.1 Cleaned up the mass of overloads into methods with optional parameters. Added GetScalar<T>
12^th February, 2016: v1.2 Added parameter handling and GetDataTable