On-demand data population, custom paging, client-side sorting and other general functionality in GridView

• Download source code - 70.2 KB

Introduction

This article explains the implementation of day-to-day tasks associated with gridview control. Developers come across these requirement almost on a daily basis. The aim is to gather all these routine tasks under one umbrella. After the end of this article you will have an idea about how to:

• Populate data on demand. 
• Adding record with multiple columns(more than the columns shown on the gridview) 
• Editing gridview data on the same page with validation.
• Custom paging.
• Client-side sorting. 
• Searching. 
• Check/Uncheck all records(client-side)
• Showing image based on condition in itemtemplate field of gridview. 

Background

To be honest I started thinking about writing this article a couple of months ago. If you visit quick answers section on this website, you will notice that there are a number of questions regarding gridview control. Since gridview in my opinion is by far the mostly used control when it comes to representing tabular data, developers do not hesitate to use it as much as possible. Obviously we as developers need to implement functionality like sorting, searching records, custom paging, adding, deleting, updating regularly. There also comes situation when the user wants to display only few columns initially on gridview but on clicking a row or a column want to display the complete/remaining details.  

Mostly there are two approaches a developer takes(the ones I have met and discussed).   

• Add a child gridview as an itemtemplate in the parent gridview and use rowdatabound event to populate it. 
• Open a popup window that display the details based on id of the row passed as querystring.

The problem with the first approach is that it is very expensive. Every time a row is populated with data from database, anothe call is made to populate the child gridview. This makes an overhead and affects the performance of the application considerably.

The second approach does not look good although it works, but there are always popup blocker that can ruin the show. And  if the security of the client system is adjusted as per their convenience, they will not see the opened popup window at all.   

So, the solution? jQuery. We will implement jQuery's $.ajax() method to get the details in JSON format and populate the content on a modal popup.    

Using the code

First of all create a database and run the script contained in script.txt file in the source code. Modify your connection string and we are good to go.

Showing details of the user

First thing we will create is an HTTPHandler. This handler will provide us details of the selected user in JSON format. This could be done using a web page also but HTTPHandlers are always good for requirement like this because they are fast. They don't have to go through the page life cycle. So, in this HTTPHandler we will be adding a class and properties. 

public class User
{
    public string EDCode { get; set; }
    public string EDFirstName { get; set; }
    public string EDLastName { get; set; }
    public string EDDOB { get; set; }
    public string EDCellPhoneNumber { get; set; }
    public string EDLandlineNumber { get; set; }
    public string EDEmailID { get; set; }
    public string EDAddress { get; set; }
    public string EDImagePath { get; set; }
    public string EDExperience { get; set; }
    public string EDSkills { get; set; }
    public string EDDesignation { get; set; }
    public string EDPreviousCompany { get; set; }
    public string EDDOJ { get; set; }
    public string EDIsPermanent { get; set; }
    public string EDIsReportingManager { get; set; }
    public string EDDOB_MMDDYYYY { get; set; }
    public string EDDOJ_MMDDYYYY { get; set; }
}

Now is the time to populate data. For this the function is written as follows:

