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 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.


Attachments

  • GridDataManipulationSession (44347-5135-GridDataManipulationSession.rar)
  • Comments

    Guest Author: Raje25 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 Singh09 Apr 2013 Member 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 Mahato15 Feb 2014 Member Level: Gold   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");
    }
    }