Connecting to MySQL server using C#.NET

Connecting to a MySQL server using .NET can be attained using three methods,

• Using the ODBC.NET solution
• Using the MySQL Native .NET providers
• Using the OLEDB.NET solution

Here i will be explaining the ODBE.NET method to connect to a MySQL server and will be explained with a code as well.

The ODBC is the international standard for connecting and accessing any database system, the data provider is defined inside the System.Data namespace as System.Data.Odbc 

You can download the Connector/ODBC Driver from the MySQL site by following this link (http://www.mysql.com/downloads/connector/odbc/ )

The following is required for this exercise to work successfully.

• The latest .NET framework
• MySQL Server
• Connector/ODBC driver (to download, follow this link)

Now we can start with the exercise of connecting with ODBC for .NET .

Steps you need to follow before we start:

• Install the latest .NET framework
• Install the MySQL Server 
• Install the Connector software.
• Install the Visual C# Express IDE (optional)/ you could also use the command line way to work on C# programs.

Connecting to the server:

The following code can be used to perform a simple connection :
using System;
using System.Data.Odbc;

namespace Logiphix
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connecting to MySQL database server");

            string MyConString = "DRIVER={MySQL ODBC 5.1 Driver};" 
                                         + "SERVER=localhost;" + "DATABASE=test;" 
                                         + "UID= username ;" + "PASSWORD= password ;";
            try
            {
                OdbcConnection MyConnection = new OdbcConnection(MyConString);

                MyConnection.Open(); 

                if (MyConnection.State == System.Data.ConnectionState.Open)
                {
                    Console.WriteLine("Connected to MySQL database server");
                }
                else
                {
                    Console.WriteLine("Unable to Connect to MySQL database server");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            Console.ReadKey();
        }
    }
}

In the above code we can see that we have imported the namespace System.Data.Odbc , this namespace has all the objects for using the odbc technology for MySQL in .NET. 
The following code is the connection string that has all the information for the connection.

string MyConString = "DRIVER={MySQL ODBC 5.1 Driver};" + "SERVER=localhost;" + "DATABASE=test;"+ "UID= username ;" + "PASSWORD= password ;";

The DRIVER attribute tells the name of the driver that will be used in this case its 
MySQL ODBC 5.1 Driver
The SERVER attribute tells the name of the server, if you are using your local machine which has the MySQL instance , it will be given as "localhost" 
The DATABASE attribute tells us the initial database to connect to
The UID is the username or UserID 
The PASSWORD is the Password given during the account creation.

This line of code  OdbcConnection MyConnection = new OdbcConnection(MyConString); creates a new connection object and to open the connection  MyConnection.Open(); if it successfully connects then the connection state will be open which is determined by System.Data.ConnectionState.Open

Please note that the code is put within a try catch block, if there is any exception while connecting the message can be seen on the console.

Reading Data from the server:


using System;
using System.Data.Odbc;

namespace Logiphix
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connecting to MySQL database server... \n");

            string MyConString = "DRIVER={MySQL ODBC 5.1 Driver};" 
                                          + "SERVER=localhost;" +"DATABASE=test;" 
                                          + "UID=Username;" +"PASSWORD=password;";

            try
            {
                OdbcConnection MyConnection = new OdbcConnection(MyConString);
                MyConnection.Open();

                if (MyConnection.State == System.Data.ConnectionState.Open)
                {
                    Console.WriteLine("Connected to the server! \n");
                    OdbcCommand command = new OdbcCommand("show databases", MyConnection);

                    OdbcDataReader reader;
                    Console.WriteLine("Executing the query ");
                    reader = command.ExecuteReader();
                    object o = null;
                    while (reader.Read())
                    {
                        o = reader[0].ToString();
                        Console.WriteLine(o.ToString());
                    }             
                }
                else
                {
                    Console.WriteLine("Unable to Connect to MySQL database server");
                }
                MyConnection.Close();
                Console.WriteLine("\nClosing the connection and exiting .. bye");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            Console.ReadKey();
        }
    }
}

In the above code 

OdbcCommand command = new OdbcCommand("show databases", MyConnection);
this is used to create a new OdbcCommand object, the parameters passed is the query(the database query to be run on the server and the second parameter is the connection object

OdbcDataReader reader; this declares a reader object used to read the returned dataset

reader = command.ExecuteReader();  this will execute the command on the server and return the dataset to the reader object.

The Output for the above program is as follows

Similarly, we can write data into the server by using the insert command as the query, and using the ExecuteNonQuery() method in place of the ExecuteReader() method.

CODE FILE ?
You can download the Code/Solution file from the following link if you cannot access it please paste the following url in your browser (https://www.box.com/s/480bb8916a35ec114edd)

The code is written using Visual C# 2010 Express edition IDE this is a registerware and can be downloaded for free from the internet.