Wednesday, 26 June 2013

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

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


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)
  • No comments:

    Post a Comment