Connecting to MySQL database using ASP.NET

I decided to use MySQL instead of SQL Server because of the costs involved in getting a licence for Microsoft SQL Server. So although the programming language I’ve decided to stick to remains C# (asp.net), I’m moving away from SQL Server. I’m also using nHibernate which will make it easier if I need to switch to another data store.

Ways to connect to MySQL in .net

There are 3 ways to connect to MySQL database from a .net web application and they are Odbc, MySQL native .net providers or OLEDB.net. The first two are most common and there are advantages and disadvantages to both.

Using ODBC.NET

You need to download the MySQL ODBC driver – MyODBC 3.51 from here. Once this is installed, you can set up a DSN (Data Source Name) if you want.

You can now start using the odbc driver by editing your web.config file and adding a connection string for your MySQL database as follows:

string connectiongString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=mydatabase;UID=myusername;PASSWORD=mypassword;OPTION=3";
 OdbcConnection con = new OdbcConnection(connectiongString);

 con.Open();

 OdbcCommand cmd = new OdbcCommand("SELECT * FROM tblCustomers", con);
 OdbcDataReader dr = cmd.ExecuteReader();

 while (dr.Read())
 {
 // read the data here
 }

 cmd.Dispose();
 con.Close();
 con.Dispose();

You may need to add a reference to the System.Data.dll and import the System.Data namespace to get this to work.

The advantage of using ODBC is that it is the standard way of connecting to databases, it is not database specific, which means that if you change from MySQL to Oracle or PosgreSQL, your code will still work. The main drawback of this approach is that it is rather slow because of the many layers involved and performance suffers.

Using MySQL native .Net Providers

You will need to download the MySQL .NET provider from here. Once this is installed, you can write sample code as follows:

string connectionString = "Database=mydatabase;Data Source=localhost;User Id=myusername;Password=mypassword;"
string commandString = "DELETE FROM Session WHERE Expires < ?Expires";
MySqlConnection conn = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand(commandString, conn);
cmd.Parameters.Add("?Expires", MySqlDbType.DateTime).Value = DateTime.Now;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

You will need to import the MySql.Data.MySqlClient namespace to get the code to compile. The MySQL .net connector is the same as the ByteFX connector for MySQL. MySQL bought ByteFX and renamed it to MySQL .NET connector. You can also use CoreLab.MySql available from CoreLabs but that connector is not free.

There are many benefits to using a native .net connector instead of ODBC and the main reason is performance gain. There’s the speed, garbage collection and security as well as database specific things that you can use. The only drawback is that if you later decide to change from MySQL to something else, you might need to rework your code.

Using OLEDB.NET

You can use OLEDB in the same way as ODBC. You need to download MyOLEDB provider (MyOleDB 3.0) and install it on your machine first. Then you should edit your connection string as follows:

Provider=MySQLProv;location=localhost;Data Source=mydatabase;User ID=myusername;Password=mypassword;

I prefer to use a native .net connector and I’ve installed MySQL Server 5.1 on my Windows 7 machine to get it working.