Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net C#
Click on Image to enlarge
Introduction: In this article I am going to explain How to perform Bind, Insert/Save, Edit, Update, Cancel, Delete, Paging in GridView in asp.net using C# language.
Basically you will learn the following through this example:
How to Save/Insert and update data in Sql Server Database from asp.net web application?
How to Bind Data from Sql Server to GridView Data Control?
How to perform Edit, Update and Delete operation in GridView?
How to Fill Day, Month and Year in DropDownList and set current date as selected?
How to find controls like TextBox and DropDownList placed inside GridView and get their values?
How to implement paging in GridView?
In previous articles i explained How to Bind,Insert,Edit,Update,Delete in GridView in asp.net VB.Net and WCF Service to bind,insert,edit,update,delete from sql server database in asp.net C# and Bind,upload,download,delete image files from the GridView in asp.net using Sql Server as a back end database and How to upload, download and delete files from GridView in Asp.net? and How to bind gridview using SqlDataAdapter,DataSet and Stored procedure in Asp.net and Bind and Export GridView data to PDF file in asp.net(C#, VB.Net) and How to bind empty GridView with header and custom message when no data present in Datatable and some other GridView Examples .
Click on image to enlarge
First of all create the Database in Sql Server and Name it "Emp_DB" or whatever you want. Create a Table having columns as shown in figure and name it "Emp_Tb" .
Note: Emp_Id_Pk column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1.
In the Web.config file create the connection string as:
<connectionStrings>
<add name="conStr"connectionString="Data Source=LocalServer;Initial Catalog=Emp_DB;Integrated Security=True"/>
</connectionStrings>
Create a folder in the root directory and name it "Images". Search edit and delete .png icon image from the google search and add in the "Images" folder.
Now In the Design page(.aspx) design the web page as:
Note: I have also implemented the asp.net validations on the Employee Name, Address and the Salary fields.
<fieldset style="width:545px;">
<legend style="font-size:20px; color:Green;">Save, Edit, Update, Delete, Paging example in
Gridview</legend>
<table>
<tr>
<td>
Employee Name</td>
<td>
<asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEmpName" runat="server"
ErrorMessage="Please enter employee name"
ControlToValidate="txtEmployeeName" Display="Dynamic" ForeColor="Red"
SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
Address</td>
<td>
<asp:TextBox ID="txtAddress" runat="server" TextMode="MultiLine"Height="59px"
Width="267px"></asp:TextBox><asp:RequiredFieldValidator ID="rfvAddress"runat="server"
ErrorMessage="Please enter Address"
ControlToValidate="txtAddress" Display="Dynamic" ForeColor="Red"
SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
DOJ</td>
<td>
<asp:DropDownList ID="ddlYear" runat="server" ToolTip="Select Year"
onselectedindexchanged="ddlYear_SelectedIndexChanged">
</asp:DropDownList>
<asp:DropDownList ID="ddlMonth" runat="server" ToolTip="Select Month"
onselectedindexchanged="ddlMonth_SelectedIndexChanged">
</asp:DropDownList>
<asp:DropDownList ID="ddlDay" runat="server" ToolTip="Select Day">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Salary</td>
<td>
<asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvSal" runat="server"
ErrorMessage="Please enter salary" ControlToValidate="txtSalary"
Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
<asp:Button ID="btnReset" runat="server" Text="Reset" CausesValidation="false"
onclick="btnReset_Click" />
</td>
</tr>
</table>
<asp:GridView ID="grdEmp" runat="server"
DataKeyNames="Emp_Id_Pk" CssClass="rowHover" RowStyle-CssClass="rowHover"
AutoGenerateColumns="False" AllowPaging="True"onpageindexchanging="grdEmp_PageIndexChanging"
PageSize="4" onrowcancelingedit="grdEmp_RowCancelingEdit"
onrowdeleting="grdEmp_RowDeleting" onrowediting="grdEmp_RowEditing"
onrowupdating="grdEmp_RowUpdating"onrowdatabound="grdEmp_RowDataBound"
CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderText="Emp Name" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<%#Eval("EmpName")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEmpName" runat="server" Text='<%#Eval("EmpName") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEmpName" runat="server" ErrorMessage="*"ForeColor="red" ControlToValidate="txtEmpName" Display="Dynamic"SetFocusOnError="True"></asp:RequiredFieldValidator>
</EditItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Address" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<%#Eval("Address")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditAddress" runat="server" Text='<%#Eval("Address") %>' TextMode="MultiLine"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvAddress" runat="server" ErrorMessage="*"ForeColor="red" ControlToValidate="txtEditAddress" Display="Dynamic"SetFocusOnError="True"></asp:RequiredFieldValidator>
</EditItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="DOJ" HeaderStyle-HorizontalAlign="Center"ItemStyle-Width="180px">
<ItemTemplate>
<%#Eval("DOJ", "{0:dd/MM/yyyy}")%>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlEditDay" runat="server" ToolTip="Select Day">
</asp:DropDownList>
<asp:DropDownList ID="ddlEditMonth" runat="server" ToolTip="Select Month">
</asp:DropDownList>
<asp:DropDownList ID="ddlEditYear" runat="server" ToolTip="Select Year">
</asp:DropDownList>
</EditItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" Width="180px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<%#Eval("Salary")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtSal" runat="server" Text='<%#Eval("Salary") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvSal" runat="server" ErrorMessage="*"ForeColor="red" ControlToValidate="txtSal" Display="Dynamic" SetFocusOnError="True"></asp:RequiredFieldValidator>
</EditItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit" HeaderStyle-HorizontalAlign="Center"ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:ImageButton ID="imgEdit" runat="server" CommandName="Edit" ImageUrl="~/Images/Edit.png" ToolTip="Edit" CausesValidation="false"/>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="lkUpdate" runat="server" Text="Update"CommandName="Update" ToolTip="Update" CausesValidation="false"></asp:LinkButton>
<asp:LinkButton ID="lkCancel" runat="server" Text="Cancel"CommandName="Cancel" ToolTip="Cancel" CausesValidation="false"></asp:LinkButton>
</EditItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete" HeaderStyle-HorizontalAlign="Center"ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:ImageButton ID="imgDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png" OnClientClick="return confirm('Are you sure you want to delete selected record ?')" ToolTip="Delete" CausesValidation="false"/>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle CssClass="rowHover" BackColor="#F7F6F3" ForeColor="#333333"></RowStyle>
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333"/>
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</fieldset>
C#.Net Code to Bind, Save, Edit, Update, Cancel, Delete, Paging example in GridView
In the Code behind file (.aspx.cs) write the code as:
#region "namespaces"
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;
using System.IO;
#endregion
public partial class _Default : System.Web.UI.Page
{
//Creating Connection object and getting connection string
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
#region "page Load"
protected void Page_Load(object sender, EventArgs e)
{
//Check and Open Database connection
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (!Page.IsPostBack)
{
//Bind gridview existing records
bindGridView();
//DOJ Field
//Fill Years with current year selected
for (int i = 2013; i >= 1980; i--)
{
ddlYear.Items.Add(i.ToString());
}
ddlYear.Items.FindByValue(System.DateTime.Now.Year.ToString()).Selected = true; //set current year as selected
//Fill Months with current month selected
for (int i = 1; i <= 12; i++)
{
ddlMonth.Items.Add(i.ToString());
}
ddlMonth.Items.FindByValue(System.DateTime.Now.Month.ToString()).Selected =true; // Set current month as selected
//Fill days based on selected month
FillDays();
}
}
#endregion
#region "Save employee record"
protected void btnSave_Click(object sender, EventArgs e)
{
string doj = string.Empty;
//Joining selected Day, month and year to create Date of Joining
doj = Convert.ToString(ddlDay.SelectedValue + "/" + ddlMonth.SelectedValue + "/" + ddlYear.SelectedValue);
try
{
SqlCommand cmd = new SqlCommand("Insert into Emp_Tb(EmpName,Address,DOJ,Salary) values (@EmpName,@Address,@Doj,@Salary)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@EmpName", SqlDbType.VarChar, 100).Value = txtEmployeeName.Text.Trim();
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 500).Value = txtAddress.Text.Trim();
cmd.Parameters.Add("@Doj", SqlDbType.VarChar, 50).Value = doj;
cmd.Parameters.Add("@Salary", SqlDbType.Int).Value = txtSalary.Text.Trim();
cmd.ExecuteNonQuery();
//Clear all controls after saving the record
Clear_Controls();
//Bind gridview after saving the record
bindGridView();
}
catch (Exception ex)
{
// Show error occurred in message box using JavaScript
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(),Guid.NewGuid().ToString(), "alert('" + ex.Message.ToString() + "');", true);
}
finally
{
doj = string.Empty;
//close the database connection
con.Close();
}
}
#endregion
#region "Bind gridview with data"
public void bindGridView()
{
try
{
SqlCommand cmd = new SqlCommand("select * from Emp_Tb", con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
grdEmp.DataSource = ds;
grdEmp.DataBind();
}
else
{
//Bind Empty grdview with Columns names in Header and "No employee record found" message if no records are found in the database
BindEmptyGridWithHeader(grdEmp, ds);
}
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(),Guid.NewGuid().ToString(), "alert('" + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
}
}
#endregion
#region "paging in gridview"
protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdEmp.PageIndex = e.NewPageIndex;
//Call bind gridview function
bindGridView();
}
#endregion
#region "Cancel code in gridview"
protected void grdEmp_RowCancelingEdit(object sender, GridViewCancelEditEventArgse)
{
grdEmp.EditIndex = -1;
bindGridView();
}
#endregion
#region "Deletion in gridview"
protected void grdEmp_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
int empId = Convert.ToInt32(grdEmp.DataKeys[e.RowIndex].Value);
SqlCommand cmd = new SqlCommand("delete from Emp_Tb where Emp_Id_Pk= @EmpId", con);
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
grdEmp.EditIndex = -1;
bindGridView();
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(),Guid.NewGuid().ToString(), "alert('"+ ex.Message.ToString() +"');", true);
}
finally
{
con.Close();
}
}
#endregion
#region "updation in gridview"
protected void grdEmp_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string empName = string.Empty;
string address = string.Empty;
string dojUpdated = string.Empty;
try
{
// Finding dropdownlist inside gridview and getting updated value
DropDownList ddlEditDojYear = (DropDownList)(grdEmp.Rows[e.RowIndex].FindControl("ddlEditYear"));
DropDownList ddlEditDojMonth = (DropDownList)(grdEmp.Rows[e.RowIndex].FindControl("ddlEditMonth"));
DropDownList ddlEditDojDay = (DropDownList)(grdEmp.Rows[e.RowIndex].FindControl("ddlEditDay"));
// creating Updated DOJ field
dojUpdated = Convert.ToString(ddlEditDojDay.SelectedValue + "/" + ddlEditDojMonth.SelectedValue + "/" + ddlEditDojYear.SelectedValue);
//Read Emp_id_Pk from DataKeyNames
int empId = Convert.ToInt32(grdEmp.DataKeys[e.RowIndex].Value);
//Finding TextBox inside gridview and getting updated value
empName = ((TextBox)(grdEmp.Rows[e.RowIndex].FindControl("txtEmpName"))).Text.Trim();
address = ((TextBox)(grdEmp.Rows[e.RowIndex].FindControl("txtEditAddress"))).Text.Trim();
int sal = Convert.ToInt32(((TextBox)(grdEmp.Rows[e.RowIndex].FindControl("txtSal"))).Text.Trim());
SqlCommand cmd = new SqlCommand("update Emp_Tb set EmpName=@EmpName,Address=@Address,DOJ=@Doj,Salary=@Salary where Emp_Id_Pk=@EmpId", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
cmd.Parameters.Add("@EmpName",
...
No comments:
Post a Comment