Connect MySql with .Net


Recently I started working on a new project and the clients wanted to use MySql database server because it is free. I had dealt with the MS Sql 2000 and 2005 servers from .Net and that was a breeze. I had a hell of a time trying to figure out how I can connect MySql and .Net and finally after lots and lots of googling and with lots of common sense I managed to get this done. So I thought of writing this post to help all those who have this requirement and don’t want to go through the entire WWW to find this out. 🙂

Step 1
First of all you must download and install the connector that is needed to connect with the MySql database. You can get the driver here free of charge. (It’s open source ;)) At the time of writing version 5.0 was the stable and newest version. Get that and not 1.0 because to install 1.0 you need the .Net framework 1.1.

Step 2
Then go tour project in Visual Studio. (I am using Visual Studio Professional 2005) Right click your project and select Add Reference, and select MySQL.Data as shown in the figure below.

Step 3
Now you are ready to go. Everything is set and hopefully will work out. 🙂 Just type some code like below to check whether everything is in order. (I am using an ASP.Net page to check connectivity)

public void Page_Load(Object sender, EventArgs e)
{
string conString = “SERVER=localhost;DATABASE=Infinity;UID=root;PASSWORD=test123;”;
MySqlConnection connection = new MySqlConnection(conString);
MySqlCommand command = connection.CreateCommand();
command.CommandText = “select * from Room”;
connection.Open();

MySqlDataReader Reader;
Reader = command.ExecuteReader();
while (Reader.Read())
{
string row = “”;
for (int i = 0; i < Reader.FieldCount; i++)
{
row += Reader.GetValue(i).ToString() + “,”;
}
Response.Write(row);
}
connection.Close();
}

If everything works fine your aspx page should show all the data of the table you queried.


Leave a Reply