BIND,SAVE,EDIT,UPDATE,CANCEL,DELETE,PAGING EXAMPLE IN GRIDVIEW IN ASP.NET C#

Introduction: In this article I am going to explain How to perform CRUD operation i.e. 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?

Implementation: Let's create an example web application to see it in action.

Bind,save, edit, update, delete, paging example in gridview

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>

Note: Replace the Data Source and the Initial catalog as per your application.

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.

<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">

<asp:TemplateField HeaderText="Emp Name" HeaderStyle-HorizontalAlign="Center">

<%#Eval("EmpName")%>

</ItemTemplate>

<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>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address" HeaderStyle-HorizontalAlign="Center">

<%#Eval("Address")%>

</ItemTemplate>

<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>

</asp:TemplateField>

<asp:TemplateField HeaderText="DOJ" HeaderStyle-HorizontalAlign="Center" ItemStyle-Width="180px">

<%#Eval("DOJ", "{0:dd/MM/yyyy}")%>

</ItemTemplate>

<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>

</asp:TemplateField>

<asp:TemplateField HeaderText="Salary" HeaderStyle-HorizontalAlign="Center">

<%#Eval("Salary")%>

</ItemTemplate>

<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>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

<asp:ImageButton ID="imgEdit" runat="server" CommandName="Edit" ImageUrl="~/Images/Edit.png" ToolTip="Edit" CausesValidation="false"/>

</ItemTemplate>

<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>

</asp:TemplateField>

<asp:TemplateField HeaderText="Delete" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

<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>

</asp:TemplateField>

</Columns>

</asp:GridView>

</fieldset>

Asp.Net C# 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 = new SqlConnection(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, GridViewCancelEditEventArgs e)

{

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", SqlDbType.VarChar, 50).Value = empName;

cmd.Parameters.Add("@Address", SqlDbType.VarChar, 50).Value = address;

cmd.Parameters.Add("@Doj", SqlDbType.VarChar, 50).Value = dojUpdated;

cmd.Parameters.Add("@Salary", SqlDbType.BigInt).Value = sal;

cmd.ExecuteNonQuery();

grdEmp.EditIndex = -1;

bindGridView();

}

catch (Exception ex)

{

ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('" + ex.Message.ToString() + "');", true);

}

finally

{

empName = string.Empty;

address = string.Empty;

dojUpdated = string.Empty;

con.Close();

}

#endregion

#region "Editing in gridview"

protected void grdEmp_RowEditing(object sender, GridViewEditEventArgs e)

{

grdEmp.EditIndex = e.NewEditIndex;

bindGridView();

}

#endregion

#region "Bind Empty Gridview with header"

public void BindEmptyGridWithHeader(GridView gridView, DataSet ds)

