Resources » Code Snippets » ASP.NET GridView

How to insert, update and delete operations in gridview with using session?


Last Updated:   Category: ASP.NET GridView    
Author: Member Level: Diamond    Points: 60


In this article I am going to explain in detail about how to handle all operation like insert, delete , update etc. in the grid view with use of session values and finally insert all data in the sql database.



Description :

I have found one thread in the forum section asked how to all operation through gridview based on session data. I have worked on this scenario posted the below code

Design side

I have design page with addition textbox and gridview, add new record using like below design

Enter Eno<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br/>
Enter Empname<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br/>
Enter Sal<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br/>
<asp:Button ID="Button1" runat="server" Text="Added to grid" OnClick="Button1_Click" />
<asp:Button ID="Button2" runat="server" Text="Export data to Database" OnClick="Button2_Click" />


And also placed one gridview control to do operations

Code Behind

First of all in the page load event I have declared datatable and its column and stored in the session value

protected void Page_Load(object sender, EventArgs e)
{
lblmsg.Text = "";
lbldbmsg.Text = "";
if (!Page.IsPostBack)
{
dt.Columns.Add("eno");
dt.Columns.Add("empname");
dt.Columns.Add("sal");
Session["reptable"] = dt;
GridData();
}
}
//Load grid data from session
void GridData()
{
GridView1.DataSource = (DataTable)Session["reptable"];
GridView1.DataBind();
}

Now if add new record in using above textbox values I have stored in the session values and rebind the grid data
addnew_record


//Add each row in the session when user add new details in the submit button
protected void Button1_Click(object sender, EventArgs e)
{
dt = (DataTable)Session["reptable"];
dr = dt.NewRow();
dr["eno"] = TextBox1.Text;
dr["empname"] = TextBox2.Text;
dr["sal"] = TextBox3.Text;
dt.Rows.Add(dr);
Session.Remove("reptable");
Session["reptable"] = dt;
GridData();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
}

Editing through session values


I have editing the grid view data and rebind in the same gridview using session
edit record


//Editing operation perform through session
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GridData();
}

//Canceling operation perform through session
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridData();
}

//Updating operation perform through session
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = GridView1.Rows[e.RowIndex];
string eno;

eno = GridView1.DataKeys[e.RowIndex].Value.ToString();

TextBox empname = (TextBox)row.FindControl("txtempname");
TextBox sal = (TextBox)row.FindControl("txtsal");


if (Session["reptable"] != null)
{
DataTable dt1 = new DataTable();
dt1.Clear();
dt1 = Session["reptable"] as DataTable;
for (int i = 0; i <= dt1.Rows.Count - 1; i++)
{
DataRow dr;
if (dt1.Rows[i][0].ToString() == eno)
{
dr = dt1.Rows[i];
dt1.Rows[i].Delete();
}
}
Session.Remove("reptable");
Session["reptable"] = dt1;

//add that updated row here
dt = (DataTable)Session["reptable"];
dr1 = dt.NewRow();
dr1["eno"] = eno;
dr1["empname"] = empname.Text;
dr1["sal"] = sal.Text;
dt.Rows.Add(dr1);
Session.Remove("reptable");
Session["reptable"] = dt;
}

GridView1.EditIndex = -1;
GridData();
}

Now I am goind to delete data from gridview as well as remove that data from session too using below code
delete record


//Deleting operation perform through session
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string eno;
eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
if (Session["reptable"] != null)
{
DataTable dt1 = new DataTable();
dt1.Clear();
dt1 = Session["reptable"] as DataTable;
for (int i = 0; i <= dt1.Rows.Count - 1; i++)
{
DataRow dr;
if (dt1.Rows[i][0].ToString() == eno)
{
dr = dt1.Rows[i];
dt1.Rows[i].Delete();
//dt1.Rows.Remove(dr);
}
}
Session.Remove("reptable");
Session["reptable"] = dt1;
}
GridData();
}

If you want apply page index then use page index changing method and call GridData() method once again after assign new page no.

Complete source

