How do I set up MySQL to work with C#?

Question

I have been tasked with creating a new frontend for a legacy website.

It is written in php (pre-oo), and uses a MySQL database. The hosting provides a .Net package, but does not offer Ms Sql Server.

This is fine, as the database is working fine, but I really want to use Asp.net for the pages. However, most tutorials I've seen on connecting to MySQL from C# require installing an ODBC Driver specifically for MySQL. Not controlling the hosting env, I doubt I'll be able to do just that :)

Have you got any insight to share on this issue?

Answer 1

MySql does have connector for .Net. You do not need to use ODBC,

MySql Connector will let you interact with your MySql database and is fully managed ADO.Net provider. You have the binary (dll) or the source code if you desire. It's pretty simple, once you have imported the dll you just need a connexion string (username,password,location) and you will be setup!

Here is a sample of code (ref: bitdaddy.com):

string MyConString = "SERVER=localhost;" +
 "DATABASE=mydatabase;" +
 "UID=testuser;" +
 "PASSWORD=testpassword;";
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select * from mycustomers";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
 string thisrow = "";
 for (int i= 0;i<Reader.FieldCount;i++)
   thisrow+=Reader.GetValue(i).ToString() + ",";
 listBox1.Items.Add(thisrow);
}
connection.Close();

I suggest you to do not put your code and persistance in the same place and to place your connexion string in you App.Config, but I think this show you how to do it.

Answer 2

Agree with Daok, plus would like to add that easiest way to interact with MySQL is

1. Get the MySQL connector for .Net v6.0 - this has support for Entity Framework
2. Create a Data Model layer using ADO.Net Entity Model to interact with database

OR

You could also take a look at Linq to MySQL provider implementation - it's pretty good.

I went with the former option, when I had a similar requirement.