How to insert, update and delete operations in gridview with using session?
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 operationsCode 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
//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
//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
//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.
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