I wanna share…

Using MySql with C#.Net and GridView Paging

DownLoad Link: Click Here

 In this Article I’m explaining about how to add the data to the database and how to show the data in Gridview in C#.Net with MySql with Paging. This is very useful article to who are using MySql Database with C#.Net first time.

Steps:

  1. Create the Web Application like “Gridviewincsharpmysql.aspx”.
  2. Copy the MySql.Data.dll from MySql website and Paste into the Bin Folder of your application.
  3. Create a table Emp_master.

Aspx page having three tables tblone, tbladd, and tblgv.

.aspx Code is available in Downloads Zip.

 In page_Load Event I checked the value of Seesion object with null value. If right i assigned  the value ‘1’ to the session object.

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

 First(0) is to show the tables tblone, tbladd, and tblgv depends on the method parameter value.

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

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

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

 In 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();

}

}

private void Page_PreRender(object sender, EventArgs e)

{

lblRefresh.Text = Session[“RefreshCtr”].ToString();

}

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);

}

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, Emp_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();

}

private void Clearall()

{

TextBox1.Text = “”;

TextBox4.Text = “”;

TextBox5.Text = “”;

TextBox6.Text = “”;

TextBox7.Text = “”;

TextBox8.Text = “”;

TextBox9.Text = “”;

TextBox10.Text = “”;

TextBox11.Text = “”;

TextBox12.Text = “”;

}

}

When we run this we can see this.

Click AddNew Button

This table will appear,

You may face these Errors are:

1. Overload method Gridview_pageiindexchanging matches delegate.

Sol:

Create this event from design page. If we write the onclick property from source page of aspx file Pageindexchanging will not fired.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: