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.