{

try

{

if (ds.Tables[0].Rows.Count == 0)

{

//Add a blank row to the dataset

ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());

//Bind the DataSet to the GridView

gridView.DataSource = ds;

gridView.DataBind();

//Get the number of columns to know what the Column Span should be

int columnCount = gridView.Rows[0].Cells.Count;

//Call the clear method to clear out any controls that you use in the columns.

gridView.Rows[0].Cells.Clear();

gridView.Rows[0].Cells.Add(new TableCell());

gridView.Rows[0].Cells[0].ColumnSpan = columnCount;

gridView.Rows[0].Cells[0].Text = "<font color=Red><b><center>No employee record Found !</center></b></font>";

}

catch (Exception ex)

{

//Do your exception handling here

}

#endregion

#region "Grid RowDataBound Event"

protected void grdEmp_RowDataBound(object sender, GridViewRowEventArgs e)

{

string day = string.Empty;

string month = string.Empty;

string year = string.Empty;

try

{

if (e.Row.RowType == DataControlRowType.DataRow && grdEmp.EditIndex == e.Row.RowIndex)

{

//Read Emp_Id_Pk from DataKeyNames

int empId = Convert.ToInt32(grdEmp.DataKeys[e.Row.RowIndex].Value);

SqlDataAdapter adp1 = new SqlDataAdapter("Select * from Emp_Tb where Emp_Id_Pk=@EmpId", con);

adp1.SelectCommand.CommandType = CommandType.Text;

adp1.SelectCommand.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;

DataTable dtEdit = new DataTable();

adp1.Fill(dtEdit);

string getDoj = dtEdit.Rows[0]["DOJ"].ToString();

//Splitting the DOJ field into day, month and year

string[] strDoj = getDoj.Split('/');

day = strDoj[0];

month = strDoj[1];

year = strDoj[2];

DropDownList ddlEditDojYear = (DropDownList)(e.Row.FindControl("ddlEditYear"));

DropDownList ddlEditDojMonth = (DropDownList)(e.Row.FindControl("ddlEditMonth"));

DropDownList ddlEditDojDay = (DropDownList)(e.Row.FindControl("ddlEditDay"));

//Fill Years

for (int i = 2013; i >= 1980; i--)

{

ddlEditDojYear.Items.Add(i.ToString());

}

ddlEditDojYear.SelectedValue = year;

//Fill Months

for (int i = 1; i <= 12; i++)

{

ddlEditDojMonth.Items.Add(i.ToString());

}

ddlEditDojMonth.SelectedValue = month;

//Fill days

FillDaysInsideGrid(ddlEditDojDay, ddlEditDojYear, ddlEditDojMonth, day);

}

catch (Exception ex)

{

ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('" + ex.Message.ToString() + "');", true);

}

finally

{

day = string.Empty;

month = string.Empty;

year = string.Empty;

}

#endregion

#region "DOJ"

protected void ddlYear_SelectedIndexChanged(object sender, EventArgs e)

{

FillDays();

}

protected void ddlMonth_SelectedIndexChanged(object sender, EventArgs e)

{

FillDays();

}

public void FillDays()

{

if (!Page.IsPostBack)

{

ddlDay.Items.Clear();

}

//getting number of days in selected month & year

int noofdays = DateTime.DaysInMonth(Convert.ToInt32(ddlYear.SelectedValue), Convert.ToInt32(ddlMonth.SelectedValue));

//Fill days

for (int i = 1; i <= noofdays; i++)

{

ddlDay.Items.Add(i.ToString());

}

if (!Page.IsPostBack)

{

ddlDay.Items.FindByValue(System.DateTime.Now.Day.ToString()).Selected = true;// Set current date as selected

}

#endregion

#region "Fill Days Inside GridView"

public void FillDaysInsideGrid(DropDownList ddlEditDojDay, DropDownList ddlEditDojYear, DropDownList ddlEditDojMonth, string day)

{

ddlEditDojDay.Items.Clear();

//getting number of days in selected month & year

int noofdays = DateTime.DaysInMonth(Convert.ToInt32(ddlEditDojYear.SelectedValue), Convert.ToInt32(ddlEditDojMonth.SelectedValue));

//Fill days

for (int i = 1; i <= noofdays; i++)

{

ddlEditDojDay.Items.Add(i.ToString());

}

ddlEditDojDay.SelectedValue = day;

}

#endregion

#region "Clear & reset all controls"

protected void btnReset_Click(object sender, EventArgs e)

{

Clear_Controls();

}

private void Clear_Controls()

{

txtEmployeeName.Text = string.Empty;

txtSalary.Text = string.Empty;

txtEmployeeName.Focus();

txtAddress.Text = string.Empty;

txtEmployeeName.Focus();

//Reset DOJ Field

//Fill Years with current year selected

ddlYear.Items.Clear();

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

ddlMonth.Items.Clear();

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 according to current month

ddlDay.Items.Clear();

//Getting number of days in selected month & year

int noofdays = DateTime.DaysInMonth(Convert.ToInt32(ddlYear.SelectedValue), Convert.ToInt32(ddlMonth.SelectedValue));

//Fill days

for (int i = 1; i <= noofdays; i++)

{

ddlDay.Items.Add(i.ToString());

}

ddlDay.Items.FindByValue(System.DateTime.Now.Day.ToString()).Selected = true;// Set current date as selected

}

#endregion

}

KANSIRIS

BIND,SAVE,EDIT,UPDATE,CANCEL,DELETE,PAGING EXAMPLE IN GRIDVIEW IN ASP.NET C#

0 comments:

Post a Comment

Complete Tutorials

Popular Posts

Labels