Client side

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>GridView Data Manipulation using Session</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h3>
GridView Data operation using session</h3>
<table width="800" cellpadding="0" cellspacing="0" align="center">
<tr>
<td colspan="2" align="center" valign="middle" height="30">
<asp:Label ID="lblmsg" runat="server" Text="Label"></asp:Label>
</td>
</tr>
<tr>
<td height="30">
Enter Eno
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Enter Empname
</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Enter Sal
</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" align="center" valign="middle" height="30">
<asp:Button ID="Button1" runat="server" Text="Added to grid" OnClick="Button1_Click" />
<asp:Button ID="Button2" runat="server" Text="Export data to Database" OnClick="Button2_Click" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:GridView ID="GridView1" runat="server" DataKeyNames="eno" AutoGenerateColumns="false"
OnRowEditing="GridView1_RowEditing" OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowDeleting="GridView1_RowDeleting" OnPageIndexChanging="GridView1_PageIndexChanging"
PageSize="5" AllowPaging="true" OnRowUpdating="GridView1_RowUpdating" Width="800">
<Columns>
<asp:TemplateField HeaderText="Employee no">
<ItemTemplate>
<%#Eval("eno")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emp name">
<ItemTemplate>
<%#Eval("empname")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtempname" runat="server" Text='<%#Eval("empname") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<%#Eval("sal")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtsal" runat="server" Text='<%#Eval("sal") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Modify" ShowEditButton="true" EditText="Edit">
<ControlStyle Width="50" />
</asp:CommandField>
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:LinkButton ID="lnkDelete" CommandName="Delete" runat="server" OnClientClick="return confirm('Are you sure you want to delete this record?');">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="2" align="left" valign="middle" height="30">
<b>Database record grid</b>
</td>
</tr>
<tr>
<td colspan="2" align="left" valign="middle" height="30">
<asp:Label ID="lbldbmsg" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:GridView ID="GridView2" runat="server" Width="800px" BackColor="White" BorderColor="#3366CC"
BorderStyle="None" BorderWidth="1px" CellPadding="4">
<RowStyle BackColor="White" ForeColor="#003399" />
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Server side

using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
DataTable dt1 = new DataTable();
DataRow dr;
DataRow dr1;

protected void Page_Load(object sender, EventArgs e)
{
lblmsg.Text = "";
lbldbmsg.Text = "";
if (!Page.IsPostBack)
{
dt.Columns.Add("eno");
dt.Columns.Add("empname");
dt.Columns.Add("sal");
Session["reptable"] = dt;
GridData();
//For testing only
DelDbRecord();
}
}
//Editing operation perform through session
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GridData();
}

//Load grid data from session
void GridData()
{
GridView1.DataSource = (DataTable)Session["reptable"];
GridView1.DataBind();
}

//Canceling operation perform through session
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridData();
}

//Updating operation perform through session
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = GridView1.Rows[e.RowIndex];
string eno;

eno = GridView1.DataKeys[e.RowIndex].Value.ToString();

TextBox empname = (TextBox)row.FindControl("txtempname");
TextBox sal = (TextBox)row.FindControl("txtsal");


if (Session["reptable"] != null)
{
DataTable dt1 = new DataTable();
dt1.Clear();
dt1 = Session["reptable"] as DataTable;
for (int i = 0; i <= dt1.Rows.Count - 1; i++)
{
DataRow dr;
if (dt1.Rows[i][0].ToString() == eno)
{
dr = dt1.Rows[i];
dt1.Rows[i].Delete();
}
}
Session.Remove("reptable");
Session["reptable"] = dt1;

//add that updated row here
dt = (DataTable)Session["reptable"];
dr1 = dt.NewRow();
dr1["eno"] = eno;
dr1["empname"] = empname.Text;
dr1["sal"] = sal.Text;
dt.Rows.Add(dr1);
Session.Remove("reptable");
Session["reptable"] = dt;
}

GridView1.EditIndex = -1;
GridData();
}

//Deleting operation perform through session
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string eno;
eno = GridView1.DataKeys[e.RowIndex].Value.ToString();
if (Session["reptable"] != null)
{
DataTable dt1 = new DataTable();
dt1.Clear();
dt1 = Session["reptable"] as DataTable;
for (int i = 0; i <= dt1.Rows.Count - 1; i++)
{
DataRow dr;
if (dt1.Rows[i][0].ToString() == eno)
{
dr = dt1.Rows[i];
dt1.Rows[i].Delete();
//dt1.Rows.Remove(dr);
}
}
Session.Remove("reptable");
Session["reptable"] = dt1;
}
GridData();
}

//Page index changing operation perform through session
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridData();
}


//Add each row in the session when user add new details in the submit button
protected void Button1_Click(object sender, EventArgs e)
{
dt = (DataTable)Session["reptable"];
dr = dt.NewRow();
dr["eno"] = TextBox1.Text;
dr["empname"] = TextBox2.Text;
dr["sal"] = TextBox3.Text;
dt.Rows.Add(dr);
Session.Remove("reptable");
Session["reptable"] = dt;
GridData();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
}

//Bulk Insert data into sql server database
protected void Button2_Click(object sender, EventArgs e)
{
dt = (DataTable)Session["reptable"];
//Upload data to Database using bulk copy
SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["Con"].ToString());
sqlBulk.DestinationTableName = "emp"; //table name
sqlBulk.WriteToServer(dt);

//remove data after insert
dt.Clear();
Session["reptable"] = dt;
GridData();
lblmsg.Text = "All Record Inserted successfully into the database";
LoadDbGridData();
}

