Introduction
Please remember to vote on this article.
In this article, I am going to bring together several pieces of code to help handle database connections transparently. I will be referencing code and ideas
covered in some of my other articles including:
By the end of this article, I will show a group of objects that allow simple use of a database, pushing the vast majority of complicated code into a few simple lines.
For example, using these objects, loading the connection string from the app.config/web.config, getting the provider string, loading the provider factory
will be hidden within a simple call.
var sc = new SQLCommand("dsn");
Furthermore, all the tedium of handling the numerous objects to do a simple query are eliminated. The following block with the above is all that will be needed
to get a strongly typed row count.
sc.CommandText.Append("SELECT count(ID) from MyTable");
var count = sc.ExecuteScalar<int>();
Used correctly, this set of objects can also greatly increase your application's ability to use any database as a backend.
Background
Reusable code is the goal of many libraries, and many programmers. Sometimes in an effort to separate the code and make things more flexible, code bits end up being
written over and over and over. A perfect example is the code for executing a SQL statement and returning a single value using ExecuteScalar.
Let's take a look at a typical example.
ppublic const string connectionString =
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI";
public static SqlConnection _connection = null;
public static void Main(string[] args)
{
try
{
_connection = new SqlConnection(connectionString);
_connection.Open();
int count;
using (var cmd = new SqlCommand("select count(*) from [Order Details]",
_connection))
{
count = (int)cmd.ExecuteScalar();
}
Debug.WriteLine("Number of records in Order Details: " + count);
using (var outerCommand = _connection.CreateCommand())
{
outerCommand.CommandText = "select OrderID from Orders";
using (var outerDataReader = outerCommand.ExecuteReader())
{
while (outerDataReader.Read())
{
int orderid = outerDataReader.GetInt32(0);
using (var innerCommand = _connection.CreateCommand())
{
innerCommand.CommandText =
"select * from [Order Details] where OrderID=" + orderid;
using (var innerDataReader = innerCommand.ExecuteReader())
{
while (innerDataReader.Read())
{
}
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
if (_connection != null)
{
_connection.Dispose();
}
}
}
Mistakes in this block:
- The data provider is hard coded; this will make changing to another provider very difficult, especially since this block uses specific functionality from
the
SqlClient provider. - The connection string is hard coded, ouch. Even if it isn't hard coded, people store the connection strings everywhere but where they are supposed to be,
in the
ConnectionStrings area of your app.config. - Leaving the connection open for the entire application to be reused. This makes it very hard to recover if there is a network glitch. Some providers, currently Npgsql,
springs to mind, don't support the event that would tell you when the connection state changes. So much for using the
StateChange event as a way
to fix that. I also provided an example of the problem of nested data readers when reusing the same connection. Finally, it will prevent you from taking advantage
of connection pooling. - More annoyingly, the approach means that the
CommandBuilder code must be copied everywhere, cluttering up the code. - The SQL isn't taking advantage of parameters.
I have covered lots of this in previous articles. In my article on Secure Password Authentication, I offered a simple static database class that automatically handled some
of this. However, one of the criticisms I received was that it was too "old school". The code wasn't meant to be production code, though I
had used a much more functional version in the past. When I went to refactor my original code, for my articles, it occurred to me that there was some unnecessary
overhead because of the way I was going about some of the things. So the goals here are:
- Hide the constant building and disposing of connections and commands
- Make transparent use of connection pooling
- Be able to load and connect to any database for any installed provider
- Store the datasource information for each connection
Building the class
In my article Using Information from the .NET DataProvider,
I covered filling in a data source information class from the data. In my article Secure Password
Authentication Explained Simply, I created a static database class, but it didn't support connections to multiple databases, this is not to say that
it didn't support multiple connections.
At this point, I would like to point out that a static database class is not a class of static variables. The problem with static variables is that they are essentially
global variables, so they are subject to change when you aren't expecting it. This problem is compounded when you throw in multiple threads. I bring this
up because one of the main uses for .NET code is ASP.NET web applications, and a web application is truly a multithreaded application, each page request gets a thread
from the theadpool, runs through the page, then returns the thread to the pool. This means that static variables are great for "write once read many"
variables, perfect for things like strings, especially if properly locked. However, if you make the mistake of making your ASP.NET application use a static
database connection variable, you run into a host of problems:
- Locking the connection properly means that while the connection is in use, all other people using the site must wait for the pending operation to finish. Transactions
become impossible, because you can't separate out who is doing what.
- You can't ever close the connection, because the connection would likely be in the wrong state for the next person, and because it is working in a multithreaded
environment, checking the connection state prior to use doesn't help. Even worse, should the database disconnect, the provider could still show the connection open.
- You eliminate the ability to use multiple datasets at the same time.
In short, while static variables are fine for some uses, database connections are not one of them. A list of connection strings is a great example of something that
might be useful, especially once cleaning of the string has been done, simply because you wouldn't want to read the config, parse the connection string, then use it every single time.
The static database class in my previous article wraps much of the tedious code in dealing with databases in .NET. Without code similar to this, you have to read the connection string,
hopefully the provider, get the factory class, create a connection and open the connection, and create a command before you do anything else. Of course,
I have seen people skip reading the factory, and wrap the rest into a class. That, however, isn't really taking advantage of what is available in .NET.
I want my class to serve certain goals:
- Handle reading the connection string, and use the
DbConnectionStringBuilder object to validate it. - Handle reading the provider and getting the Singleton instance for the factory.
- Use the factory to create all new objects,
DbConnectionStringBuilder, DbConnection, DbDataAdapter,
DbDataReader, and especially DbParameter. - Create a connection that I can keep open should I wish for transactions, otherwise it closes the connection for me, returning it to the pool.
- Exposes most if not all of the functionality of the underlying objects, the goal is to simplify, not to eliminate, functionality.
- Use my
DataSourceInformation class to let me wrap object names with the proper escaping characters,
allowing the use of keywords and special characters as object names (like a table named group). - Use my
DataSourceInformation class to let me determine which character is to be used for.
All too often, wrapper classes remove functionality, especially with database wrapping code. It tends to force you to use one connection string, it limits what you
can do, most often removing the use of parameters.
I believe you shouldn't overlook what is already handed to you in the base libraries. So the first thought is to wrap certain functions and then return a connected
DbCommand object. This would easily address many of my objectives. Unfortunately, it becomes very difficult to work with parameters,
because if we are using the DbCommand rather than a derived object, there is no way to add a parameter without getting a reference to the factory again. Furthermore,
string concatenation on a string can be very slow when using code like this.
cmd.CommandText = "SELECT ";
cmd.CommandText += "Colum1, ";
cmd.CommandText += "Colum2, ";
cmd.CommandText += "Colum3, ";
cmd.CommandText += "Colum4 ";
cmd.CommandText += "FROM " + WrapObjectName("dbo.TableName") + " a WHERE ";
cmd.CommandText += WrapObjectName("a.ID") + " = " +
GetParameterChar("nameIfNamedParamsAreSupported");
So I want a StringBuilder object to do string manipulation with, and so now it becomes a new object.
The next step is coming up with a name, SQLCommand is all I could come up with, because it embodies what I am doing and what I am wrapping.
Next, I need an object to hold a few pieces of information, my DataSourceInformation object, the connection string after it
has been validated, and the factory that I can use to create new objects, and a dictionary that I can use to fill and access it using the connection string name.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Security.Cryptography;
using System.Text;
using System.Threading;
namespace CodeProjectArticles
{
public class DataSource
{
private static readonly RandomNumberGenerator
_random = RandomNumberGenerator.Create();
private string _name;
private DataSourceInformation _information;
private DbCommandBuilder _commandBuilder;
private DbProviderFactory _factory;
private DbConnectionStringBuilder _connectionStringBuilder;
private char _compositeIdentifierSeparatorPattern = ' ';
private bool _trackOpenConnections;
private string _seperator;
private string _quoteSuffix;
private string _quotePrefix;
private int _openConnections;
public DataSource(string name)
{
var css = ConfigurationManager.ConnectionStrings[name];
Initialize(name, css.ConnectionString, css.ProviderName);
}
public DataSource(string name, string connectionString,
string providerName)
{
Initialize(name, connectionString, providerName);
}
public string Name
{
get { return _name; }
}
public DataSourceInformation Information
{
get { return _information; }
}
public DbProviderFactory Factory
{
get { return _factory; }
}
public DbConnectionStringBuilder ConnectionStringBuilder
{
get { return _connectionStringBuilder; }
}
public string ConnectionString
{
get { return _connectionStringBuilder.ConnectionString; }
}
private DbCommandBuilder CommandBuilder
{
get { return _commandBuilder ??
(_commandBuilder = Factory.CreateCommandBuilder()); }
}
private char CompositeIdentifierSeparatorPattern
{
get
{
if (_compositeIdentifierSeparatorPattern == ' ')
{
var seperator = '.';
var s = _information.CompositeIdentifierSeparatorPattern;
if (!string.IsNullOrEmpty(s))
{
seperator = s.Replace("\\", string.Empty)[0];
}
_compositeIdentifierSeparatorPattern = seperator;
}
return _compositeIdentifierSeparatorPattern;
}
}
private string JoinSeperator
{
get
{
if (string.IsNullOrEmpty(_seperator))
{
_seperator = string.Concat(QuoteSuffix,
CompositeIdentifierSeparatorPattern, QuotePrefix);
}
return _seperator;
}
}
private string QuoteSuffix
{
get
{
if (string.IsNullOrEmpty(_quoteSuffix))
{
_quoteSuffix = CommandBuilder.QuoteSuffix;
if (string.IsNullOrEmpty(_quoteSuffix))
{
_quoteSuffix = "\"";
}
_quoteSuffix = _quoteSuffix.Trim();
}
return _quoteSuffix;
}
}
private string QuotePrefix
{
get
{
if (string.IsNullOrEmpty(_quotePrefix))
{
_quotePrefix = CommandBuilder.QuotePrefix;
if (string.IsNullOrEmpty(_quotePrefix))
{
_quotePrefix = "\"";
}
_quotePrefix = _quotePrefix.Trim();
}
return _quotePrefix;
}
}
public string GenerateNewParameterName()
{
var len = Information.ParameterNameMaxLength;
return GenerateNewParameterName(len);
}
public string GenerateNewParameterName(int length)
{
if (length == 0 || length > 8)
{
length = 8;
}
var buffer = new byte[length];
_random.GetBytes(buffer);
var sb = new StringBuilder();
var i = 0;
foreach (var b in buffer)
{
var valid = b > 64 && b < 91;
valid |= b > 96 && b < 123;
if (i > 0)
{
valid |= b > 47 && b < 58;
}
var c = !valid ? (char)((b % 26) + 'a') : (char)b;
sb.Append(c);
i++;
}
return sb.ToString();
}
public string WrapObjectName(string objectName)
{
if (!string.IsNullOrEmpty(objectName))
{
var quoteSuffix = QuoteSuffix;
var quotePrefix = QuotePrefix;
if (objectName.Contains(quotePrefix) ||
objectName.Contains(quoteSuffix))
{
objectName = UnwrapObjectName(objectName);
}
var ss = objectName.Split(CompositeIdentifierSeparatorPattern);
if (ss.Length > 1)
{
objectName = string.Join(JoinSeperator, ss);
}
objectName =
string.Concat(quotePrefix, objectName, quoteSuffix);
}
return objectName;
}
public string UnwrapObjectName(string objectName)
{
if (!string.IsNullOrEmpty(objectName))
{
var ss = objectName.Split(CompositeIdentifierSeparatorPattern);
var quotePrefix = QuotePrefix;
var quoteSuffix = QuoteSuffix;
if (ss.Length > 1 && quoteSuffix.Length > 0 &&
quotePrefix.Length > 0)
{
var list = new List<string>();
foreach (var s in ss)
{
var tmp = s;
var len = tmp.Length;
if (len > 2)
{
if (tmp.Substring(0, 1) == quotePrefix &&
tmp.Substring(len - 1, 1) == quoteSuffix)
{
tmp = tmp.Substring(1, len - 2);
}
}
list.Add(tmp);
}
list.CopyTo(ss);
}
objectName = string.Join(
CompositeIdentifierSeparatorPattern.ToString(), ss);
}
return objectName;
}
public DbConnection GetNewConnection()
{
var conn = Factory.CreateConnection();
conn.ConnectionString = _connectionStringBuilder.ConnectionString;
if (_trackOpenConnections)
{
conn.StateChange += StateChange;
}
conn.Disposed += ConnDisposed;
conn.Open();
return conn;
}
public string GetParameterName(string parameterName)
{
var s = parameterName;
var l = Information.ParameterNameMaxLength;
if (l < 1)
{
return Information.ParameterMarker;
}
if (l < s.Length)
{
s = s.Substring(0, l);
}
var reg = Information.ParameterNamePatternRegex;
if (!reg.IsMatch(s))
{
s = GenerateNewParameterName();
}
return string.Concat(Information.ParameterMarker, s);
}
private void Initialize(string name,
string connectionString, string providerName)
{
_name = name;
_factory = DbProviderFactories.GetFactory(providerName);
_connectionStringBuilder = Factory.CreateConnectionStringBuilder() ??
new DbConnectionStringBuilder(true);
_connectionStringBuilder.ConnectionString = connectionString;
TestConnectionStringForMicrosoftExcelOrAccess();
using (var conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.StateChange += ConnStateChange;
conn.Open();
_information = new DataSourceInformation(
conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation));
}
}
private void TestConnectionStringForMicrosoftExcelOrAccess()
{
var useSquareBrackets = false;
var name = _connectionStringBuilder.GetType().FullName ?? string.Empty;
if (name.StartsWith("System.Data.OleDb"))
{
var s = _connectionStringBuilder["Extended Properties"] as string;
if (!string.IsNullOrEmpty(s) && s.ToLower().Contains("excel"))
{
useSquareBrackets = true;
}
else
{
s = _connectionStringBuilder["Provider"] as string ?? string.Empty;
useSquareBrackets = s.Contains("MS Remote");
if (!useSquareBrackets)
{
s = (_connectionStringBuilder["Data Source"]
as string ?? string.Empty).ToLower();
useSquareBrackets = s.EndsWith(".accdb") || s.EndsWith(".mdb");
}
}
}
else
{
if (name.StartsWith("System.Data.Odbc"))
{
var s = _connectionStringBuilder["driver"] as string;
if (!string.IsNullOrEmpty(s))
{
s = s.ToLower();
useSquareBrackets =
s.Contains("*.xls") || s.Contains("*.mdb");
}
}
}
if (useSquareBrackets)
{
_quotePrefix = "[";
_quoteSuffix = "]";
}
}
private void ConnStateChange(object sender, StateChangeEventArgs e)
{
_trackOpenConnections = true;
}
private void ConnDisposed(object sender, EventArgs e)
{
}
private void StateChange(object sender, StateChangeEventArgs e)
{
var connectionState = e.CurrentState;
switch (connectionState)
{
case ConnectionState.Open:
Interlocked.Increment(ref _openConnections);
break;
case ConnectionState.Closed:
case ConnectionState.Broken:
Interlocked.Decrement(ref _openConnections);
break;
default:
break;
}
}
}
}
The entirety of this class is dedicated to keeping track of all that "stuff" you should be handling when you are dealing with a database. This includes:
- getting data like
QuotePrefix and QuoteSuffix from the appropriate command builder - getting the correct
DbProviderFactory - functions to correctly wrap entity names with those quotes
- validating parameter names, using the Regular Expression that is supplied by the provider
- generating valid parameter names if named parameters are supported
- counting open connections (if the events are supported)
- loading information from the
ConnectionStrings area of the .config file - allowing the use of built-in connection strings (allowing you to store the connection information somewhere else)
You will see a theme start to appear, lazy load what I need (load it only when it's needed), validate it, then keep in memory so I don't have to do it again and
again. The concern about using a lot of memory with this approach is valid, but for most applications, you have a very small number of connections, 5 is the
most I have ever seen in an application (connection to three different database types); in the vast majority of cases, it is usually only a single connection.
Next, I need a class that allows me to use this information, wrapping all of the standard functions. In .NET, the existing DbCommand object contains
three very
easy functions: ExecuteScalar, ExecuteReader, and ExecuteNonQuery. For some unknown reason, they didn't choose
to implement a ExecuteDataSet or ExecuteDataTable, so I will add my own. Also, ExecuteScalar fails
on the TimesTen database every
single time, with an error that indicates it is trying to go through additional record sets. To alleviate this error,
I handle it by doing an ExecuteReader, specifying the behavior I want "return a single row, and a single column".
If the database and underlying provider obey this, then the SQL statement could be:
SELECT * FROM TableWithAMillionRows
Only the first column from the first row would be returned. Still it is better to write your SQL so that you return only what you want.
I wanted to keep my parameters separate from the DbCommand so I created a simple override for parameters:
using System.Collections.Generic;
using System.Data.Common;
namespace CodeProjectArticles
{
public class ParameterDictionary : Dictionary<string, DbParameter>
{
public void Add(DbParameter item)
{
Add(item.ParameterName, item);
}
}
}
Now for the class that does all the work:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading;
namespace CodeProjectArticles
{
public sealed class SQLCommand : IDisposable
{
private static readonly Dictionary<string, DataSource>
_dataSourceDictionary = new Dictionary<string, DataSource>();
private static readonly object _syncObject = new object();
private readonly ParameterDictionary _parameters =
new ParameterDictionary();
private readonly StringBuilder _commandText = new StringBuilder();
private DataSource _dataSource;
private int _disposed;
private DbConnection _connection;
public SQLCommand(string connectionName)
{
Initialize(connectionName);
}
public SQLCommand(string connectionName, string connectionString,
string providerName)
{
DataSource dataSource = null;
lock (_syncObject)
{
if (!_dataSourceDictionary.TryGetValue(
connectionName, out dataSource))
{
dataSource = new DataSource(connectionName,
connectionString, providerName);
_dataSourceDictionary.Add(connectionName, dataSource);
}
}
_dataSource = dataSource;
}
~SQLCommand()
{
Dispose(false);
}
public StringBuilder CommandText
{
get { return _commandText; }
}
public bool InTransaction
{
get { return false; }
}
public ParameterDictionary Parameters
{
get { return _parameters; }
}
public void Dispose()
{
Dispose(true);
}
public void Initialize(string connectionName)
{
DataSource dataSource = null;
lock (_syncObject)
{
if (!_dataSourceDictionary.TryGetValue(connectionName,
out dataSource))
{
dataSource = new DataSource(connectionName);
_dataSourceDictionary.Add(connectionName, dataSource);
}
}
_dataSource = dataSource;
}
public DbDataReader ExecuteReader()
{
var behavior = InTransaction ?
CommandBehavior.Default : CommandBehavior.CloseConnection;
return ExecuteReader(behavior, CommandType.Text, 30);
}
public DbDataReader ExecuteReader(CommandBehavior commandBehavior,
CommandType commandType, int? commandTimeOut)
{
var conn = GetConnection();
try
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = _commandText.ToString();
cmd.CommandType = commandType;
cmd.CommandTimeout = commandTimeOut ?? cmd.CommandTimeout;
try
{
foreach (var parameter in Parameters.Values)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteReader(commandBehavior);
}
finally
{
cmd.Parameters.Clear();
}
}
}
finally
{
if ((commandBehavior & CommandBehavior.CloseConnection) ==
CommandBehavior.CloseConnection)
{
_connection = null;
}
}
}
public object ExecuteScalar()
{
return ExecuteScalar(CommandType.Text, null);
}
public object ExecuteScalar(CommandType commandType)
{
return ExecuteScalar(commandType, null);
}
public object ExecuteScalar(CommandType commandType,
int? commandTimeout)
{
try
{
var behavior = InTransaction ? CommandBehavior.Default :
CommandBehavior.CloseConnection;
behavior |= CommandBehavior.SingleRow |
CommandBehavior.SingleResult;
using (var dr = ExecuteReader(behavior,
commandType, commandTimeout))
{
dr.Read();
return dr.GetValue(0);
}
}
finally
{
DisposeConnection();
}
}
public T ExecuteScalar<T>()
{
return (T)ExecuteScalar(CommandType.Text, null);
}
public T ExecuteScalar<T>(CommandType commandType)
{
return (T)ExecuteScalar(commandType, null);
}
public T ExecuteScalar<T>(CommandType commandType, int? commandTimeout)
{
return (T)ExecuteScalar(commandType, commandTimeout);
}
public int ExecuteNonQuery(CommandType commandType)
{
return ExecuteNonQuery(commandType, null);
}
public int ExecuteNonQuery(CommandType commandType, int? commandTimeout)
{
var conn = GetConnection();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = _commandText.ToString();
cmd.CommandTimeout = commandTimeout ?? cmd.CommandTimeout;
cmd.CommandType = commandType;
try
{
foreach (var parameter in Parameters.Values)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteNonQuery();
}
finally
{
cmd.Parameters.Clear();
}
}
}
public DataSet ExecuteDataSet()
{
var conn = GetConnection();
try
{
using (var cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = _commandText.ToString();
cmd.CommandType = CommandType.Text;
using (var da = _dataSource.Factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
var dt = new DataSet();
da.Fill(dt);
return dt;
}
}
finally
{
cmd.Parameters.Clear();
}
}
}
finally
{
DisposeConnection();
}
}
public DataTable ExecuteDataTable()
{
var conn = GetConnection();
try
{
using (var cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = _commandText.ToString();
cmd.CommandType = CommandType.Text;
using (var da = _dataSource.Factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
var dt = new DataTable();
da.Fill(dt);
return dt;
}
}
finally
{
cmd.Parameters.Clear();
}
}
}
finally
{
DisposeConnection();
}
}
public void BeginTransaction()
{
throw new NotImplementedException();
}
public void CommitTransaction()
{
throw new NotImplementedException();
}
public void RollbackTransaction()
{
throw new NotImplementedException();
}
public string WrapObjectName(string objectName)
{
return _dataSource.WrapObjectName(objectName);
}
public DbParameter CreateParameter(DbType dbType,
string name, object value)
{
var p = _dataSource.Factory.CreateParameter();
p.ParameterName = name;
p.Value = value;
p.DbType = dbType;
return p;
}
public string GenerateNewParameterName()
{
return _dataSource.GenerateNewParameterName();
}
public string GetParameterName(DbParameter dbParameter)
{
return _dataSource.GetParameterName(dbParameter.ParameterName);
}
public string GetParameterName(string parameterName)
{
return _dataSource.GetParameterName(parameterName);
}
public DbParameter CreateParameter(DbType dbType, object value)
{
return CreateParameter(dbType,
_dataSource.GenerateNewParameterName(), value);
}
private DbConnection GetConnection()
{
if (_connection != null && _connection.State ==
ConnectionState.Closed)
{
DisposeConnection();
}
_connection = _connection ?? GetNewConnection();
return _connection;
}
private void DisposeConnection()
{
if (!InTransaction && _connection != null)
{
_connection.Dispose();
_connection = null;
}
}
private void Dispose(bool disposing)
{
if (Interlocked.Increment(ref _disposed) == 1)
{
if (disposing)
{
GC.SuppressFinalize(this);
}
if (_connection != null)
{
if (InTransaction)
{
}
DisposeConnection();
}
_dataSource = null;
}
Interlocked.Exchange(ref _disposed, 1);
}
private DbConnection GetNewConnection()
{
return _dataSource.GetNewConnection();
}
}
}
A few things to notice are the Generic ExecuteScalar overloads, the lazy loading of the configuration, and the (intentionally) partial implementation of Transactions
(I chose to leave that either to the reader, or to a later article). Much of this class is self-explanatory, some of it is not. If you aren't familiar
with the ?? operator in this block:
_connection = _connection ?? GetNewConnection();
It is equivalent to this block of code, it just makes things easier:
if(_connection == null)
{
_connection = GetNewConnection();
}
Other interesting points include, that I chose to implement IDisposable so that it can be used in a using block. The default behavior
is to close the connection when using a DbDataReader, this prevents open connections lying around.
I had to update my DataSourceInformation class so here is the modified version:
using System;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text.RegularExpressions;
namespace CodeProjectArticles
{
public class DataSourceInformation
{
private static readonly Type _Type = typeof(DataSourceInformation);
private static readonly Type _IdentifierCaseType =
Enum.GetUnderlyingType(typeof(IdentifierCase));
private static readonly Type _GroupByBehaviorType =
Enum.GetUnderlyingType(typeof(GroupByBehavior));
private static readonly Type _SupportedJoinOperatorsType =
Enum.GetUnderlyingType(typeof(SupportedJoinOperators));
private readonly string _compositeIdentifierSeparatorPattern = string.Empty;
private readonly string _dataSourceProductName = string.Empty;
private readonly string _dataSourceProductVersion = string.Empty;
private readonly string _dataSourceProductVersionNormalized = string.Empty;
private readonly GroupByBehavior _groupByBehavior;
private readonly string _identifierPattern = string.Empty;
private readonly IdentifierCase _identifierCase;
private readonly bool _orderByColumnsInSelect = false;
private readonly string _parameterMarkerFormat = string.Empty;
private readonly string _parameterMarkerPattern = string.Empty;
private readonly Int32 _parameterNameMaxLength = 0;
private readonly string _parameterNamePattern = string.Empty;
private readonly string _quotedIdentifierPattern = string.Empty;
private readonly Regex _quotedIdentifierCase;
private readonly string _statementSeparatorPattern = string.Empty;
private readonly Regex _stringLiteralPattern;
private readonly SupportedJoinOperators _supportedJoinOperators;
private Regex _parameterNamePatternRegex;
private string _parameterPrefix;
public DataSourceInformation(DataTable dt)
{
foreach (DataRow r in dt.Rows)
{
foreach (DataColumn c in dt.Columns)
{
string s = c.ColumnName;
object o = r[c.ColumnName];
if (o == DBNull.Value)
{
o = null;
}
if (!string.IsNullOrEmpty(s) && o != null)
{
switch (s)
{
case "QuotedIdentifierCase":
_quotedIdentifierCase = new Regex(o.ToString());
break;
case "StringLiteralPattern":
_stringLiteralPattern = new Regex(o.ToString());
break;
case "GroupByBehavior":
o = Convert.ChangeType(o, _GroupByBehaviorType);
_groupByBehavior = (GroupByBehavior)o;
break;
case "IdentifierCase":
o = Convert.ChangeType(o, _IdentifierCaseType);
_identifierCase = (IdentifierCase)o;
break;
case "SupportedJoinOperators":
o = Convert.ChangeType(o, _SupportedJoinOperatorsType);
_supportedJoinOperators = (SupportedJoinOperators)o;
break;
default:
FieldInfo fi = _Type.GetField("_" + s,
BindingFlags.IgnoreCase | BindingFlags.NonPublic |
BindingFlags.Instance);
if (fi != null)
{
fi.SetValue(this, o);
}
break;
}
}
}
break;
}
}
public string CompositeIdentifierSeparatorPattern
{
get { return _compositeIdentifierSeparatorPattern; }
}
public string DataSourceProductName
{
get { return _dataSourceProductName; }
}
public string DataSourceProductVersion
{
get { return _dataSourceProductVersion; }
}
public string DataSourceProductVersionNormalized
{
get { return _dataSourceProductVersionNormalized; }
}
public GroupByBehavior GroupByBehavior
{
get { return _groupByBehavior; }
}
public string IdentifierPattern
{
get { return _identifierPattern; }
}
public IdentifierCase IdentifierCase
{
get { return _identifierCase; }
}
public bool OrderByColumnsInSelect
{
get { return _orderByColumnsInSelect; }
}
public string ParameterMarkerFormat
{
get { return _parameterMarkerFormat; }
}
public string ParameterMarkerPattern
{
get { return _parameterMarkerPattern; }
}
public int ParameterNameMaxLength
{
get { return _parameterNameMaxLength; }
}
public string ParameterNamePattern
{
get { return _parameterNamePattern; }
}
public string QuotedIdentifierPattern
{
get { return _quotedIdentifierPattern; }
}
public Regex QuotedIdentifierCase
{
get { return _quotedIdentifierCase; }
}
public string StatementSeparatorPattern
{
get { return _statementSeparatorPattern; }
}
public Regex StringLiteralPattern
{
get { return _stringLiteralPattern; }
}
public SupportedJoinOperators SupportedJoinOperators
{
get { return _supportedJoinOperators; }
}
public Regex ParameterNamePatternRegex
{
get { return _parameterNamePatternRegex ??
(_parameterNamePatternRegex = new Regex(ParameterNamePattern)); }
}
public string ParameterMarker
{
get
{
if (string.IsNullOrEmpty(_parameterPrefix))
{
_parameterPrefix = _parameterNameMaxLength != 0
? ParameterMarkerPattern.Substring(0, 1)
: ParameterMarkerFormat;
}
return _parameterPrefix;
}
}
}
}
Using the code
Finally, the example of how to use this, and this is where all this work seems useful.
using (var sc = new SQLCommand("dsn"))
{
var g = new Guid("2ac385a5-7843-4027-a586-dd4e9db8e72b");
sc.CommandText.AppendFormat("SELECT * from {0}",
sc.WrapObjectName("sec.page"));
sc.CommandText.AppendFormat(" WHERE {0}=",
sc.WrapObjectName("ID"));
var p = sc.CreateParameter(DbType.Guid, g);
sc.CommandText.Append(sc.GetParameterName(p));
sc.Parameters.Add(p);
using (var dr = sc.ExecuteReader())
{
while (dr.Read())
{
Debug.WriteLine(dr.GetGuid(0).ToString());
}
}
}
This example is super simple, the new SQLCommand loads all the information from the web.config or app.config. The CommandText is built using
WrapObjectName, and creates a parameter name, and if the database supports it, inserts the named parameter, and if not, it inserts a positional parameter. This allows me to change
from one database to another (this example works with MS SQL via SqlClient, OLEDB, and ODBC, PostgreSQL via OLEDB, ODBC, and NpgSQL). There are other things that
can be done to make it even more portable, but that is for another article.
The CreateParameter call uses the overload without the parameter name, this creates a valid random name. This is very useful for creating SQL
statements with lots of parameters. I don't want to get into named parameters and positional parameters but, except to say that while named
parameters can be reused inside the statement, positional parameters cannot be; if you want your code to be ultimately database independent, then assume you are using positional parameters.
If you put a breakpoint at the ExecuteReader line, you will see that it generates different SQL, it could be:
SELECT * from "sec"."page" WHERE "ID"=?
Which will work for any SQL92 compliant database, it will generate something proprietary:
SELECT * from [sec].[page] WHERE [ID]=@ggatndbp
Which works on SQL Server.
Points of interest
Nothing can solve every problem, this won't solve not parameterizing your SQL (but it makes it easy to do it right), or writing bad SQL, or screwing up the position of parameters.
What it does do is solve lots of the missteps, like leaving connections open.
The one thing I wasn't able to design in nicely is connected datasets/datatables. I personally don't use datatables or datasets too often,
datareaders are faster, so it's not something I care to spend time on. Especially since on the web, once the page is rendered, your connected datatable is disconnected.
The SQLCommand can be used and reused without creating new ones. Because I am clearing and disposing the internal DbCommand objects, the entire
statement can be reused.
I chose not to use the existing ParameterCollection object so that parameters can be accessed easily after adding them.
