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

Advertisements

Day 8.2 – Dot NET On Linux – Connecting & Consuming SQLite with ADO.NET

Intro

SQLite is as light weight and simple yet remarkably powerful relational database. It requires no installation other than the existence of the executable and requires no running services SQLite does not support concurrent databases writes and in fact locks the whole database from other write transactions while writing. It also does not support network access;  it requires the executable, database file and implementing software to exist on the same machine.

SQLite has a good website for appropriate use here. Though this might not be suitable for a live website requiring concurrent usage across a network it still has several usages including strong integration within the Java based Android framework and is strongly promoted for storing application and user settings.

Mono has a SQLite ADO.NET data provider installed as default.

This post will run through installing, database and scheme creation and then connection and consumption through code.

The Source Code

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

Installation

We are going to install sqllite3, the documentation and sqlitebrowserl  a simple yet convenient tool for managing and using SQLite databases. Version 3 is in the LMDE repositories along with sqlitebrowser and can be installed with the following command.

sudo apt-get install sqlite3 sqlite3-doc sqlitebrowser

Create a new Database

Open up sqllitebrowser via the launch icon which should be sitting inside the Programming menu. From the File menu select File –> New Database. You will be provided with a file dialog box. Find a suitable location to create the database file and select OK.

Create our Sample Table

To create a sample table go to the Edit menu and select; Edit File –> Create Table

Provide a sample name and then hit Add. To add a new field provide a name and a type from one of; TEXT, NUMERIC, BLOB and INTEGER PRIMARY KEY.

The screen shots below should be enough information to create the same SampleTable which mimics the same table we created in MySQL previously.

We could go into MySQL and generate a SQL script to create our SampleTable but this won’t actually work. I talk about this more in the next blog when I show how we can write code to interface with both MySQL and Sqlite with virtually no differences other than the connection string and a few tweaks.

SQLite DataTypes

You can read about the SQLite data types  here to help understand why they are very limited. In short the data type matches what you store and not what you define. This caused a few problems for me as you can imagine when I initially swapped out MySQL for SQLite in the previous code we wrote. This also explains why I have picked text to store a DateTime type.Connection.

The SQLite data provider comes installed and registered with mono. We don’t need to touch the machine.config to register the data provider like we did with MySQL. We still want to register the connection string in the machine.config. Search for and add in the following section:

<pre><connectionStrings>
    <add name="DotNetOnLinuxSQLLite" connectionString="Data Source=/PersonalData/DevProjects/SandBox/Git/DOT-NET-on-Linux/Day8ADOExample/ADOExample/SqliteDB/SampleSqlite.db;Version=3;" />
	</connectionStrings>

As you can see the connection string is pretty simple requiring just the location of the file and the version of SQLite.

The connection string can be easily accessed in code by:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinuxSQLLite"].ConnectionString;

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows the code behave agnostic to the database; see my next post where I talk about how to implement this.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download ( see at the top) where I have tried to implement a simple DAL layer with basic ORM capability.

Day 8.1 – Dot NET On Linux – Connecting & Consuming MySQL with ADO.NET

Introduction

The ADO.NET framework provides a consistent way to connect and consume databases through the .NET languages. Microsoft provide ADO.NET data providers for a number of databases however support for MySQL come from Oracle.

The Source Code

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

Installation

The Mono provider for MySQL can be used in both mono and .NET and can be downloaded here.

Download and extract the file taking note of the version you have downloaded.

The dll which was shipped as part of the downloaded was configured incorrectly. It has been named all in lower case and needs to be named in upper camel case just like its namespace; rename the dll to MySql.Data.dll which you can do with the following command.

cd path_to_your mysql.data.dll
cp mysql.data.dll MySql.Data.dll

GAC Registration

The MySQL ADO.Net data provider is required to be installed in the GAC. Mono provides a gacutil just like .Net on windows and it follows the same function arguments. You can register the dll with the following command;

sudo gacutil -i MySql.Data.dll

You need to register the dll in the GAC with root privileges; hence why we sudo. You can check the dll was successfully registered with the following commands.

Through the GAC utility application

gacutil -l | grep My

Or by searching the physical location of the GAC.

cd /usr/lib/mono/gac
ls | grep MySql.Data

No matter how many .NET run-time environment versions you have installed you will onyl have one GAC by default. The dlls are version registered which is automatically resolved during run-time. When you reference a dll which has been installed in the GAC you  reference a copy of a correct versioned dll anywhere on your hard disk; this is resolved to the version in the GAC during run-time.

Machine.Config Registration

The MySQL data provider needs to be registered within the Machine.Config. Depending upon how many .Net run-time versions you have installed on your machine you could have more than one machine.config on your disk. I have .NET 2, 3 and 4 with a machine.config inside each of the installations.

I am coding in the latest available version of .Net 4 so I placed my config inside this file; /etc/mono/4.0/machine.config. You will need to edit this file as root. Search for the section and add in a section for our MySQL data provider.

<system.data>
<...>
<OTHER PROVIDERS>
    <DbProviderFactories>
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL"
	    type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
</system.data>

Please note the version number of the download against the version you have downloaded. The PublicKeyToken should remain constant.

Connecting through ADO.NET

Firstly register our database as a connection string within the machine.config used previously. Search for and add in the connection details for our MySQL database named DotNetOnLinuxExample.

    <connectionStrings>
        <.....>
        <OTHER CONNECTION STRIGNS>
        <add name="DotNetOnLinux" connectionString="Server=localhost;Database=DotNetOnLinuxExample;User ID=ReadWriteUser;Password=MYPASSWORD;Pooling=false" providerName="MySql.Data." />
</connectionStrings>

This registers our MySQL database as running on our local host. The Database connected to is Database={DataBaseName} which we created previously as DotNetOnLinuxExample. I will create a new user with ReadWrite access which will only have access to read and write data. Using our AdminUser user would allow our code more rights than is required. I have kept connection pooling turned off as we are running locally with no concurrent users. The final part is the name of the dll we downloaded and regsitered in the GAC for the MySQL data provider.

We can then simply refer to this string as DotNetOnLinux through our applications strings like so:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinux"].ConnectionString;

Next we need to add a reference to the MySql.Data.dll. As noted before we need to locate this dll as if it is a standalone dll and not registerd in the GAC

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows a database agnostic connection and consumption which I will prove later when we swap MySQL for Sqlite with minimal effort; cough*.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download where I have tried to implement a simple DAL layer with basic ORM capability.

Creating a MySQL User with Read and Write Access

We don’t want our run time environment to have access to any commands which we don’t need. We will create a user for our ADO.NET provider to use on our database DotNetOnLinuxExample which only has read and write access to this database.

The following creates a user called ReadWrite on the database named DotNetOnLinuxExample which can only connect to our database from the local host. Change the password NEWPD to something meaningful. We then grant permission to run the commands SELECT, INSERT, DELETE and UPDATE on any table within this database. This might not be as secure as required however for development purposes this is secure enough.

INSERT INTO mysql.user (User,Host,Password) VALUES('ReadWriteUser','localhost',PASSWORD('NEWPD'));
FLUSH PRIVILEGES;

GRANT SELECT, INSERT, DELETE, UPDATE ON DotNetOnLinuxExample.* TO ReadWriteUser@'localhost';
FLUSH PRIVILEGES;

show grants for 'ReadWriteUser'@'localhost';