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';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s