Connecting to MySQL database from C# application using ODBC

HOW TO CONNECT TO MYSQL DATABASE FROM A C# APPLICATION 
In this article I am going to explain how to connect to a MySQL database from a C# application  using ODBC driver .I have use a very simple program to explain the operation to display the data from my test database in to a web browser .
1.)The first step in this process is to download ODBC connector and configure the ODBC configuration .Upon trying to run this sample program without installing the specific driver, you get following error:

2)You can download the ODBC  driver  3.51  from this link :
3)After downloading the driver change the ODBC settings by going to
    StartMenu > Control Panel >  Data Sources (ODBC) , which should bring you to this screen :
 4) Click the  ADD button and  choose  MySQL ODBC 3.51 driver  and click FINISH button which should take you to the following screen .
5) Put in all the respective parameters  for your MySql Server in the following screen except the Database , which will be refreshed and populated in the drop down control later . Then click TEST.
6) Now we are all set to run our test program by just referencing to the namespace System.Data.Odbc .
Following is the sample code :
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Odbc;
namespace TestMySqlDBConnectionString
{
    public partial class TestConn : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            connectoToMySql(“123.125.44.58 “);//This is the address of         
                                                your server 
        }
        private void connectoToMySql(string server)
        {
            //set your connection string.
            //NOTE: I am a big supporter of having the connection
            //stored in the web.config, not inline like this
          string connString = “DRIVER={MySQL ODBC 3.51 Driver};SERVER=”
                + server + “;” + “PORT = 3306 ;” +
                “DATABASE=test;” +
                “UID=test;” +
                “PASSWORD=test;” + “OPTION=3”;
            //create your mySQL connection
            OdbcConnection cnMySQL = new OdbcConnection(connString);
            //create your mySql command object
            OdbcCommand cmdMySQL = cnMySQL.CreateCommand();
            //create your mySQL reeader object
            OdbcDataReader reader;
            //set the command text (query) of the
            //mySQL command object
            cmdMySQL.CommandText = “select * from cardtypes”;
            //open the mySQL connection
            cnMySQL.Open();
            //execute the reader, thus retrieving the data
            reader = cmdMySQL.ExecuteReader();
            //while theres data keep reading
            while (reader.Read())
            {
                string thisrow = “”;
                for (int i = 0; i < reader.FieldCount; i++)
                    thisrow += reader.GetValue(i).ToString() + “,”;
            // I added a text box in my UI where I wanted to populate
               My results .
                myTestListBox.Items.Add(thisrow);
            }
            cnMySQL.Close();
        }
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s