ASP.NET Basics: GridView from Database with Paging, Sorting and Editing

How to use ASP.NET GridView control for paging and sorting data. Includes code samples to Insert, Update, and delete data from a GridView control.

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.

By Peter Bromberg   Popularity  (8591 Views)