private User GetUserDetails(int Id)
{
    var user = new User();
    using (SqlConnection con = new SqlConnection(
      System.Web.Configuration.WebConfigurationManager.ConnectionStrings[
      "SqlConnection"].ConnectionString))
    {
        con.Open();
        DataTable dtFiles = new DataTable();
        SqlCommand com = new SqlCommand("SP_GetCompleteUserDetails", con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@userid", Id);
        SqlDataReader dr = com.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();
            user.EDCode = dr["ED_Code"].ToString().Trim();
            user.EDFirstName = dr["ED_FirstName"].ToString().Trim();
            user.EDLastName = dr["ED_LastName"].ToString().Trim();
            user.EDDOB = dr["ED_DOB"].ToString().Trim();
            user.EDDOB_MMDDYYYY = Convert.ToDateTime(dr["ED_DOB"]).ToString("MM/dd/yyyy");
            user.EDCellPhoneNumber = dr["ED_CellPhoneNumber"].ToString().Trim();
            user.EDLandlineNumber = dr["ED_LandlineNumber"].ToString().Trim();
            user.EDEmailID = dr["ED_EmailID"].ToString().Trim();
            user.EDAddress = dr["ED_Address"].ToString().Trim();
            user.EDImagePath = dr["ED_ImagePath"].ToString().Trim();
            user.EDExperience = dr["ED_Experience"].ToString().Trim();
            user.EDSkills = dr["ED_Skills"].ToString().Trim();
            user.EDDesignation = dr["ED_Designation"].ToString().Trim();
            user.EDPreviousCompany = dr["ED_PreviousCompany"].ToString().Trim();
            user.EDDOJ = dr["ED_DOJ"].ToString().Trim();
            user.EDDOJ_MMDDYYYY = Convert.ToDateTime(dr["ED_DOJ"]).ToString("MM/dd/yyyy");
            user.EDIsPermanent = dr["ED_IsPermanent"].ToString().Trim();
            user.EDIsReportingManager = dr["ED_IsReportingManager"].ToString().Trim();
        }
    }
    return user;
}

Now when we have object of User class, we will be serializing it into JSON object and our HTTPHandler will return this object that we will be using later to display the details of the selected user. The code for serializing and returning the JSON object is as follows:

public void ProcessRequest(HttpContext context)
{
    var userDetails = GetUserDetails(Convert.ToInt32(context.Request["EID"].Trim()));
    JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
    string serializedUserDetails = javaScriptSerializer.Serialize(userDetails);
    context.Response.ContentType = "text/html";
    context.Response.Write(serializedUserDetails);
}

The above code is self explanatory. All we have done is fetched the details from database and converted it into a JSON object. Now comes the real picture: to show these details on our page.

I have added a label in itemtemplate of gridview and added an attribute to it from code behind. The markup of the label is: 

<asp:Label ID="linkDetails" 
  runat="server" Text='<%# Eval("ED_Code") %>' 
  CssClass="userDetails"></asp:Label>

And in gridview's rowdatabound event added the onclick attribute like:

protected void GridViewUsers_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DataRowView rowView = (DataRowView)e.Row.DataItem;
        string rowID = rowView["ED_ID"].ToString();
        Label linkDetails = new Label();
        Button btnEditRow = new Button();
        linkDetails = (Label)e.Row.FindControl("linkDetails");
        btnEditRow = (Button)e.Row.FindControl("btnEditRow");
        linkDetails.Attributes.Add("onclick", 
          "getDetails('" + rowID + "');");
        btnEditRow.Attributes.Add("onclick", 
          "return getDetailsToEdit('" + rowID + "');");
    }
} 

In the above code I have added onclick attribute to two controls. linkDetails will be used to show the details of the selected user whereas btnEditRow is the button I have used to edit the details. Editing will also be done in the same fashion(Modal Popup).

There are a number of modal popups available on the internet. I have used reveal modal popup. It is lightweight, configurable and very easy to use. All you have to do is wrap the contents of modal popup inside a div and call the JavaScript method to display the modal popup either on pageLoad or click of an element. You can download it from http://zurb.com/playground/reveal-modal-plugin 

The function to get the details is written like: 

function getDetails(objID) {
    $.ajax({
        url: "AjaxRequestHandler.ashx",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        data: { 'EID': objID },
        responseType: "json",
        success: OnComplete,
        error: OnFail
    });
}

On successfully receiving the JSON object the function OnComplete will be called and it will populate the div content. The implementation of OnComplete function is as follows:

