Simple.Data and MySql

Submitted by Vidar on 7 March, 2011 - 21:11

I've been looking for a basic .net data access layer for some time. I didn't want all the overhead of a full blown ORM framework like NHibernate, or Entity framework, but I wanted to get rid of all the messy boilerplate needed to use a database. Allso, It needed to support Mysql 4.0 (!). MySql 4.0 is fairly ancient tehnology, making my search rather difficult.
I ended up writing my own simple layer to suit my needs for the project I was working on.
It worked, but it was not elegant, and just slightly less painful to work with than plain ADO. Also, it required another kind of boilerplate code, as it was based on pre-defined data classes.
After spending too many hours on reinventing the wheel and several subsequent technologies, I stumbled over the Simple.Data library by Mark Rendle (@markrendle - http://blog.markrendle.net/)

Simple and Dynamic

Since Simple.Data is based on the dynamic part of .net 4, so you don't need any predefined data classes, no mappings, it just works:

var db = Database.OpenConnection(
"server=localhost;user=SimpleData;database=SimpleDataTest;password=test;");
var user = db.users.FindByName("Vidar");
Console.WriteLine(user.Name); //Vidar
Console.WriteLine(user.Id); //1

That's all here is to it. Fetch a user, and start using the data.
Does most of what I ever could want my own data access code to ever do, and it does it quite elegant.

Expanding compatibility

But, When I discovered Simple.Data it did not support MySql, much less Mysql 4.0. But being open source on Github, MySql support was only a fork away.

Simple.Data against relational databases has three layers:

Top layer: All the common stuff, dynamic magic, implicit casting, the Simple.Data syntax
Adapter layer. For relational databases this is the Ado adapter, is an abstraction of the stock .net ado.net technology.
Ado providers. This is the customization needed to hook up ado with your pick of relational database. Also it provides a couple of useful abstractions used by simple.data to do it's implicit joining.

I set out to create an Ado provider for MySql.

A Simple.Data Ado provider consists of two Interfaces:

IConnectionProvider

Provides the Ado adapter a mean to create a connection to a database.

ISchemaProvider

Provides a set of abstractions that the ado adapter uses to get information about the database it is connected to. Also specifies some database specific stuff used in the generated sql, like the parameter name marker and how table names are quoted.

How I did it

Being lazy I just copied the provider, and tests for the sql compact edition provider that Mark had already made. Next step was making the tests pass using a local MySql test database. And now the fun began..

I needed to work with a MySql 4.0 database. MySql 4.0 is an ancient version, it's not supported anymore, and it requires an old version of the mysql .net connector (which is also not supported anymore).
The old connector is a bit dodgy, and though it claims to be 100% ado compliant, it's not true.

Useful abstractions

The abstractions provided by Simple.Data in the ISchemaProvider interface really helped me work around the limitations in the old rusty connector.
The first point I needed assistance from the abstractions where simply getting the schema information. The MsSql implementation basically used the built in ado function fot getSchema(); this failed brutally with the old MySql Connector. Luckily the abstractions allowed me to create my own implementation.
Next, it is a well known fact that the MySql MyISam database engine don't support foreign key constraints. Simple.Data uses foreign keys to implicitly create joins when writing find statements:

db.Customers.Find(db.Customers.Invoices.Paid == “N”)

Produces following sql:

SELECT [Customers].* FROM [Customers]
JOIN [Invoices] ON [Customers].[CustomerId] = [Invoices].[CustomerId]
WHERE [Invoices].[Paid] = @p1

The Simple.Data ISchemaProvider abstractions allowed me to create an implementation based on naming conventions, allowing Simple.Data to function seamlessly with MySql the same way it does with MsSql.

By pure luck, it turned out that the ancient MySql connector worked with both the old 4.0 version, and the latest 5.5 version of the database. Thus I did not have to create different versions for different MySql versions. (that is until someone notifies me of some quirks I have yet to experience myself..)

Resources:

Simple.Data, and all supporting adapters and providers are available through NuGet,
Code is available on GitHub:
Simple.Data: http://github.com/markrendle/Simple.Data
Simple.Data.Mysql: http://github.com/Vidarls/Simple.Data.Mysql