//Get records from database and bind in gridview
void LoadDbGridData()
{
DataTable dtDb = new DataTable();
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("select * from emp", sqlcon);
da = new SqlDataAdapter(sqlcmd);
da.Fill(dtDb);
if (dtDb.Rows.Count > 0)
{
GridView2.DataSource = dtDb;
GridView2.DataBind();
}
else
{
lbldbmsg.Text = "No Records found in database!";
GridView2.DataBind();
}


}
catch (Exception ex)
{

}
finally
{
sqlcon.Close();
}
}

//delete old records only for testing
void DelDbRecord()
{
DataTable dtDb = new DataTable();
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("delete from emp", sqlcon);
sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{

}
finally
{
sqlcon.Close();
}
}
}


Source code:

Client Side: ASP.NET
Code Behind: C#

Conclusion

I hope this code snippet is helping you to manipulate session data through gridview.


Did you like this resource? Share it with your friends and show your love!

Responses to "How to insert, update and delete operations in gridview with using session?"
Guest Author: Raje     25 Feb 2013
Dear sir,
As i'm new to asp.net,i'm little confused with Parameters passed to the event handler.
Eg: for button1_click you are using -
Button1_Click(object sender, EventArgs e)
where as For GridRowEdit,GridRowCancel,GridRowUpdate, GridRowDelete and PageIndexChange you r using

-GridView1_RowEditing(object sender, GridViewEditEventArgs e)
-GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
-GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

-GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
-GridView1_PageIndexChanging(object sender,GridViewPageEventArgs e)

the question is why we are changing the second argument



Author: Pravesh Singh    09 Apr 2013Member Level: Bronze   Points : 4
Hi!

This is nice article. Some more article help me to create custom button for edit and delete operation. Prefer following url to study grid view feature.

http://mindstick.com/Articles/38ca3305-6346-467e-b03b-a536737ff623/?Insert%20Delete%20Update%20in%20Gr#dvHeaderText

http://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&sqi=2&ved=0CC8QFjAA&url=http%3A%2F%2Fwww.aspdotnet-suresh.com%2F2011%2F02%2Fhow-to-inserteditupdate-and-delete-data.html&ei=CPJjUbSmEIPLrQeQjoG4AQ&usg=AFQjCNFy-s9HNDaYQ_ZfUOtYaZyOGxXDQg&bvm=bv.44990110,d.bmk

http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html



Author: Phagu Mahato    15 Feb 2014Member Level: Diamond   Points : 10
You can use given code snippet for Insert,Edit,Update,Delete,Cancel and Print operations in Gridview using C#

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

public partial class gridview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{
GetData();
}
}

private void GetData()
{
SqlConnection con = new SqlConnection(@"Data Source=.\;AttachDbFilenm=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;");
con.Open();
SqlCommand cmd = new SqlCommand("select*from customer", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.Commandnm == "ins")
{
TextBox nm = (TextBox)GridView1.FooterRow.FindControl("TextBox2");
TextBox age = (TextBox)GridView1.FooterRow.FindControl("TextBox3");
TextBox Cnty = (TextBox)GridView1.FooterRow.FindControl("TextBox4");
SqlConnection con = new SqlConnection(@"Data Source=.;AttachDbFilenm=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;Max Pool Size=20; Connection Timeout=10;");
con.Open();
SqlCommand cmd = new SqlCommand("insert into customer values('" + nm.Text + "','" + age.Text + "','" + Cnty.Text + "')", con);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Label1.Text = "Record inserted successfully....";
GetData();
}
}
else if (e.Commandnm == "up")
{
SqlConnection con2 = new SqlConnection(@"Data Source=.;AttachDbFilenm=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;Max Pool Size=20; Connection Timeout=10;");
con2.Open();
GridViewRow grid1 = (GridViewRow)((Button)e.CommandSource).NamingContainer;
Label sid = (Label)grid1.FindControl("lsid");
TextBox nm = (TextBox)grid1.FindControl("tnm");
TextBox age = (TextBox)grid1.FindControl("tage");
TextBox Cnty = (TextBox)grid1.FindControl("tCnty"); SqlCommand cmd = new SqlCommand("update customer set nm='" + nm.Text + "',age='" + age.Text + "',Cnty='" + Cnty.Text + "' where sid ='" + sid.Text + "'", con2);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Label1.Text = "Record Updated successfully....";
GetData();
}
}
else if (e.Commandnm == "del")
{
GridViewRow grid1 = (GridViewRow)((Button)e.CommandSource).NamingContainer;
SqlConnection con1 = new SqlConnection(@"Data Source=.;AttachDbFilenm=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;Max Pool Size=20; Connection Timeout=10;");
con1.Open();
Label sid = (Label)grid1.FindControl("lsid");
SqlCommand cmd = new SqlCommand("delete from customer where sid='" + sid.Text + "'", con1);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Label1.Text = "Record Deleted successfully....";
GetData();
}
}
else if (e.Commandnm == "del")
{
GridView1.EditIndex=-1;
GetData();
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
Response.Redirect("print.aspx");
}
}



Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India