In this demo, we will use the ubiquitous Northwind Database. If you do not have this
database installed, you can get a copy here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034
First let's go through the steps needed to create an ASP.NET Webform with these
features.
Step 1. Create a C# ASP.NET Web Application named GridView in Visual Studio 2010
/ Visual Web Developer. If you want to keep things simple, you can create an
"empty web application" and add a single WebForm, "Default.aspx".
Step 2. Drag a GridView control, a LinkButton control and a Panel control into the
Default.aspx ASP.NET Web Form page.
(1) Rename the controls as follows:
GridView1 -> gvEmployee
LinkButton1 -> lbtnAdd
Panel1 -> pnlAdd
(2) Change the Text property of lbtnAdd to Add New.
(3) Right-click on gvEmployee, select Show Smart Tag -> Auto Format, choose style
Colorful and press OK to save.
(4) On Show Smart Tag, select Add New Columns, choose CommandField, check Delete,
Edit/Update and Show cancel button then press OK.
(5) On Show Smart Tag, select Add New Columns, choose BoundField, and add the following
three columns:
Header text Data field Read only
-----------------------------------------
EmployeeID EmployeeID Y
LastName LastName N
FirstName FirstName N
(6) On Show Smart Tag, select Edit Columns, un-check Auto-generate fields, select
LastName field, and click Convert this field into a TemplateField. Do the same
operation to FirstName field.
Step 3. Copy the methods from the sample, and paste them in code-behind:
Default.aspx.cs
Page_Load Method:
Initialize underlying objects when the Page is accessed for the first time.
BindGridView Method:
Set the sort column and sort order and bind the GridView control with a DataTable
from the database.
Step 4. Drag two TextBox controls and two LinkButton controls into pnlAdd.
(1) Rename the controls as follows:
TextBox1 -> tbLastName
TextBox2 -> tbFirstName
LinkButton1 -> lbtnSubmit
LinkButton2 -> lbtnCancel
(2) Change the Text properties of lbtnSubmit to Submit and lbltCancel to Cancel.
The page in the designer at this point should look like this:
Step 5. Navigate to the Property Sheet of gvEmployee and then switch to Events. Double-click
on the following events to generate method stubs for the Event handlers. After
that, fill the generated methods with the sample code.
(1) RowDataBound Event: Occurs when a data row is bound to data in a GridView control.
In this event, we add a client-side confirmation dialog box that appears when the
Delete button is clicked. It will help prevent a user from deleting a row accidentally.
(2) PageIndexChanging Event: Occurs when one of the pager buttons is clicked, but before the GridView control
handles the paging operation.
In other to show data in the new page, we need to set the index of new page and then
rebind the GridView control to show data in view mode.
When clicking the Edit button to edit a particular row, the GridVew control will
enter the edit mode and show Update and Cancel buttons.
(3) RowEditing Event: Occurs when a row's Edit button is clicked, but before the GridView control enters
edit mode.
To make the GridView control into edit mode for the select row, we need to set the
index of the row to edit and then rebind the
GridView control to render data in edit mode.
(4) RowCancelingEdit Event: Occurs when the Cancel button of a row in edit mode is clicked, but before the row
exits edit mode.
We can click the Cancel button to cancel the edit mode and show data in normal view
mode.
(5) RowUpdating Event: Occurs when a row's Update button is clicked, but before the GridView control
updates the row.
After modifying values in the selected row, we click the Update button to save changes
back to the data source.
To identify the Employee for editing, the EmployeeID value is required, which is
read-only and cannot be modified.
string strEmployeeID = gvEmployee.Rows[e.RowIndex].Cells[2].Text;
e.RowIndex is the index of current row.
In Step 2 we converted LastName and FirstName to TemplateFields, so we cannot get
the edit values directly. Since LastName and FirstName are both string values,
Label controls are generated in each ItemTemplate for displaying values and TextBox
controls are generated in each EditItemTemplate for editing values.
We can access the cells and get the values in the following way:
string strLastName = ((TextBox)gvEmployee.Rows[e.RowIndex].FindControl("TextBox1")).Text;
string strFirstName = ((TextBox)gvEmployee.Rows[e.RowIndex].FindControl("TextBox2")).Text;
After getting these values, we can save them back to the DataTable in ViewState,
Session or directly to the table in SQL Server.
(6) RowDeleting Event: Occurs when a row's Delete button is clicked, but before the GridView control
deletes the row.
To identify the Employee for deleting, the EmployeeID value is required, which is
read-only and cannot be modified.
string strEmployeeID = gvEmployee.Rows[e.RowIndex].Cells[2].Text;
After getting the EmployeeID, we can delete the Employee from the Table in SQL Server.
(7) Sorting Event: Occurs when the hyperlink to sort a column is clicked, but before the GridView control
handles the sort operation.
The SortDirection property on the GridView is changed only when the GridView is bound
to a DataSource control using the DataSourceID property. Otherwise, sort direction
always returns "Ascending" and needs to be manipulated manually.
In the Page_Load Event, we store a default sorting expression in ViewState.
ViewState["SortExpression"] = "EmployeeID ASC";
We set the sort column and sort order based on it in BindGridView method:
dvEmployee.Sort = ViewState["SortExpression"].ToString();
So when first visiting the page, all Employee table rows will be shown in ascending
order of EmployeeID.
When clicking a column’s header to sort this column, we need to get previous sort
column and sort order and compare the sort column with the current column. If
they are same, we just change the sort order to show data in the other order,
e.g. ascending to descending or descending to ascending.
If not, we specify the current column as the sort column and set the sort order to
ASC. The sort expression is stored into ViewState to persist data across postbacks.
Step 6. Double-click on the Click event of lbtnAdd to generate the Event handler
and then fill the generated methods with the sample code. Do the same operations
to lbtnSubmit and lbtnCancel.
lbtnAdd.Click Event:
Hide the Add button and showi Add panel.
lbtnSubmit.Click Event:
Fetch the values of the TextBox controls and add new row to the
DataTable in ViewState or the table in SQL Server.
lbtnCancel.Click Event:
Show the Add button and hide the Add panel.
Your completed codebehind should look like the following:
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;
namespace GridView
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Enable the GridView paging option and specify the page size.
gvEmployee.AllowPaging = true;
gvEmployee.PageSize = 15;
// Enable the GridView sorting option.
gvEmployee.AllowSorting = true;
// Initialize the sorting expression.
ViewState["SortExpression"] = "EmployeeID ASC";
// Populate the GridView.
BindGridView();
}
}
private void BindGridView()
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
// Create a DataSet object.
DataSet dsEmployee = new DataSet();
// Create a SELECT query.
string strSelectCmd = "SELECT EmployeeID,LastName,FirstName FROM EMPLOYEES";
// Create a SqlDataAdapter object
// SqlDataAdapter represents a set of data commands and a
// database connection that are used to fill the DataSet and
// update a SQL Server database.
SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);
// Fill the DataTable named "Employee" in DataSet with the rows
// returned by the query.
da.Fill(dsEmployee, "Employee");
// Get the DataView from Employee DataTable.
DataView dvEmployee = dsEmployee.Tables["Employee"].DefaultView;
// Set the sort column and sort order.
dvEmployee.Sort = ViewState["SortExpression"].ToString();
// Bind the GridView control.
gvEmployee.DataSource = dvEmployee;
gvEmployee.DataBind();
}
}
// GridView.RowDataBound Event
protected void gvEmployee_RowDataBound(object sender, GridViewRowEventArgs e)
{
// Make sure the current GridViewRow is a data row.
if (e.Row.RowType == DataControlRowType.DataRow)
{
// Make sure the current GridViewRow is either
// in the normal state or an alternate row.
if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
{
// Add client-side confirmation for deleting.
((LinkButton)e.Row.Cells[1].Controls[0]).Attributes["onclick"] = "if(!confirm('Are you certain you want to delete this Employee ?'))
return false;";
}
}
}
// GridView.PageIndexChanging Event
protected void gvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
// Set the index of the new display page.
gvEmployee.PageIndex = e.NewPageIndex;
// Rebind the GridView control to
// show the data in the new page.
BindGridView();
}
// GridView.RowEditing Event
protected void gvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
// Put the GridView control into edit mode
// for the selected row.
gvEmployee.EditIndex = e.NewEditIndex;
// Rebind the GridView control to show data in edit mode.
BindGridView();
// Hide the Add button.
lbtnAdd.Visible = false;
}
// GridView.RowCancelingEdit Event
protected void gvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
// Exit edit mode.
gvEmployee.EditIndex = -1;
// Rebind the GridView control to show data in view mode.
BindGridView();
// Re-Show the Add button.
lbtnAdd.Visible = true;
}
// GridView.RowUpdating Event
protected void gvEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
using (SqlCommand cmd = new SqlCommand())
{
// Assign the connection to the command.
cmd.Connection = conn;
// Set the command text
// SQL statement or the name of the stored procedure
cmd.CommandText =
"UPDATE Employees SET LastName = @LastName, FirstName = @FirstName WHERE EmployeeID
= @EmployeeID";
// Set the command type
// CommandType.Text for ordinary parameterized SQL statements;
// CommandType.StoredProcedure for stored procedures.
cmd.CommandType = CommandType.Text;
// Get the EmployeeID of the selected row.
string strEmployeeID = gvEmployee.Rows[e.RowIndex].Cells[2].Text;
string strLastName = ((TextBox) gvEmployee.Rows[e.RowIndex].FindControl("TextBox1")).Text;
string strFirstName = ((TextBox) gvEmployee.Rows[e.RowIndex].FindControl("TextBox2")).Text;
// Append the parameters.
cmd.Parameters.AddWithValue("@EmployeeID", strEmployeeID);
cmd.Parameters.AddWithValue("@LastName", strLastName);
cmd.Parameters.AddWithValue("@FirstName", strFirstName);
// Open the connection.
conn.Open();
// Execute the command.
cmd.ExecuteNonQuery();
}
}
// NOTE: The nested using {... } directives in these methods will automatically close
and dispose both Command and Connection objects.
// It is the equivalent of wrapping the code in a try / finally block.
// Exit edit mode.
gvEmployee.EditIndex = -1;
// Rebind the GridView control to show data after updating.
BindGridView();
// Show the Add button.
lbtnAdd.Visible = true;
}
// GridView.RowDeleting Event
protected void gvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
using (SqlCommand cmd = new SqlCommand())
{
// Create a command object.
// Assign the connection to the command.
cmd.Connection = conn;
// Set the command text
// SQL statement or the name of the stored procedure
cmd.CommandText = "DELETE FROM Employees WHERE employeeID = @EmployeeID";
// Set the command type
// CommandType.Text for ordinary SQL statements;
// CommandType.StoredProcedure for stored procedures.
cmd.CommandType = CommandType.Text;
// Get the EmployeeID of the selected row.
string strEmployeeID = gvEmployee.Rows[e.RowIndex].Cells[2].Text;
// Append the parameter.
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = strEmployeeID;
// Open the connection.
conn.Open();
// Execute the command.
cmd.ExecuteNonQuery();
}
}
// Rebind the GridView control to show data after deleting.
BindGridView();
}
// GridView.Sorting Event
protected void gvEmployee_Sorting(object sender, GridViewSortEventArgs e)
{
string[] strSortExpression = ViewState["SortExpression"].ToString().Split(' ');
// If the sorting column is the same as the previous one,
// then change the sort order.
if (strSortExpression[0] == e.SortExpression)
{
if (strSortExpression[1] == "ASC")
{
ViewState["SortExpression"] = e.SortExpression + " " + "DESC";
}
else
{
ViewState["SortExpression"] = e.SortExpression + " " + "ASC";
}
}
// If sorting column is another column,
// then specify the sort order to "Ascending".
else
{
ViewState["SortExpression"] = e.SortExpression + " " + "ASC";
}
// Rebind the GridView control to show sorted data.
BindGridView();
}
protected void lbtnAdd_Click(object sender, EventArgs e)
{
// Hide the Add button and show Add panel.
lbtnAdd.Visible = false;
pnlAdd.Visible = true;
}
protected void lbtnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
using (SqlCommand cmd = new SqlCommand())
{
// Assign the connection to the command.
cmd.Connection = conn;
// Set the command text
// SQL statement or the name of the stored procedure
cmd.CommandText = "INSERT INTO Employees ( LastName, FirstName ) VALUES ( @LastName, @FirstName
)";
// Set the command type
// CommandType.Text for ordinary SQL statements;
// CommandType.StoredProcedure for stored procedures.
cmd.CommandType = CommandType.Text;
// Append the parameters. We show the use of both Add and AddWithValue methods.
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = tbLastName.Text;
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = tbFirstName.Text;
// Open the connection.
conn.Open();
// Execute the command.
cmd.ExecuteNonQuery();
}
}
// Rebind the GridView control to show inserted data.
BindGridView();
// Empty the TextBox controls.
tbLastName.Text = "";
tbFirstName.Text = "";
// Show the Add button and hiding the Add panel.
lbtnAdd.Visible = true;
pnlAdd.Visible = false;
}
protected void lbtnCancel_Click(object sender, EventArgs e)
{
// Empty the TextBox controls.
tbLastName.Text = "";
tbFirstName.Text = "";
// Show the Add button and hiding the Add panel.
lbtnAdd.Visible = true;
pnlAdd.Visible = false;
}
}
}
You can download the completed, working Visual Studio 2010 solution here. if you are using SQLExpress, please modify the connection string accordingly.