How to Connect MySQL Database in Asp.Net using C#

In this post we will discuss How to Connect MySQL Database in Asp.Net using C#, or you can say how to establish connection with MySQL server and display data in GridView control. To connect with MySQL you have to use MySQL connector for .net from here. Install connector on your system and copy paste .dll files from appropriate .net framework folder to application folder. After that you have to add MySQL namespace on your code page as mentioned below.

In earlier post we have discussed about How to Install MySQL in Windows 7, How To Add MySql As Linked Server In SQL Server, MySQL to SQL Server Data Migration Through SSIS and Create Database, Table in MySQL.

HTML Markup:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>How to Connect MySQL Database in Asp.Net C#</title>
<style>
body
{
margin:0px auto;
width:900px;
padding-top:50px;
font-family:Calibri;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" Width="900px" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        binddata();
    }
    public void binddata()
    {
        string strcon = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        MySqlConnection conn = new MySqlConnection(strcon);
        conn.Open();
        string strquery = "select * from student_info";
        MySqlCommand cmd = new MySqlCommand(strquery, conn);
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        conn.Close();
    }
}

web.config:

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>

    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
    </system.web>
  <connectionStrings>
    <add name="con" connectionString="Data Source=localhost;port=3306;Initial Catalog=student;User Id=root;password=root"/>
  </connectionStrings>

</configuration>