Reader Level:
ARTICLE

Using MySql With C#.Net and GridView Paging

Posted by Jitendra Sampathirao Articles | ADO.NET July 03, 2010
In this article I'm explaining how to add data to the database and how to show the data in a Gridview in C#.Net with MySql with Paging. This is a very useful article for whoever uses a MySql Database with C# for the first time.
  • 0
  • 0
  • 23125
Download Files:
 

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 the 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 the 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 vthen 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 changs, 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.

 mysqlGridview.gif

Click AddNew Button


This table will appear,

mysqlGridview2.gif

You may face these errors:


1. Overload method Gridview_pageiindexchanging matches delegate.


Solution:


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

COMMENT USING

Trending up