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