Wednesday, 3 April 2013

Working Insert, Edit, Update and Delete with in GridView in ASP.Net using C#

Working Insert, Edit, Update and Delete with in GridView in ASP.Net using C#

As the title says I am going to explain how to Insert, Edit, Update and Delete within GridView in ASP.Net using C#. It explains the methods of binding Asp.Net GridView control with simple DataSet and also explains the methods like Insert, Edit, Update and Delete function in the GridView control.

This Example is for Beginners in ASP.Net.

The GUI looks like as shown in the image below....








So let us start the application.
Create a database named as aspdotnetDB and a table as EMPTable as shown below

CREATE TABLE EMPTable
(
    empid int IDENTITY(1,1) primary key NOT NULL,
    fname varchar(50) NOT NULL,
    lname varchar(50) NOT NULL,
    qualification varchar(50) NULL,
    designation varchar(50) NULL,
    sal int NULL
)

In my case Gridview name is EmpGrid

Set GridView Properties as Follows :
  1. AutoGenerateColumns="False"
  2. DataKeyNames="empid,fname,lname"
  3. ShowFooter="True" HeaderStyle-Font-Bold="true"
  4. OnRowCancelingEdit="EmpGrid_CanclingEdit"
  5. OnRowDeleting="EmpGrid_Deleting"
  6. OnRowEditing="EmpGrid_Editing"
  7. OnRowUpdating="EmpGrid_Updating"
  8. OnRowCommand="EmpGrid_Command" 
  •  Add <Columns> template within gridview
  •  Add <asp:TemplateField> and HeaderText="Commands"
  • Now add <EditItemTemplate>,  <ItemTemplate> and  <FooterTemplate>
  • add link buttons in the templates 2 buttons in  EditItemTemplate, ItemTemplate and 1 button in FooterTemplate.
And  then It should look like this...

<Columns>

<asp:TemplateField HeaderText="Commands">
<EditItemTemplate>
<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update"ToolTip="Update"/>
<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel"ToolTip="Cancel"/>           
</EditItemTemplate>
<ItemTemplate>
<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit"ToolTip="Edit"/>
<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"ToolTip="Delete"/>
</ItemTemplate>      
<FooterTemplate>
<asp:LinkButton ID="btnAddNew" Text="Add New" runat="server"CommandName="AddNew" ToolTip="ADD"/>
</FooterTemplate>
</asp:TemplateField>

</Columns>

Now ADD another ItemTemplate for binding and inserting one column as shown below 

<asp:TemplateField HeaderText="First Name">
<EditItemTemplate>
<asp:Label ID="lblEditFname" runat="server" Text='<%#Eval("fname") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblFname" runat="server" Text='<%#Eval("fname") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFooterFname" runat="server" />
<asp:RequiredFieldValidator ID="rqvtxtfname" runat="server"ControlToValidate="txtFooterFname" Text="*" ValidationGroup="validation"/>
</FooterTemplate>
</asp:TemplateField>

And Now Repeat for the remaining columns ....
Designing Code should look like this........


<%@ 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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="EmpGrid" runat="server" AutoGenerateColumns="False"DataKeyNames="empid,fname,lname" ShowFooter="True" HeaderStyle-Font-Bold="true"
OnRowCancelingEdit="EmpGrid_CanclingEdit"
OnRowDeleting="EmpGrid_Deleting"
OnRowEditing="EmpGrid_Editing"
OnRowUpdating="EmpGrid_Updating"
OnRowCommand="EmpGrid_Command" BackColor="#DEBA84" BorderColor="#DEBA84"
BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2"> 
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
   
<Columns>

<asp:TemplateField HeaderText="Commands">
<EditItemTemplate>
<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update"ToolTip="Update"/>
<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel"ToolTip="Cancel"/>           
</EditItemTemplate>
<ItemTemplate>
<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit"ToolTip="Edit"/>
<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"ToolTip="Delete"/>
</ItemTemplate>      
<FooterTemplate>
<asp:LinkButton ID="btnAddNew" Text="Add New" runat="server"CommandName="AddNew" ToolTip="ADD"/>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="First Name">
<EditItemTemplate>
<asp:Label ID="lblEditFname" runat="server" Text='<%#Eval("fname") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblFname" runat="server" Text='<%#Eval("fname") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFooterFname" runat="server" />
<asp:RequiredFieldValidator ID="rqvtxtfname" runat="server"ControlToValidate="txtFooterFname" Text="*" ValidationGroup="validation"/>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Last Name">
<EditItemTemplate>
<asp:Label ID="lblEditlname" runat="server" Text='<%#Eval("lname") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lbllname" runat="server" Text='<%# Eval("lname") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfooterLname" runat="server" />
<asp:RequiredFieldValidator ID="rqvtxtlname" runat="server"ControlToValidate="txtfooterLname" Text="*" ValidationGroup="validation" />
</FooterTemplate>
</asp:TemplateField>
   
<asp:TemplateField HeaderText="Qualification">
<EditItemTemplate>
<asp:TextBox ID="txtEditqualification" runat="server" Text='<%# Eval("qualification") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblqualification" runat="server" Text='<%# Eval("qualification")%>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox runat="server" ID="txtfooterqualification" />
<asp:RequiredFieldValidator ID="rfvtxtqualification" runat="server" Text="*"ControlToValidate="txtfooterqualification" ValidationGroup="validation" />
</FooterTemplate>
</asp:TemplateField>
   
