Saturday, April 10, 2010

How to connect MySQL Database with C#

MySQL database is a low cost alternative to MS SQL Server database and can be used with web application (ASP.NET) and Windows Application. In this article I will explain the steps to connect to MySQL database from windows Form Application.

Major requirement : First of all you have to download and install MySQL ODBC 3.51 Driver. You can download it from following URL.

http://dev.mysql.com/downloads/connector/odbc/3.51.html


In this example I will implement the basic search functionality using MySQL database table data.

Below picture you can see the Form designing part.



How this works.
1.User enter valid employee ID and click on search button.
2.Then application with connect to the database named “employees” and from relevant table (emp) application will select the Emp Name and Emp Age and those two data will display on relevant text boxes.

Below you can see coding part for this application.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace WindowsFormsApplication5
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void btnSearch_Click(object sender, EventArgs e)
{
//Connection string for the MySQL Database.
string sConString = "Driver={MySQL ODBC 3.51 Driver};" + "Server=localhost;" + "Database=employees;" + "user id=root;" + "password=SLIIT";

OdbcConnection oConnection = new OdbcConnection(sConString);

string sSQL = "SELECT * FROM emp where eid='" + textBox1.Text + "'";

try
{
oConnection.Open();

OdbcCommand cmd = new OdbcCommand(sSQL, oConnection);

OdbcDataReader myReader;

myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

myReader.Read();

string name = myReader["ename"].ToString();

string age = myReader["age"].ToString();

textBox2.Text = name;

textBox3.Text = age;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}


}
}
}