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

There are other classes, but these are the ones I am going to touch on. Let's get started.

Using the Bits

Configuration

Here is 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 our 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 not: 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, let's 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 connection string.

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 of 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 Data Providers

Here is a simple line of code to see which providers are installed, in case you want to dynamically create a connection by letting the user input the connection information, 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 hard-coding 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", and the 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 be 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

• 1^st February, 2010: Initial version