CYCLOMEDIA

I recently had the opportunity to build a new Chan-based site against a MySQL database, this article is essentially a collection of all the knowledge I needed to make an ASP .Net/Mono app talk to it, and so isnt exclusive to Chan (Because Chan does its best to get out of your way there's no database handling or binding built in) so hopefully it'll be useful to anyone else wanting to do it, just like the Chan + Mono + openSUSE install guide is also applicable to any ASP.Net app.

Background

Personally I tend to build very basic objects with typed properties to represent rows and List<T>'s of these to represent sets, I then use a basic central static object to open and return a database connection wherever I need it, and to close them when I'm done (Handy Tip 1: Do not Dispose() connections - it kills pooling in .Net). Anyway, what I end up with is a set of generic classes named DataTransformers with simple functions on top for retreiving and saving these objects. One call in such a DataTransformer would look like this:

class Users : DataTransformer<Types.User>
{
  public static Types.User ByID(Int64? UserID)
  {
    return Select(DB.Connection, "SELECT TOP 1 * FROM [Users] WHERE [UserID]=?", new object[] { UserID });
  }
}

The Select function (and the List<T> equivalent SelectList) accepts a Connection, SQL statement and array of objects and the base class takes care of building command objects and binding parameters to them. The connection is courtesy of my static connection builder which just spawns, and returns a new instance. When its done it creates an instance of the User class, populates it from the returned data and returns it. This way the code in the rest of the app can deal exclusively with the objects and never need to deal with the database.

For reasons best left unexplained the site in question was prototyped against an Access database and then deployed to MySql when development took proper flight. The migration was simple enough courtesy of the donation-ware app Access To MySql. But the SQL Syntax had to change to accomodate.

Installing the MySql data namespace

First thing to do was download the DLLs needed to talk to MySql from .Net, there's a handy DLL-only package that is also Mono compatible (as is Chan, remember!) at http://dev.mysql.com/downloads/connector/net/, just select ".Net & Mono" from the drop down and unzip the payload into your /bin directory. You then need to reference MySql.Data.dll in your solution (or at the csc command line with /R). You then have a native .Net MySqlClient data namespace.

Connecting to the database

MySql connection strings are, it turns out, the most sensible in the universe. So connecting is so easy your grandma could do it. This connects to the MySql database "bar" on localhost as user "foo":

using MySql.Data.MySqlClient;

// ...

MySqlConnection conn = new MySqlConnection( "Server=127.0.0.1;Uid=foo;Pwd=12345;Database=bar;" );
conn.Open();
// Do Stuff
conn.Close();

Best practise is to wrap this and do it in its entirety for every single database operation, regardless of the db type you are connecting to. Repeatedly spawning, opening and closing objects might normally be seen as totally bonkers, but the connection pool actually handles things behind the scenes and you'll find that the connection isn't really being created, opened, closed and destroyed multiple times. It's quite smart and handles itself very ably, so let it do its job!

MySql Quirks

Quirks is probably a little unfair, as it's essentially a matter of point of view. The simplest changes were to replace the square brackets (supported by Access, MSSQL and SQLite) with backticks:

SELECT TOP 1 * FROM `Users` WHERE `UserID`=?

Next is to swap the TOP [count] clause with the much more useful LIMIT [start,stop], which is also the way that SQLite does things:

SELECT * FROM `Users` WHERE `UserID`=? LIMIT 0,1

Originally my code simply iterated through an array of objects and stuffed them into the connection as parameters in the order arrived, but MySQL throws a fit if the parameters dont have names. My auto-parameter-stuffing code had to change it from something like:

IDbCommand cmd = new MySqlCommand(sql, DB.Connection);

if( null != objects && objects.Length > 0 )
  foreach( object o in objects )
  {
    IDataParameter prm = new MySqlParameter();
    cmd.Parameters.Add(prm);
    prm.Value = val ?? DBNull.Value;	
  }

To auto-name each parameter p0, p1, p2 etc as follows:

IDbCommand cmd = new MySqlCommand(sql, DB.Connection);

if( null != objects )
  for( Int32 i = 0 ; i < objects.Length ; i++ )
  {
    IDataParameter prm = new MySqlParameter();
    prm.ParameterName = "p" + i.ToString();
    cmd.Parameters.Add(prm);
    prm.Value = val ?? DBNull.Value;	
  }

The final tweak was to name the parameters from anonymous question marks to @-named params (like MSSQL) which matched the above scheme:

SELECT * FROM `Users` WHERE `UserID`=@p0 LIMIT 0,1

The final code snippet is below. If you come from an MS world - which is obviously natural for .Net developers - then pretty much everything you need to know from a coding point of view is somewhere above, there's really nothing else to it as the rest is taken care of by the MySql.Data DLL.

class Users : DataTransformer<Types.User>
{
  public static Types.User ByID(Int64? UserID)
  {
    return Select(DB.Connection, "SELECT * FROM `Users` WHERE `UserID`=@p0 LIMIT 0,1", new object[] { UserID });
  }
}