Day 8.3 – Agnostic database connection with ADO.NET

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here

Intro

The ADO.NET framework abstracts each database provider such that our code needs to know very little of the actual implemented database. What little we do require can be abstracted into a factory for each provider and then injected into our code via a DI kernel. Only each database provider requires any knowledge of the actual database.

The following constitutes the few items which require an understanding of the actual database implemented.

  • Connection and connection string
    • Initiation of MySQLConnection or SQLiteConnection with the correct connection string
    • Connection string should be placed within machine.config ConnectionStrings.
    • Each connection implements IDbConnection
  • DataAdapter
    • Initiation of MySQLDataAdapter or SQLiteDataAdapter
    • Each adapter implements IDbDataAdapter
  • Command
    • Though not actually required by our factory it feels right to include this here.
    • Initiation of DbCommand with our connection via the CreateCommand upon the connection object
  • Database Schema
    • Even between SQLServer and .NET we don’t have the CTE. SQLite’s 4 data types proved a little troublesome.

Connection and connection string

A good place for storing our ADO.NET DataProvider connection string is in the machine.config, there is even a pre existing section to keep them all together called ‘ConnectionStrings’. We can then reference the hard coded database provider specific connection string via:

Creating an instance of our database provider connection is as simple as:

new MySQLConnection(connectionString);
new SQLiteConnection(connectionString);

DataAdapter

Our factory simply requires to return an instantiated version of our providers data adapter

new SqliteDataAdapter();
new MySqlDataAdapter();

Command

connection.CreateCommand();

An Example Factory

 public class MySQLADOProviderFactory : IADODataProviderFactory
 {
        public IDbConnection GetConnection()
        {
            return new MySqlConnection(GetDBConnectionString());
        }

        public IDbDataAdapter GetDataAdapter()
        {
            return new MySqlDataAdapter();
        }

        public string GetDBConnectionString()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinux"].ConnectionString;
        }
 }
public class ProviderFactory : IProviderFactory
{
        public IADODataProviderFactory GetADODataProviderFactory()
        {
            return InjectionFactory.Instance.kernel.Get<IADODataProviderFactory>();
        }
}

Injecting The Factory

public class ProviderFactory : IProviderFactory
{
        public IADODataProviderFactory GetADODataProviderFactory()
        {
            return InjectionFactory.Instance.kernel.Get<IADODataProviderFactory>();
        }
}

Database Schema

The hardest part of this work was getting the model and the two database table schemas to behave nicely with each other.

I had already coded the Id field in MySQL as INT and the associated Id column on SampleModel.cs as int. I had casting issues when mapping SQLite Integer data type as it actually mapped more directly to MySQL BIGINT(20) and c# long types.

The name field had to be changed in MySQL to a VARCHAR(45) to accommodate the size of SQLite TEXT data type.

Dates worked fine though both databases required the format to be ‘yyyy-MM-dd