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 });
  }
}

Cyclomedia.co.uk - always believing that the chef should taste his own soup - has been converted to a bleeding edge Chan 0.9 build. Obviously work is not nearly finished, specifically in matching the HTML5 form validation sensibilities with Chan's own but some of the experience will be helpful to future developments. By far the oddest sensation was found when converting a few things to use the new MVC-ish square bracket notation. For example, whilst previously to use one of Chan's built in Icons you had to create an Image control and assign it's Path property like this:

<Chan:Image runat="server" Path="[Icon.Link]" />
	

... probably creating a whole load of ASP.Net reflection and memory overhead on the server in the process ... You can now just do this:

<img src="[Icon.Link]">
	

Entering this in raw HTML and magically having a fully configured and correctly pointed path to a PNG magically pop out of the renderer is actually a little unnerving when you're not used to it! What's more fun is that the code that performs this neat trick parses the HTML in a stream as it is output to the client meaning you can stuff these square brackets nearly anywhere. This is used internally by Chan to surpass the old Region system, whenever you bind a Panel or Fragment to a region it attaches itself to the Renderer and waits until it is asked for its HTML. whereas the pre 0.9 builds had a list of regions that had to be created and pre-filled prior to rendering, creating a lot of strings floating about in RAM.

Something that attaches itself to the Renderer and swaps out square-bracketted snippets for HTML is called a Resolver and you can easily create your own - It's a public interface with just one function. So you could have a resolver that responds to "[Foo]" with "The anwser is [Bar]" and another that resolves "[Bar]" to "42". Stick "[Foo]" somewhere in your ASPX, HTML or even the Text property of a Control and the renderer won't even break a sweat; the client will see "The answer is 42" in its place.

Anyway, as you can no doubt see it doesnt LOOK much different around here, esp considering that said Chan Reference pages are still only at 0.85. The trick is to view the pages in Opera and see the HTML5 Date and Number input types automagically appear...

The proof-of-concept browser capability detection code has been pulled into Chan's development trunk. Essentially this is an xml file containing a bunch of regular expressions that are run against the User Agent string to determine what the user is, well, using. It's lightweight and aware that the user can bugger the UA up so defaults to assuming you're at least capable of full HTML4 + JS support but also allows for both Mobile (less capable) browsers and granular resolution of what Html 5 features a browser supports.

What this means in practise is that if the RegExp sees "Opera/" in the string it assumes that you have a version that can natively support the Html 5 Date Input. It then double checks the version number with 5 through 8 overriding this. This means that you would not need to update the xml file when Opera 11 comes out, unlike MS's browsercaps system which is somewhat clunky and in practise works in the opposite direction.

Anyway, i've teased you for too long, here is a screenshot of (left to right) Firefox 3 with Javascript disabled, IE8 running normally and Opera 10. Each is showing the exact same page, and each has been automatically served a different Date input. The NoScript one, one with a javascript powered date picker and a native Html 5 <input type="date">, respectively. Click to enlarge: