How to Connect and CRUD Operation With MySQL Database In ASP.NET

Add This To Del.icio.us Share on Facebook StumbleUpon This Add to Technorati Digg This

In this article I use the C# and VB.Net ASP.Net application using the help of MySQLConnector will explain how to connect to the MySQL database.

 The Existing Database in MySQL is as Follow

Requirement For MySQL Database Connection

You will need to download and install the MySQLConnector in order to connect to the MySQL database in ASP.Net.

 Download MySQL Connector

After installation is complete you need to open Windows Explorer and look for the MySql installation in the Program Files folder of your Windows drive. There you will find a folder for MySQL Connector and inside that you will find the MySql.Data.dll which you need to copy inside the BIN folder of your project.

MySql Connection String

Below is the connection string to the MySql Database.

<connectionStrings>

    <add name="constr" connectionString="Data Source=localhost;port=3306;Initial Catalog=SampleDB;User Id=mudassar;password=pass@123"/>

</connectionStrings>

Namespaces

You will need to import the following namespaces.

C#

using System.Data;

using System.Configuration;

using MySql.Data.MySqlClient;

VB.Net

Imports System.Data

Imports System.Configuration

Imports MySql.Data.MySqlClient

Binding the GridView with records from MySQL Database Table

If you are aware of ADO.Net then using MySql will be lot simpler as the MySql Connector classes have very similar names to that of the ADO.Net classes. For example in ADO.Net we have SqlConnection class and the corresponding class in MySql is MySqlConnection.

Inside the Page Load event of the page, I am populating the GridView control with records from the MySql database using a DataTable.

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!this.IsPostBack)

    {

            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

            using (MySqlConnection con = new MySqlConnection(constr))

            {

                using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM Customers"))

                {

                    using (MySqlDataAdapter sda = new MySqlDataAdapter())

                    {

                        cmd.Connection = con;

                        sda.SelectCommand = cmd;

                        using (DataTable dt = new DataTable())

                        {

                            sda.Fill(dt);

                            GridView1.DataSource = dt;

                            GridView1.DataBind();

                        }

                    }

                }

            }

    }

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    If Not Me.IsPostBack Then

        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

        Using con As New MySqlConnection(constr)

            Using cmd As New MySqlCommand("SELECT * FROM Customers")

                Using sda As New MySqlDataAdapter()

                    cmd.Connection = con

                    sda.SelectCommand = cmd

                    Using dt As New DataTable()

                        sda.Fill(dt)

                        GridView1.DataSource = dt

                        GridView1.DataBind()

                    End Using

                End Using

            End Using

        End Using

    End If

End Sub

For Further Reading,

ASP.NET