Inspired by Joe Audette's presentation at the Nashville .NET Users Group meeting last night, I've been on a mission to learn more about MySQL. I downloaded the database server, administrator, query browser and the .NET data provider "MySQL Connector/Net" (formally ByteFX, I believe). After installing all the goodies, I fired up MySQL Administrator and created a new database ...er... schema, and added a couple of tables.
Setting up a "hello world" test was a breeze. MySql.Data.MySqlClient includes a MySqlHelper class, which is equivalent to the SqlHelper class found in the Microsoft Application Blocks for Data (now in the Enterprise Library). Within just a few lines of code, I had a DataGrid populated from my new database...uh...schema.
string cnnString = "Server=localhost;Port=3306;Database=mydb;Uid=myusr;Pwd=mypwd";
MySqlConnection cnx = new MySqlConnection(cnnString);
cnx.Open();
DataSet ds = MySqlHelper.ExecuteDataset(cnx, "SELECT * FROM favorite");
dgFavorite.DataSource = ds;
dgFavorite.DataBind();
But, I didn't stop there. Oh, no. My new mission was to see if I could get Paul Wilson's ORMapper to talk to MySQL. It took some Googling and a couple of good resources, but I was eventually able to do it.
// Retrieve a single object using GetObject()
Favorite fav = (Favorite) DataManager.ObjectSpace.GetObject(typeof(Favorite), 1);
lblResults.Text = "Retrieved Favorite: " + fav.Text;
// Retrieve all objects using Paul Welter's RetrieveAll() static method
dgFavorite2.DataSource = Favorite.RetrieveAll();
dgFavorite2.DataBind();
Of course, there are a lot of details I'm leaving out. I may use this experience as a starting point for a tutorial or two for my "Learn ASP.NET" section.
By the way, I'm using Paul Welter's latest CodeSmith templates for the ORMapper, which automatically sets up static methods on the generated entity classes such as Retrieve(), RetrieveAll(), RetrievePage(), RetrieveQuery(), Save() and Delete(). Very cool.
What a rush... this is why I became a developer.