Using MySql With C#.Net and GridView Paging

In this article, I'm explaining how to add data to the database and how to show the data in a Gridview in C# with MySQL with Paging. This is a very useful article for whoever uses a MySQL Database with C# for the first time.

Steps:

1. Create a Web Application like "Gridviewincsharpmysql.aspx".
2. Copy the MySql.Data.dll from the MySQL website and paste it into the Bin folder of your application.
3. Create a table, Emp_master. 

The Aspx page has three tables tblone, tbladd, and tblgv.

 

The .aspx code and aspx.cs code is available in the Downloads Zip. 

 

In the page_Load Event, I checked the value of the Session object for a null value. If it is null then I assigned the value "1" to the session object.

 

After that, I checked the postback method, if not postback until that movement I called the Select Method. The Select method has the two methods First(0) and gvwithpaging(gview, qry()) 

 

First(0) shows the tables tblone, tbladd, and tblgv depending on the method parameter value.

 

gvwithpaging(gview, qry()) is to bind the values to the gridview. The gvwithpaging method has DatasetBinding(out ds, some). 

 

At the time of Databinding the gview_rowDataBound event is fired. When the page changes, gview_PageIndexChanging is fired. 

 

When we try to refresh the page, the last data entered will again be entered into the database. To overcome that we are changing the session object value from stage to stage. Finally, the PreRender Event is fired. 

 

In the web.config file I added my applications settings like this: 

 

You have to change the values where XXXXX are shown, with your Database connection values.

<appSettings>
    <add key="Mysql" value="Data Source=XXXXX;User ID=XXXXXX; password=XXXXX; Database=XXXXX"></add>
</appSettings>

This is the Code-behind page code: 

public partial class Gridviewincsharpmysql: System.Web.UI.Page {
    protected void Page_Load(object sender, EventArgs e) {
        if (Session["RefreshCtr"] == null) {
            Session["RefreshCtr"] = 1;
            lblRefresh.Text = Session["RefreshCtr"].ToString();
        }
        if (!IsPostBack) {
            Select();
        }
    }
    public void Select() {
        first(0);
        gvwithpaging(gview, qry());
    }
    private string qry() {
        string myquery = "";
        myquery = "SELECT Emp_Id, Emp_FName, Emp_LName,  Emp_Gender, Emp_P_City, Emp_P_Country,Emp_P_PIN,Emp_Phone, mp_Bank_Name, PAN_NO FROM test.emp_master";
        return myquery;
    }
    public void gvwithpaging(GridView gv, string some) {
        DataSet ds = null;
        if (gv.PageIndex == 0) {
            int myindex = 0;
            gv.PageIndex = myindex;
        }
        DatasetBinding(out ds, some);
        gv.DataSource = ds;
        gv.DataBind();
    }
    public void DatasetBinding(out DataSet dset, string myqry) {
        MySqlConnection myconn = new MySqlConnection(ConfigurationManager.AppSettings["Mysql"]);
        myconn.Open();
        MySqlCommand mycomm = new MySqlCommand();
        mycomm.Connection = myconn;
        mycomm.CommandType = CommandType.Text;
        mycomm.CommandText = myqry;
        MySqlDataAdapter myadapter = new MySqlDataAdapter(mycomm);
        DataSet dts = new DataSet();
        myadapter.Fill(dts);
        dset = dts;
        mycomm.Dispose();
        myconn.Close();
    }
    public void gview_RowDatabound(object sender, GridViewRowEventArgs e) {
        int page = gview.PageIndex + 1;
        int count = gview.PageCount;
    }
    public void first(int a) {
        if (a == 0) {
            tblgv.Visible = true;
            tbladd.Visible = true;
            tblone.Visible = false;
        }
        if (a == 1) {
            tblgv.Visible = false;
            tbladd.Visible = false;
            tblone.Visible = true;
        }
    }
    protected void gview_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        gview.PageIndex = e.NewPageIndex;
        gvwithpaging(gview, qry());
        int pagenumber = gview.PageIndex + 1;
        Response.Write("You are at page number " + pagenumber);
    }
    public void ConnectionString() {
        int Emp_Id = Convert.ToInt32(TextBox1.Text);
        string Emp_FName = TextBox4.Text;
        string Emp_LName = TextBox5.Text;
        string Emp_Gender = TextBox6.Text;
        string Emp_P_City = TextBox7.Text;
        string Emp_P_Country = TextBox8.Text;
        string Emp_P_PIN = TextBox9.Text;
        string Emp_Phone = TextBox10.Text;
        string Emp_Bank_Name = TextBox11.Text;
        string PAN_NO = TextBox12.Text;
        MySqlConnection myconn = new MySqlConnection(ConfigurationManager.AppSettings["Mysql"]);
        myconn.Open();
        MySqlCommand mycomm = new MySqlCommand();
        mycomm.Connection = myconn;
        mycomm.CommandType = CommandType.Text;
        string myqry = "insert into test.emp_master(Emp_Id, Emp_FName, Emp_LName, Emp_Gender, Emp_P_City, Emp_P_Country, Emp_P_PIN, Emp_Phone, Emp_Bank_Name, PAN_NO)values('" + Emp_Id + "','" + Emp_FName + "','" + Emp_LName + "','" + Emp_Gender + "','" + Emp_P_City + "','" +
                       Emp_P_Country + "','" + Emp_P_PIN + "','" + Emp_Phone + "','" + Emp_Bank_Name + "','" + PAN_NO + "')";
        mycomm.CommandText = myqry;
        MySqlDataAdapter myadapter = new MySqlDataAdapter(mycomm);
        DataSet ds = new DataSet();
        myadapter.Fill(ds);
        mycomm.Dispose();
        myconn.Close();
    }
    protected void Addnew(object sender, EventArgs e) {
        first(1);
        Clearall();
    }
    protected void Button1_click(object sender, EventArgs e) {
        if (lblRefresh.Text == Session["RefreshCtr"].ToString()) {
            Session["RefreshCtr"] = Session["RefreshCtr"].ToString() + 1;
            ConnectionString();
            Select();
        }
        first(0);
    }
    private void Clearall() {
        TextBox1.Text = "";
        TextBox4.Text = "";
        TextBox5.Text = "";
        TextBox6.Text = "";
        TextBox7.Text = "";
        TextBox8.Text = "";
        TextBox9.Text = "";
        TextBox10.Text = "";
        TextBox11.Text = "";
        TextBox12.Text = "";
    }
    private void Page_PreRender(object sender, EventArgs e) {
        lblRefresh.Text = Session["RefreshCtr"].ToString();
    }
}

When we run this we will see this.

 

 

Click AddNew Button

This table will appear,

 

You may face these errors:

1. Overload method Gridview_pageiindexchanging matches delegate.

Solution:

Declare pageiindexchanging event from the design page. If we write the onclick property from the source page of the aspx file then the Pageindexchanging will not fire.