I’ve been using the MySQL ODBC driver for a long time now in my ASP.NET projects (as posted about here). But today when I was about to start a new project on my new Vista machine I thought I’d check if anything new had emerged the last years when it comes to MySQL drivers for .NET – and not too surprisingly there is now (probably been there for a loooong time) an ADO.NET driver called ADO.NET Driver for MySQL Connector/NET.

So this post is on how to get it going, it’s very straight forward.
This is tried on Vista with Visual Studio 2005 SP1 and the Visual Studio 2005 Service Pack 1 Update for Windows Vista using the ADO.NET Driver for MySQL (Connector/NET) version 5.0.6.

1) Download and install (using the included installer) the ADO.NET Driver for MySQL Connector/NET.

2) Create or open a project in Visual Studio 2005. I’m using a VB.NET console project in my example.

3) Go to Project + Add Reference…
On the .NET tab find and hilite MySQL.Data and then click Ok.
(It seems this step can be skipped sometimes…)
[Update: In an ASP.NET project go to Website + Add Reference]

4) Go to Project + YourProject Properties… + References Tab
Under Imported Namespaces check MySql.Data.MySqlClient and MySql.Data.Types and save.
[Update: In an ASP.NET project open web.config and add as namespace under configuration – system.web – pages – namespaces]

5) Write your code and run it. Here is an example to get you started:

Dim myConnectionString As String = "Database=TestDB;Data Source=localhost;User Id=TestID;Password=TestPwd"
Dim myConnection As New MySqlConnection(myConnectionString)
Dim myCommand As New MySqlCommand("SELECT * FROM testtable", myConnection)
Dim myDataReader As MySqlDataReader
myConnection.Open()
myDataReader = myCommand.ExecuteReader
While myDataReader.Read
Console.WriteLine(myDataReader("testcolumn"))
End While
myDataReader.Close()
myConnection.Close()

More examples can be found in the manual from MySQL.

Note: If your accessing your MySQL server on another machine, i.e. not as localhost, you need to make sure the firewall on the server allows access on TCP port 3306. If the server is using Windows firewall just simply go into Control Panel – Windows Firewall – Exceptions Tab click Add Port. (Just make sure you remember to disable the port again after you are done testing!)