Don't hard code your DataProviders

Introduction 

I am tired of seeing people writing their own data access layers and wrappers to enable "simplified" code.  The thing is, they rarely actually simplify code. Instead, I see new configuration sections, whole configuration files, and entirely new object structures to somehow make things better when .NET framework offers most of the functionality I see written over and over again. 

So this article is to make things more clear, and truly simplified.

Bits

Configuration File 

In the App.Config or Web.Config there is an available section called connectionStrings. Inside that is add elements, these are individual connections to databases. Each add element has 3 important bits name,connectionString, and providerName.

name is obvious, this is the name of the connection.

connectionString is also obvious, this is the connection string.

providerName is not obvious and everyone seems to miss it. This allows us to DYNAMICALLY create the objects we need, using ANY properly installed DataProvider. I will come back to this in a moment.

Code bits

Starting with .NET 2.0, all .NET DataProviders inherit from a certain set of classes found in System.Data.Common. All of these can be created dynamically from the provider factory. The following table is a quick cross reference between the objects that are more familiar with the base classes that we need to reference.

Base object	SqlClient object	OleDb Object
DbConnection	SqlConnection	OleDbConnection
DbCommand	SqlCommand	OleDbCommand
DbParameter	SqlParameter	OleDbParameter
DbConnectionStringBuilder	SqlConnectionStringBuilder	OleDbConnectionStringBuilder
DbProviderFactory	SqlClientFactory	OleDbFactory

There are other classes but these are the ones I am going to touch on. Lets get started. 

Using the bits

Configuration

A simple App.Config with example connections for most popular databases: SQL Server, Oracle, PostgresSQL, MySQL and MS Access.

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <!-- all your application settings -->
  </appSettings>
  <connectionStrings>
    <add name="blah" providerName="System.Data.SqlClient" connectionString="Data Source=(local);Initial Catalog=cars;Integrated Security=SSPI"/>
    <!-- examples of other providers OleDb, Oracle (using TNS.ORA), MySQL and Npgsql with sample connectionstrings from connectionstrings.com -->
    <!--    <add name="blah" providerName="Npgsql" connectionString="Server=127.0.0.1;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;"/> -->
    <!--    <add name="blah" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;"/> -->
    <!--    <add name="blah" providerName="MySql.Data.MySqlClient" connectionString="User ID=root;Password=myPassword;Host=localhost;Port=3306;Database=myDataBase; Direct=true;Protocol=TCP;Compress=false;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;"/> -->
    <!--    <add name="blah" providerName="Oracle.DataAccess.Client" connectionString="Data Source=TORCL;User Id=myUsername;Password=myPassword;"/> -->
  </connectionStrings>
</configuration>
    

As you can see, by changing the connection string, we connect to different databases. Nothing new here. We could do that with OleDb and Odbc, but with both those technologies we are passing through layers of code. With the managed providers, chances are they are 100% managed code, which means no layers to go through, thus much faster access. Additionally, by using the managed provider we will get the greatest performance and flexibility (like named parameters) from out database.

The one thing to note here is that some of these providers are third party providers and must be installed.  If you use them, you may have to do some of the install yourself.

IMPORTANT NOTE: Do NOT use the Microsoft Oracle Provider (System.Data.OracleClient) it is being eliminated. Although, if you use the information in this article, it will only require a change to the configuration file.

Make the connection

Now that we have a configuration we can use, lets create a connection to the configured database.

//get the information out of the configuration file.
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["blah"];

//get the proper factory 
DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);

//create a command of the proper type.
DbConnection conn = factory.CreateConnection();
//set the connection string
conn.ConnectionString = connectionStringSettings.ConnectionString;

//open the connection
conn.Open();
                

Now I can connect to ANY database with the proper provider and connectionString.

Modifying and verifying the connection string

Let's go back to that code for a second. Yes it works, but what about making sure that certain features are turned on. For this example, I am going to pick on SQL Server, and a feature I love MultipleActiveRecordSets.

