How to use generic ADO.NET data factories to load a database

Too many samples on the internet only show how to write ADO.Net code for their chosen provider, none really cover the fact that ADO requires a generic factory interface that can make changing database backends as simple as modifying a single file. This allows for any number of database backends to be used, as long as they all support the same SQL syntax, or are close enough that simple type checks can be used to alter the syntax on the fly.

The example below shows how to create a generic connection and execute a command using only the connection string defined in app.config and the System.Data.Common namespace:

string connectionName = "ConnectionString";
System.Configuration.ConnectionStringSettings connectionString = 
        System.Configuration.ConfigurationManager.ConnectionStrings[connectionName];
System.Data.Common.DbProviderFactory factory = 
        System.Data.Common.DbProviderFactories.GetFactory(connectionString.ProviderName);

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString.ConnectionString;
    connection.Open();
    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM ATable WHERE BColumn = @BColumn";
        command.CommandType = CommandType.Text;
        DbParameter param = command.CreateParameter();
        param.ParameterName = "BColumn";
        param.Value = 12;
        command.Parameters.Add(param);

        using (DbDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                int col = reader.GetOrdinal("AColumn");
                if (!reader.IsDBNull(col))
                {
                    object value = reader[col];
                }
            }
        }

    }
}

NOTE: To use the above sample on a website all you have to change is replace System.Configuration.ConfigurationManager with System.Web.WebConfigurationManager.

New backend provider, no problem

Using the above code as an example, if you needed to move your code from VistaDB to SQL server you would only need to change your connection string to one necessary for SQL server and the code will function the same.

It is also possible to provide multiple connection strings in an application and just change the name passed in to switch between them. You could even sync between two different connections as long as both backends support similar SQL syntax.

This example uses a command parameter to pass through a value to filter the query by. This is where the syntax requirements are important. Different backends may require different markers for parameters, One way to work around this us to use a CommandBuilder as it will build the SQL syntax for you, however they will also incur a cost as they have to query the backend to get the schema for the table. Depending on the system this can be a slow operation. Also CommandBuilder's only support a very basic table structure and you cannot perform complex joins or aggregates with them, though if your backend supports Views you can build views to handle such complexities then use the CommandBuilder with them.

Some might suggest that Datasets provide similar results and write less code. Unfortunately, Microsoft decided to not use the generic factories when constructing Datasets and instead the XML for the Dataset is tied to the classname of the underlying backend instead of its generic. This forces you to rebuild the entire Dataset when changing your provider.