How to Bind Data From MySQL Database to GridView in ASP.Net 4.5

Introduction

This article shows how to bind data from a MySQL database to a GridView in ASP.Net 4.5.

Use

• ASP .NET web page
• MySQL Database
• GridView
• MySQLCommand ,DataSet and MySqlDataAdapter

See the following screen; select "MySQL Wamp Server database" > "customers data".



Create a new project using "File" -> "New" -> "Project..." then select web "ASP .Net Web Forms Application". Name it "GridViewBindMySql".



Now in the Design page “Default.aspx” design the web page as in the following screen:



In the code behind file (Default.aspx.cs) write the code as:

Default.aspx.cs

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewBindMySql._Default" %>
<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">
    <section class="featured">
        <div class="content-wrapper">
            <hgroup class="title">
                <h2>Bind Data from MySql to GridView using DataSet and MySqlDataAdapter in Asp .NET 4.5
                </h2>
            </hgroup>
            <p>
                To learn more about ASP.NET 4.5 ,
            </p>
        </div>
    </section>
</asp:Content>
<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">
    <h3>We suggest the following:</h3>
    <asp:Panel ID="Panel1" runat="server" Width="100%" ScrollBars="Horizontal">
    <p>
       <asp:Button ID="btnBind" runat="server" Text="View" OnClick="btnBind_Click" /> <asp:Label ID="Label1" runat="server" Font-Bold="true" ForeColor="Green"  Text="Total Customers:">    </asp:Label><asp:Label ID="lbltotalcount" runat="server" ForeColor="Red" Font-Size="Larger"></asp:Label> </p>
    <asp:GridView ID="grvCustomers" runat="server"></asp:GridView>
    </asp:Panel>
</asp:Content>

In the Web.config file create the connection string as:

Web.config

<connectionStrings>
    <add name="ConnectionString" connectionString="Server=localhost;userid=root;password=;Database=northwind" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

 

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//Using namespaces
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Text;
using System.Net;
using System.Net.Mail;
using System.Data;
namespace GridViewBindMySql
{
    public partial class _Default : Page
    {
        #region MySqlConnection Connection
        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!Page.IsPostBack)
                {
                }
            }
            catch (Exception ex)
            {
                ShowMessage(ex.Message);
            }
        }
        #endregion
        #region show message
        /// <summary>
        /// This function is used for show message.
        /// </summary>
        /// <param name="msg"></param>
        void ShowMessage(string msg)
        {
            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('" + msg + "');</script>");
        }
        #endregion
        #region Bind Data
        /// <summary>
        /// This display the data fetched from the table using MySQLCommand,DataSet and MySqlDataAdapter
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnBind_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("Select * from customers", conn);
                MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                grvCustomers.DataSource = ds;
                grvCustomers.DataBind();
                lbltotalcount.Text = grvCustomers.Rows.Count.ToString();
            }
            catch (MySqlException ex)
            {
                ShowMessage(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            btnBind.Visible = false;
        }
        #endregion
    }
}

See the following screen for the Default.aspx:



Use the following for “btnBind_Click "; bind the GridView with the data.



Now bind data from MySql to the GridView using a DataSet and MySqlDataAdapter in Asp .NET 4.5. I hope this article is useful. If you have any other questions then please provide your comments below.