Wednesday, May 8, 2013

Add,edit,delete,update in gridview asp.net

Hi in this post i will show how to dynamically add,edit, update and delete records in Gridview asp.net.





ASPX Code:


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

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true"
            HeaderStyle-BorderStyle="Solid" HeaderStyle-Font-Bold="true" OnRowCancelingEdit="GridView1_OnRowCancelingEdit"
            OnRowEditing="GridView1_OnRowEditing" OnRowCommand="GridView1_OnRowCommand" OnRowUpdating="GridView1_OnRowUpdating"
            OnRowDeleting="GridView1_OnRowDeleting">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:Label ID="lblEmpIDHeader" runat="server" Text="Emp ID"></asp:Label>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblEmpIdItem" runat="server" Text='<%# Bind("EmpDeptID") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Label ID="lblEmpIdEdit" runat="server" Text='<%# Bind("EmpDeptID") %>'></asp:Label>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:Label ID="lblEmpNameHeader" runat="server" Text="Emp Name"></asp:Label>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblEmpNameItem" runat="server" Text='<%# Bind("[EMpName]") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEmpNameEdit" runat="server"></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtEmpNameFooter" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:Label ID="lblSalaryHeader" runat="server" Text="Salary"></asp:Label>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblSalaryItem" runat="server" Text='<%# Bind("[salary]") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtSalEdit" runat="server"></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtSalaryFooter" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <EditItemTemplate>
                        <asp:Button ID="btnUpdate" runat="server" Text="Update" CommandName="Update" ToolTip="update" />
                        <asp:Button ID="btnCancel" runat="server" Text="Cancel" CommandName="Cancel" ToolTip="cancel" />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" ToolTip="Edit" />
                        <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete" />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Button ID="btnAdd" runat="server" Text="Add" CommandName="AddNew" CommandArgument="<%# ((GridViewRow) Container).RowIndex %>"
                            ToolTip="New" />
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Codebehind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class Grid : System.Web.UI.Page
{
string conStr = "Data Source=ChandanSql;Initial Catalog=NORTHWND;Integrated Security=True";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindgrid();
        }
    }

    public void bindgrid()
    {
        SqlConnection s1 = new SqlConnection(conStr);
        s1.Open();

        string queryString = "SELECT * FROM employee";
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, s1);

        DataSet employee = new DataSet();
        adapter.Fill(employee, "employee");

        s1.Close();

        GridView1.DataSource = employee.Tables[0];
        GridView1.DataBind();
    }

    public void GridView1_OnRowCancelingEdit(Object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        bindgrid();
    }

    public void GridView1_OnRowEditing(Object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        bindgrid();
    }

    public void GridView1_OnRowCommand(Object sender, GridViewCommandEventArgs e)
    {
        //int index = Convert.ToInt32(e.CommandArgument);
        if (e.CommandName == "AddNew")
        {
            TextBox Empname = (TextBox)GridView1.FooterRow.FindControl("txtEmpNameFooter");
            TextBox Salary1 = (TextBox)GridView1.FooterRow.FindControl("txtSalaryFooter");

            SqlConnection Con = new SqlConnection(conStr);
            Con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Con;
            cmd.CommandText = "InsertSP";
            cmd.Parameters.AddWithValue("@empname", Empname.Text);
            cmd.Parameters.AddWithValue("@sal", Salary1.Text);
            cmd.Parameters.AddWithValue("@flag", 1);
            cmd.ExecuteNonQuery();
            cmd.Dispose();

            bindgrid();
        }
        
    }
    protected void GridView1_OnRowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow row = GridView1.Rows[e.RowIndex];

        Label EmpId = ((Label)(row.FindControl("lblEmpIdEdit")));
        TextBox EmpName = ((TextBox)(row.FindControl("txtEmpNameEdit")));
        TextBox Salary1 = ((TextBox)(row.FindControl("txtSalEdit")));

        SqlConnection Con = new SqlConnection(conStr);
        Con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = Con;
        cmd.CommandText = "InsertSP";
        cmd.Parameters.AddWithValue("@empname", EmpName.Text);
        cmd.Parameters.AddWithValue("@sal", Salary1.Text);
        cmd.Parameters.AddWithValue("@flag", EmpId.Text);
        cmd.ExecuteNonQuery();
        cmd.Dispose();

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

    protected virtual void GridView1_OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        GridViewRow row = GridView1.Rows[e.RowIndex];
        Label EmpId = ((Label)(row.FindControl("lblEmpIdItem")));

        SqlConnection Con = new SqlConnection(conStr);
        Con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = Con;
        cmd.CommandText = "DelSP";
        cmd.Parameters.AddWithValue("@empid", EmpId.Text);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        bindgrid();
    }

}

SQL SCRIPTS which has been used within the code

Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)