Connecting C# to MySQL
Posted on: 01 March 2008
My previous lives have left me pretty handy with C#. I only really used .net 1.1 for serious heavy lifting, so I'm finding all kinds of nice suprises (like generics - strongly typed ArrayLists at last!) in .net 3.5 now that I've started using Visual Studio 2008 (and finding that the free Express Edition is fine for commercial development came as a rather nice suprise too!).
One of the big down sides with the whole Microsoft ecosystem is the cost of getting off the ground, so as a startup we are going down a pretty standard open source tool route (i.e. LAMP servers). However, we have some fairly sophisticated spidering to be done to get structured data from a fairly large number of web sites of widely varying quality, so in the interests of getting up and running quickly, I've developed the spider in C# (with a fairly massive leg-up from Jeff Heaton - thanks Jeff!). I need to get the spidered data back into MySQL so that our other applications can use it, which brings me to today's problem.
About five years ago I dabbled with MySQL (although in our very pro-Microsoft environment it had to be kept pretty quiet!), and connecting C# apps to it. It was all rather painful - lots of hacking to get things to work, followed by pretty poor performance when it did work. One of the great things about the open source movement though is the pace of change. All it takes now is installing the connector, and away you go:
using MySql.Data.MySqlClient;
...
...
MySqlConnection conn = new MySqlConnection(builder.ConnectionString);
conn.Open();
MySqlCommand cmd = new MySqlCommand("select description from products", conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MessageBox.Show("Product description: " + reader.GetString("description"));
}
reader.Close();
conn.Close();
The only other complication was enabling remote connections to the MySQL server, which (thanks to a little help) turned out to be as simple as changing /etc/mysql/my.cnf from
bind-address = 127.0.0.1
to the actual IP address of the server:
bind-address = 192.168.187.128
And away we go!