<asp:TemplateField HeaderText="Designation">
<EditItemTemplate>
<asp:TextBox ID="txtEditdesignation" runat="server" Text='<%# Eval("designation") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lbldesignation" runat="server" Text='<%# Eval("designation") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfooterdecription" runat="server" />
<asp:RequiredFieldValidator ID="rfvfooterdescription" runat="server" Text="*"ControlToValidate="txtfooterdecription" ValidationGroup="validation"/>
</FooterTemplate>
</asp:TemplateField>
   
<asp:TemplateField HeaderText="Salary">
<EditItemTemplate>
<asp:TextBox ID="txtEditsalary" runat="server" Text='<%# Eval("sal") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblsalary" runat="server" Text='<%# Eval("sal") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfootersalary" runat="server" />
<asp:RequiredFieldValidator ID="rfvfootersalary" runat="server" Text="*"ControlToValidate="txtfootersalary" ValidationGroup="validation"/>
</FooterTemplate>
</asp:TemplateField>
   
</Columns>
   
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />

<HeaderStyle Font-Bold="True" BackColor="#A55129" ForeColor="White"></HeaderStyle>
  
</asp:GridView>
</div>
</form>
</body>
</html>

 Coming to Code Behind file Code should look as shown below...


using System;
using System.Data;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class Default : System.Web.UI.Page
{
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["empdetailscon"].ConnectionString);
SqlDataAdapter da;
protected void Page_Load(object sender, EventArgs e)
{
   if (!IsPostBack)
   {
      Bindemployees();
   }
}

protected void Bindemployees()
{
   da = new SqlDataAdapter("select * from EMPTable", con);
   DataSet ds = new DataSet();
   da.Fill(ds, "Bindemployees");
   if (ds.Tables[0].Rows.Count > 0)
   {
       EmpGrid.DataSource = ds;
       EmpGrid.DataBind();
   }
   else
   {
       ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
       EmpGrid.DataSource = ds;
       EmpGrid.DataBind();

       int columncount = EmpGrid.Rows[0].Cells.Count;
       EmpGrid.Rows[0].Cells.Clear();
       EmpGrid.Rows[0].Cells.Add(new TableCell());
       EmpGrid.Rows[0].Cells[0].ColumnSpan = columncount;
       EmpGrid.Rows[0].Cells[0].Text = "No Items To Display...!";
   }       
}

protected void EmpGrid_Updating(object sender, GridViewUpdateEventArgs e)
{
    int empid = Convert.ToInt32(EmpGrid.DataKeys[e.RowIndex].Value.ToString());
    string fname = EmpGrid.DataKeys[e.RowIndex].Values["fname"].ToString();
    string lname = EmpGrid.DataKeys[e.RowIndex].Values["lname"].ToString();
    TextBox txtq = 
           (TextBox)EmpGrid.Rows[e.RowIndex].FindControl("txtEditqualification");
    TextBox txtd = 
           (TextBox)EmpGrid.Rows[e.RowIndex].FindControl("txtEditdesignation");
    TextBox txts = 
           (TextBox)EmpGrid.Rows[e.RowIndex].FindControl("txtEditsalary");

    string updatequery = "update EMPTable set qualification='" + txtq.Text +
                       "',designation='" + txtd.Text + "',sal='" + txts.Text +"'
                       where empid='" + empid + "'";
    DataSet ds = new DataSet();
    da = new SqlDataAdapter(updatequery,con);
    da.Fill(ds,"added");
    EmpGrid.EditIndex = -1;
    Bindemployees();
}

protected void EmpGrid_CanclingEdit(object sender, GridViewCancelEditEventArgse)
{
    EmpGrid.EditIndex = -1;
    Bindemployees();
}

protected void EmpGrid_Deleting(object sender, GridViewDeleteEventArgs e)
{
    int empid = Convert.ToInt32(EmpGrid.DataKeys[e.RowIndex].Value.ToString());
    da = new 
    SqlDataAdapter("delete from EMPTable where empid='" + empid + "'", con);
    DataSet ds = new DataSet();
    da.Fill(ds,"deleted");
    Bindemployees();
}

protected void EmpGrid_Editing(object sender, GridViewEditEventArgs e)
{
    EmpGrid.EditIndex = e.NewEditIndex;
    Bindemployees();
}

protected void EmpGrid_Command(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName.Equals("AddNew"))
    {
        TextBox txtf = (TextBox)EmpGrid.FooterRow.FindControl("txtFooterFname");
        TextBox txtl = (TextBox)EmpGrid.FooterRow.FindControl("txtfooterLname");
        TextBox txtq = 
                (TextBox)EmpGrid.FooterRow.FindControl("txtfooterqualification");
        TextBox txtd = 
                (TextBox)EmpGrid.FooterRow.FindControl("txtfooterdecription");
        TextBox txts = (TextBox)EmpGrid.FooterRow.FindControl("txtfootersalary");

        string insrtquery = "insert into EMPTable
                            (fname,lname,qualification,designation,sal) values
                            ('" + txtf.Text + "','" + txtl.Text + "',
                             '" + txtq.Text + "','" + txtd.Text + "','" +
                                  txts.Text + "')";
        da = new SqlDataAdapter(insrtquery, con);
        DataSet ds = new DataSet();
        da.Fill(ds,"inserted");
        Bindemployees();
     }       
}
}


The GridView Should look as shown above and Operations as shown below images.


Download from Here.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)