How to get a C# class to talk to Mysql in under 5 minutes

I had to access a Mysql database at work today using C# and .NET. Well, I didn't have to... but I wanted to... and I had some down time. I'm mainly a PHP guy, but C# is similar enough to C/C++ that I figured out what I needed to do in a few minutes. I think it's always important to step out of your safe little world every now and again to try something radically different. It keeps your problem solving skills fresh.

Here's the rundown for those interested.
  • Download Mysql Connector/Net package http://dev.mysql.com/downloads/connector/net/1.0.html
  • While you're there, if you don't have the Mysql sample db "world" set up on your server, you should get that too at http://dev.mysql.com/doc/ (I'm assuming you know how to set this upin Mysql.
  • Install it on the Windows server you are using (and on your dev machine too)
  • Open up Visual Studio (2k5 if you got it)
  • Open up yer C# project
  • In the References of your project, Add a reference to the Mysql Connector. You'll probably need to go to the "browse" tab and go find it. The default install puts a Mysql.Data.dll in C:\Program Files\MySQL\MySQL Connector Net 1.0.7\bin\.NET 2.0
  • For my sample below, you'll also need to add the System and System.Data .NET references
  • Then plop this test code into a C# project. This example is just a stand-alone console app to get you going. There's plenty of documentation at http://www.mysql.org/doc/refman/4.1/en/connector-net.html to keep you motivated.
using MySql.Data.MySqlClient; 

class mysql 
  static void Main() 
    MySqlConnection conn; 
    string myConnectionString; 

    myConnectionString = "server=localhost;user id=test; pwd=test;database=world;"; 

      conn = new MySqlConnection(); 
      conn.ConnectionString = myConnectionString; 

      string sql = "SELECT Name, Continent FROM Country Limit 10"; 

      MySqlCommand cmd = new MySqlCommand(); 
      MySqlDataReader results; 
      cmd.CommandText = sql; 
      cmd.Connection = conn; 
      results = cmd.ExecuteReader(); 

      while (results.Read()) 
        //string f1 = results.GetString(0); 
        //int f2 = results.GetInt32(1); 
        System.Console.WriteLine(results.GetString(0) + ", " + results.GetString(1)); 

    catch (MySqlException ex) 
      System.Console.WriteLine("error" + ex.Message); 

No comments:

Post a Comment