Connecting to MySQL database from your .NET applications.

Introduction

The article tries to explain two possible ways to connect to a MySQL database. There are a lot of other ways through which you could connect to the MySQL database. Two among those are only discussed here.

Using the code

Before using the code, it is assumed that the MySQL database is existing in the system and is up and running. Then create a System DSN and give it the name which you give in the code. Also, make sure that you use the same UID and password. The below code connects to the MySQL database using the third party (Open Source) bridge ByteFX. This works the same way as the SqlConnection. For more information on the operations, please refer to the operation manual.

 ByteFX.Data.MySqlClient; 

...
...
...

//Now try to connect using ByteFX.
//This can be downloaded from http://www.bytefx.com/download.ashx?id=8
string myConnectionString ="DSN=MySQL;UID=root;PWD=root"; 
MySqlConnection myConnection = new MySqlConnection(myConnectionString);
string myInsertQuery = "Select * from Authors";
MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
MySqlDataReader objReader = myCommand.ExecuteReader(); 
int nResultCount = 0;
while (objReader.Read())
{
    ++nResultCount;
}

Another way to connect to the database is through the Microsoft ODBC.NET which is downloadable from Microsoft. The same pre requisites for the above code applies here.

using Microsoft.Data.Odbc;
...
...

//Connect to the database using the system DSN
//Download odbc.net from 
//http://www.microsoft.com/downloads/details.aspx?
//    FamilyId=6CCD8427-1017-4F33-A062-D165078E32B1&displaylang=en

string myConnectionString ="DSN=MySQL;UID=root;PWD=root"; 
OdbcConnection MyConn;
OdbcCommand MyCmd = new OdbcCommand();
MyConn = new OdbcConnection(myConnectionString);
MyConn.Open();
MyCmd.Connection = MyConn;
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT ");
SQL.Append("*");
SQL.Append("FROM ");
SQL.Append("Authors");
MyCmd.CommandText = SQL.ToString();
OdbcDataReader result = MyCmd.ExecuteReader(CommandBehavior.CloseConnection);
int nResultCount = 0;
while (result.Read())
{
    ++nResultCount;
}

Points of Interest

You could also possibly connect to MySQL database using the OleDB classes. But it gives out exceptions when trying to access it. The work around for these kind of exceptions and other problems can be found here.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here