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:

Default

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

Bind GridView with 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.