System.Data.Common data connections: Part 1 - Connecting with ConnectionStringSettings

This is a demonstration of how to connect to data sources using the DB provider-based objects built into the .NET 2.0 framework. I've noticed a bit of confusion around programming for the new structures, so I'm going to put together a little guide on how to access data with this newly built-in, provider-driven model.

In the .NET 2.0 data objects, a new namespace has been born....System.Data.Common. Its purpose is to abstract data logic from the code interaction with a specific database. This method is not new, but it is much easier to implement in the 2.0 framework and it provides a 'patterned' way of development.

IMO, I highly recommend using the abstract class model in every scenario of data-driven development. Much like when everyone in the office buys a lottery ticket and I, the "anti-gambler", buy a ticket; it's purely preventative. I don't want to go down when everyone else wins, and you don't want to rewrite your data layer when the boss asks how long it will take to implement the same app with an Oracle DB.

Enough of the rant and down to code.

There are a few new classes that provide the construct for this kind of development:

• System.Data.Common.DbProviderFactories - provides a means of initilizing the appropriate concrete provider
• System.Data.Common.DbProviderFactory - the initialized concrete provider
• System.Configuration.ConnectionStringSettings - provides a means of storing/retrieving a data connection string and the provider to initialize.

First things first, let's talk about the Connection Strings. The connection string format in the configuration file is the following:

 <connectionStrings>
    <add name="DataConnectionString"
        connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
                Data Source=|DataDirectory|\AdventureWorks.mdb"
        providerName="System.Data.OleDb" />
</connectionStrings>

The parts of the System.Configuration.ConnectionStringSettings object are:

• Name - the name of the connection string that is used for looking up the ConnectionStringSettings object
• ConnectionString - the connection string that is to be passed into the proper DB connection object
• ProviderName - the data provider factory name which will initialize a concrete instance of the appropriate DB connection class

NOTE: Before demonstrating the data provider factory connection method, it's important to note that if you are never going to switch between providers (Oracle, MS SQL Server, MySQL, OleDB), then the provider factory method can be skipped and you can simply use the connection string with its corresponding provider (ex: new SqlConnection(ConnectionString)).

The following code illustrates how to create a DB connection using the data provider classes. Notice that this connection method provides an approach to getting a connection from an already created DbProviderFactory. The idea is to create a collection of already initialized providers, then all you are calling at runtime is the GetConnection() method of the DataManager class.  Additionally, IMO, I believe the returned connection should be stored in a local variable and reused rather than disposed.

 
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
 
public static class DataManager {
 
    public static Dictionary<string, DataConnection> _dbConnections;
 
    static DataManager() {
        // initialize the db factories
        _dbConnections = new Dictionary<string, DataConnection>(
            ConfigurationManager.ConnectionStrings.Count);
 
        foreach (ConnectionStringSettings cstring in
            ConfigurationManager.ConnectionStrings) {
 
            // initialize a new DbProviderFactory
            DbProviderFactory factory =
                DbProviderFactories.GetFactory(cstring.ProviderName);
 
            // Initialize a new DataConnection object
            DataConnection connection =
                new DataConnection(cstring.ConnectionString, factory);
 
            // add the DataConnection to the dictionary
            // names will be unique because they are unique for
            // the ConnectionStringSettingsCollection object
            _dbConnections.Add(cstrings.Name.ToLower(), connection);
        }
    }
    /// <summary>
    /// Gets a connection using the configuration
    /// connection string name.
    /// </summary>
    /// <param name="connectionStringConfigName">
    /// The name of the connection in the configuration file.
    /// </param>
    /// <returns>
    /// An instantiated DbConnection with the applied connection string.
    /// </returns>
    public static DbConnection GetConnection(
        string connectionStringConfigName) {

        if (!_dbConnections.ContainsKey(
            connectionStringConfigName.ToLower()))
            throw new ConfigurationException(
                "The data provider could not be initialized. "+
                "Please recheck the configuration file.");
 
        DataConnection cn = _dbConnections[
            connectionStringConfigName.ToLower()];
        return cn.CreateConnection();
    }
 
    private class DataConnection {
        string _connectionString;
        DbProviderFactory _dbFactory;
 
        internal DataConnection(
            string connectionString,
            DbProviderFactory factory) {
 
            _connectionString = connectionString;
            _dbFactory = factory;
        }
        public DbConnection CreateConnection() {
            DbConnection connection = _dbFactory.CreateConnection();
            connection.ConnectionString = ConnectionString;
            return connection;
        }
    }
}

If you like this approach, I am going to go on about data connections in a future "Part 2" blog entry. I will go over the idea of creating a data provider for multiple data sources using an abstract factory provider.