Click here to Skip to main content
15,868,119 members
Articles / Programming Languages / SQL

Handling database connections more easily

Rate me:
Please Sign up or sign in to vote.
4.82/5 (19 votes)
31 Jul 2011CPOL11 min read 133.9K   60   25
Building an O/R Mapper: step 3.

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.

C#
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.

C#
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.

C#
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);

        //nested datareaders will fail
        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;
                       
                        //under most providers, this will throw. 
                        using (var innerDataReader = innerCommand.ExecuteReader())
                        {
                            while (innerDataReader.Read())
                            {
                                //print the order details
                            }
                        }
                    }
                }
            }
        }
        // run the rest of the program
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex);
    }
    finally
    {
        if (_connection != null)
        {
            _connection.Dispose();
        }
    }
}

Mistakes in this block:

  1. 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.
  2. 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.
  3. 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.
  4. More annoyingly, the approach means that the CommandBuilder code must be copied everywhere, cluttering up the code.
  5. 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.

C#
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.

C#
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)
        {
            // this will throw if it doesn't exist
            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; // A-Z are valid
                valid |= b > 96 && b < 123;   // a-z are also valid
                if (i > 0)
                {
                    valid |= b > 47 && b < 58;
                    // 0-9 are only valid if not the first char
                }
                // if the byte is a valid char use it,
                // otherwise, use modulo divide and addition
                // to make it an a-z value
                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)
            {
                //Add connection state change events if the
                //provider supports it
                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;

            // get the provider and then get the Factory Singleton
            _factory = DbProviderFactories.GetFactory(providerName);

            //some providers, don't provide an inherited DbConnectionStringBuilder
            //so if the factory call returns null, use the default.
            _connectionStringBuilder = Factory.CreateConnectionStringBuilder() ?? 
                                       new DbConnectionStringBuilder(true);
            _connectionStringBuilder.ConnectionString = connectionString;
            TestConnectionStringForMicrosoftExcelOrAccess();
            using (var conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                //add a state change event, if this one is called, it will 
                //set up the events later, so we can keep track of how many are open
                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"))
            {
                //this is a OleDb connection
                var s = _connectionStringBuilder["Extended Properties"] as string;
                if (!string.IsNullOrEmpty(s) && s.ToLower().Contains("excel"))
                {
                    //we found MS Excel
                    useSquareBrackets = true;
                }
                else
                {
                    //check for MS Acess
                    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"))
                {
                    //this is an Odbc Connection
                    var s = _connectionStringBuilder["driver"] as string;
                    if (!string.IsNullOrEmpty(s))
                    {
                        s = s.ToLower();
                        //test for either excel or access
                        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)
        {
            //Debug.WriteLine("Connection Disposed");
        }

        private void StateChange(object sender, StateChangeEventArgs e)
        {
            var connectionState = e.CurrentState;
            // Debug.WriteLine(Enum.GetName(typeof(ConnectionState), 
            //                              connectionState));
            switch (connectionState)
            {
                case ConnectionState.Open:
                    Interlocked.Increment(ref _openConnections);
                    break;
                case ConnectionState.Closed:
                case ConnectionState.Broken:
                    Interlocked.Decrement(ref _openConnections);
                    break;
                default:
                    //case ConnectionState.Connecting:
                    //case ConnectionState.Executing:
                    //case ConnectionState.Fetching:
                    break;
            }
            //  Debug.WriteLine("Open Connections :" + 
            //        Interlocked.Add(ref _openConnections, 0));
        }
    }
}

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:

SQL
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:

C#
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:

C#
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
            {
                // this is a special case even if this object
                // is NOT part of a transaction so handle it 
                // differently than other cases
                if ((commandBehavior & CommandBehavior.CloseConnection) == 
                     CommandBehavior.CloseConnection)
                {
                    // get rid of the connection
                    // so the connection won't be reused
                    // if not in a transaction
                    // and the SQLCommand is reused.
                    _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.CommandTimeout = 0;
                        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.CommandTimeout = 0;
                        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()
        {
            // While I am not going to cover it here,
            // you would get the transactions existing connection 
            // from that transaction, or if need be,
            // get a new connection for a seperate database
            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)
                    {
                        //rollback 
                    }
                    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:

C#
_connection = _connection ?? GetNewConnection();

It is equivalent to this block of code, it just makes things easier:

C#
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:

C#
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));

        //These are filled within the "switch/case"
        //statement, either directly, or thru reflection.
        //since Resharper can't tell they are being filled 
        //thru reflection, it suggests to convert them to
        //constants. DO NOT do that!!!!!

        // ReSharper disable ConvertToConstant.Local
        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;
        // ReSharper restore ConvertToConstant.Local
        private Regex _parameterNamePatternRegex;
        private string _parameterPrefix;

        public DataSourceInformation(DataTable dt)
        {
            //DataTable dt = Connection.GetSchema(
            //   DbMetaDataCollectionNames.DataSourceInformation);
            foreach (DataRow r in dt.Rows)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    string s = c.ColumnName;
                    object o = r[c.ColumnName];
                    //just for safety
                    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;
                                // (o as SupportedJoinOperators?) ??
                                //    SupportedJoinOperators.None;
                                break;
                            default:
                                FieldInfo fi = _Type.GetField("_" + s,
                                  BindingFlags.IgnoreCase | BindingFlags.NonPublic |
                                  BindingFlags.Instance);
                                if (fi != null)
                                {
                                    fi.SetValue(this, o);
                                }
                                break;
                        }
                    }
                }
                //there should only ever be a single row.
                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.

C#
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:

SQL
SELECT * from "sec"."page" WHERE "ID"=?

Which will work for any SQL92 compliant database, it will generate something proprietary:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Pengdows
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionDataReader issues? Pin
shrapnel_indie2-Jun-16 9:02
shrapnel_indie2-Jun-16 9:02 
AnswerRe: DataReader issues? Pin
shrapnel_indie6-Jun-16 8:34
shrapnel_indie6-Jun-16 8:34 
Okay. While SQL Server had no issues with the given code, The InterSystems Caché Database had issues with the existing code.

A fix, albeit not necessarily with the best cleanup is to modify the method
C#
public DbDataReader ExecuteReader(CommandBehavior commandBehavior, CommandType commandType, int? commandTimeOut)
within the SQLCommand class.

The modification is to pull the using functionality off the created command class. Meaning changing:
C#
using (var cmd = connection.CreateCommand())
{
	...
}


to

C#
var cmd = connection.CreateCommand();
...


If there is a better solution, I'd like to hear it.
-Later!
-WB-

QuestionStored Procedures Pin
Member 112950508-Dec-14 22:19
Member 112950508-Dec-14 22:19 
AnswerRe: Stored Procedures Pin
Alaric Dailey9-Dec-14 5:33
Alaric Dailey9-Dec-14 5:33 
QuestionNeed help with db connections Pin
Terri Morgan15-May-14 13:44
Terri Morgan15-May-14 13:44 
AnswerRe: Need help with db connections Pin
Alaric Dailey15-May-14 15:52
Alaric Dailey15-May-14 15:52 
GeneralRe: Need help with db connections Pin
Terri Morgan16-May-14 4:05
Terri Morgan16-May-14 4:05 
GeneralRe: Need help with db connections Pin
Alaric Dailey19-May-14 6:40
Alaric Dailey19-May-14 6:40 
QuestionInteresting for learning Pin
fravelgue3-Aug-11 3:25
fravelgue3-Aug-11 3:25 
AnswerRe: Interesting for learning Pin
Alaric Dailey3-Aug-11 4:13
Alaric Dailey3-Aug-11 4:13 
AnswerRe: Interesting for learning Pin
Alaric Dailey9-Aug-11 10:24
Alaric Dailey9-Aug-11 10:24 
QuestionWhat are your thoughts on Enterprise Library? Pin
jrtechie31-Jul-11 13:17
jrtechie31-Jul-11 13:17 
AnswerRe: What are your thoughts on Enterprise Library? Pin
Alaric Dailey31-Jul-11 15:22
Alaric Dailey31-Jul-11 15:22 
GeneralRe: What are your thoughts on Enterprise Library? Pin
jrtechie1-Aug-11 0:47
jrtechie1-Aug-11 0:47 
QuestionCompile errror in class SQLCommand Pin
ProfesorXYZ31-Jul-11 11:56
ProfesorXYZ31-Jul-11 11:56 
AnswerRe: Compile errror in class SQLCommand Pin
Alaric Dailey31-Jul-11 12:42
Alaric Dailey31-Jul-11 12:42 
GeneralRe: Compile errror in class SQLCommand Pin
ProfesorXYZ31-Jul-11 12:56
ProfesorXYZ31-Jul-11 12:56 
GeneralMy vote of 3 Pin
Eduard Lu30-Jul-11 23:23
Eduard Lu30-Jul-11 23:23 
GeneralRe: My vote of 3 Pin
Alaric Dailey31-Jul-11 0:55
Alaric Dailey31-Jul-11 0:55 
QuestionQuotePrefix, QuoteSuffix, and QuoteIdentifier Pin
PIEBALDconsult30-Jul-11 20:29
mvePIEBALDconsult30-Jul-11 20:29 
AnswerRe: QuotePrefix, QuoteSuffix, and QuoteIdentifier Pin
Alaric Dailey31-Jul-11 1:59
Alaric Dailey31-Jul-11 1:59 
GeneralRe: QuotePrefix, QuoteSuffix, and QuoteIdentifier Pin
PIEBALDconsult31-Jul-11 4:26
mvePIEBALDconsult31-Jul-11 4:26 
GeneralRe: QuotePrefix, QuoteSuffix, and QuoteIdentifier Pin
Alaric Dailey31-Jul-11 4:58
Alaric Dailey31-Jul-11 4:58 
GeneralRe: QuotePrefix, QuoteSuffix, and QuoteIdentifier Pin
PIEBALDconsult31-Jul-11 6:37
mvePIEBALDconsult31-Jul-11 6:37 
AnswerRe: QuotePrefix, QuoteSuffix, and QuoteIdentifier Pin
Alaric Dailey31-Jul-11 7:50
Alaric Dailey31-Jul-11 7:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.