function OnComplete(result) {
    $('#UserDetails').reveal({
        animation: 'fadeAndPop',
        animationspeed: 300,
        closeonbackgroundclick: false,
        dismissmodalclass: 'close-reveal-modal'
    });
    $('#userid').text(result.EDCode);
    $('#username').text(result.EDFirstName + ' ' + result.EDLastName);
    $('#userdob').text(result.EDDOB);
    $('#usercellnumber').text(result.EDCellPhoneNumber);
    $('#userlandlinenumber').text(result.EDLandlineNumber);
    $('#useremailid').text(result.EDEmailID);
    $('#useraddress').text(result.EDAddress);
    $('#userexperience').text(result.EDExperience);
    $('#userskills').text(result.EDSkills);
    $('#userdesignation').text(result.EDDesignation);
    $('#userpreviouscompany').text(result.EDPreviousCompany);
    $('#userdoj').text(result.EDDOJ);
    $('#userpermanent').text(result.EDIsPermanent);
    $('#userreportingmanager').text(result.EDIsReportingManager);
    $("#userImage").attr("src", 'UserImages/' + result.EDImagePath);
}

Now when you run this program and click on Employee ID you will get the result in following format:

Similarly editing/adding is done. Adding/updating of record is also done using the same modal popup. The result will look like:

Implementing client-side check/uncheck functionality

For check/uncheck functionality I have used jQuery. The reason is that it is very easy to find checkbox placed inside the gridview and applying attributes to all of them in a single line of code rather that applying attributes to each one of them using databound event. The code is fairly simple.

var selectAllCheckBox = '#<%=GridViewUsers.ClientID%> input[id*="chkAllEmployee"]:checkbox';
var selectEmployeeCheckBox = '#<%=GridViewUsers.ClientID%> input[id*="CheckBoxEmployeeID"]:checkbox';
 
function CheckUncheckAll() {
var totalCheckboxes = $(selectEmployeeCheckBox);
var checkedCheckboxes = totalCheckboxes.filter(":checked");
var allCheckboxesAreChecked = (totalCheckboxes.length === checkedCheckboxes.length);
$(selectAllCheckBox).attr('checked', allCheckboxesAreChecked);
}
 
function pageLoad() {
$(selectAllCheckBox).live('click', function () {
$(selectEmployeeCheckBox).attr('checked', $(this).is(':checked'));
CheckUncheckAll();
});
$(selectEmployeeCheckBox).live('click', CheckUncheckAll);
CheckUncheckAll();  

First line of the above code is used to find the checkbox in the header of gridview and the second line is used to find all the checkboxes with id "CheckBoxEmployeeID" (these are the checkboxes for each row).

Next there is a function 'CheckUncheckAll'. This function finds all checkboxes and based on the attribute of the header checkbox, sets the property of the checkboxes in gridview row to the header checkbox's checked state. This function is called on pageLoad method so that we don't lose the bindings on partial postback. 

Implementing client-side sorting

Client-side sorting is implemented using jQuery TableSorter plugin. It is available at http://tablesorter.com/docs/ .

The only important thing while using this plugin is that it works with HTML table having <THEAD> and <TBODY> tags and by default gridview is not rendered with these tags. We will write some code that will generate these tags. For this, after binding data to gridview, the following code is added:

if (GridViewUsers.Rows.Count > 0)
{
	GridViewUsers.UseAccessibleHeader = true;
	GridViewUsers.HeaderRow.TableSection = TableRowSection.TableHeader;
	GridViewUsers.FooterRow.TableSection = TableRowSection.TableFooter;
}

Implementing custom paging

As developer we should always think about the performance of the application. When using gridview a common practice is to use the default paging functionality that comes with the gridview. Suppose your query returns 10000 records and the page size of the gridview is 50, on initial page you will see 50 records and the remaining 9950 records will be truncated. This is not a good idea. You can write your query in such a way that it return only the desired rows. Fortunately we have rowcount property in SQL server that comes to the rescue. We will be passing the page index and number of records to fetch and the stored procedure will return only that much rows. The stored procedure's script can be found in the source code and the SQL code is quite simple to understand. 

Points of Interest

I have tried to keep the code as simple as I could. There is still scope of improvement in the example code. Suggestions are welcome and so is criticism. Hope you will find this article helpful. For any explanation and query related to source code please feel free to comment.