How to Connect Mysql Server with Asp.net 2005 / 2008 (C#)

After successful installation of MYSQL, run your MSQL server and create a Database, than a Table. You can use the following command to create a database and a table:

mysql> create database mydataBase;
Query OK, 1 row affected (0.03 sec)
mysql> use mydataBase;
Database changed
mysql> creat table mytable(Name varchar(10),Number varchar(10));
Query OK, 0 rows affected (0.11 sec)

Now we can move to our project.

Go to Visual Studio >> Solution Explorer >> Add references and add a reference to MySQL.DATA from .net components.(check the image for detail).

MYSQL.DATA will not shown in your Add Reference window until you have installed the MYSQL Connector and MDAC.



Then the following code can be included in our C# code for the connection.

public partial class _Default : System.Web.UI.Page
{
MySqlConnection con = new MySqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = "Data Source=localhost;Database= mydataBase;User ID=root;Password=root";
//Default User ID and password is 'root' as 'sa' for Microsoft SQL Server
con.Open();
}
protected void Button1_Click(object sender, EventArgs e)
{
//Let us just get connect and display the table in a GridView.
MySqlDataAdapter dr = new MySqlDataAdapter("select * from mytable",con);
DataSet ds = new DataSet();
dr.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}

And we also need the following namespace to import.

using System.Data.Odbc;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

Again these namespace won't come until you have added the Reference for MYSQL.DATA.

For migrating Microsoft SQL to MySQL can be easily done with MYSQL Migration Tools. You can download it from the below link

http://dev.mysql.com/downloads/gui-tools/5.0.html

If still migration is not working, then you can check this forum link below.

http://www.codeproject.com/answers/56469/Asp-net-Mysql.aspx

Happy Connection!