//get the information out of the configuration file.
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["blah"];

//get the proper factory 
DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);

string connectionString = connectionStringSettings.ConnectionString;

DbConnectionStringBuilder csb = factory.CreateConnectionStringBuilder();
//make sure it got created
if (csb != null)
{
    //let the ConnectionStringBuilder parse the current connection string
    csb.ConnectionString = connectionString;
    //if this provider supports this setting, set it.
    if (csb.ContainsKey("MultipleActiveResultSets"))
    {
        csb["MultipleActiveResultSets"] = true;
    }
    //use the new modified connection string
    connectionString = csb.ConnectionString;
}

//create a command of the proper type.
DbConnection conn = factory.CreateConnection();
//set the connection string
conn.ConnectionString = connectionString;

//open the connection
conn.Open();

Now, if my connection supports it, MultipleActiveRecordSets will be set to true. This works regardless which provider we might be using. Unfortunately some providers, don't support the keys correctly, and some providers like the "Microsoft SQL Server Compact Data Provider" don't provide the connection string builder object at all.

Executing a Query

For the sake of clarity I am going to simply continue with the function above.

    //by using the CreateCommand function on the connection rather than 
    //using factory.CreateCommand()
    //the connection is automatically associated with the new command
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select * from Vehicle";
    
    //execute a datareader, closing the connection when all the data is read from it
    using(DbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while(dr.Read())
        {
            //do something with the data
        }
    }

Creating/Using parameters

When we create a parameter with factory.CreateParameter() we get a DbParameter object. Well that is fine, but it will throw some people for a loop.  You see you no longer get access to the provider specific DbType, and even though there are tricks we could use to go get the right settings, it is of limited help because each provider uses a differently named property to set their own enum.  Instead what we need to do is use the DbType that is defined in the base class like this:

    //create a 16 byte random "salt" to put into the database
    byte[] b = new byte[16];
    RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
    rng.GetBytes(b);

    //createa parameter and fill it
    p = factory.CreateParameter();
    p.DbType = DbType.Binary;
    p.Value = b;
    p.ParameterName = "salt";

Getting all installed dataproviders

A simple line to see which provider are installed, in case you want to dynamically create a connection by letting the user input the connection info or perhaps double checking the configured provider is actually installed.

DataTable dt = System.Data.Common.DbProviderFactories.GetFactoryClasses() 

One last trick

I see lots of toolkits, articles and tricks about things like writing insert and update statements but .NET provides you with that functionality too; here is the code for that. Taking advantage of the DbCommandBuilder object, connected DataTables create their SQL for insert, update and deletes using these objects.

    using (DbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select * from " + tableName;
        cmd.CommandTimeout = 10;
        using (DbDataAdapter da = factory.CreateDataAdapter())
        {
            DbCommandBuilder cb = factory.CreateCommandBuilder();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            cb.DataAdapter = da;
            DbCommand[] cmds = new DbCommand[3]
            cmds[0] = cb.GetUpdateCommand();
            cmds[1] = cb.GetDeleteCommand();
            cmds[2] = cb.GetInsertCommand();
        }
    }

Points of Interest

Correctly using the factory pattern in this way prevents hardcoding of your provider. Even better if you are using a third party provider like Oracle, MySQL, or Npgsql, if we don't directly reference that assembly (DLL), we can upgrade the provider without it breaking our code.

In the machine.config is a connection string that is called "LocalSqlServer" the default provider (as expected) is "System.Data.SqlClient" connection string is "data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" which is great for many web applications.  In truth there is no reason you couldn't use that connection string for windows applications as well.  This extra connection string, as well as the others you inherit (especially under web applications) is something to be aware of before trying to do something like:

foreach(var cs in ConfigurationManager.ConnectionStrings)
{
   //create and test each connection
} 

Obviously, this is not the only bit of data you need to make your code database independent, but it is the first thing. Some of the other bits can seen in some of my other articles here, here and here. 

If you need help making connection strings, http://connectionstrings.com is a great resource.

History

None so far.