1. Resources
    2. Code Snippets
    3. ASP.NET GridView

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

Posted Date: 18 Nov 2012 |Updated: 18-Nov-2012 |Category: ASP.NET GridView |Author: |Member Level: Gold |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.


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



  • 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:
    Email:
    Submit Article
    Return to Article Index

    Top Contributors

    Today
      Last 7 Days

      more...

      About dotnetspider.com

      We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

      Quick Links
      General
      Services

      Digital Marketing by SpiderWorks Technologies, Kochi - India. ©