Reader Level:
Articles

GridView: Filtering, Sorting, Paging And Export to CSV Using JQuery

By Suthish Nair on June 27, 2011
This article will show, how to use jQuery plugins for Filtering, Sorting, Paging and Export to CSV.
  • 1
  • 0
  • 45702


Introduction


Last week the page module of one of our old web sites needed some enhancements. The module listed the Price Order details of around 6000+ records.

The control used was GridView for populating n number of records. I don't know why my former team member used this control to load that much data.

This control also had a couple of links that opens another popup window etc.. The page size was unnecessarily large.

The new enhancements was to provide users with options for data Filtering, Sorting, Paging and Export to CSV.

Within an hour, I made all the changes (writing code behind logics) and posted the pages to the test environment. Ah!

The time required for page rendering plus the enhancements was too long. The performance of the website was

reduced due to this and I eventually forcefully recycled the IIS website application pool a couple of times.

I was wondering how to resolve the problem so I searched the web and finally got some jQuery plugins which will easily satisfy the needs.


The Filtering, Sorting, Paging was so fast, only the export option took a significant amount of time due to the post back of the web page.

Anyway these plugins were so useful for me that I thought of sharing with all of you guys.

I hope that as a result of reading this article, the reader using sorting and paging will use jQuery plugins instead of writing code behind logic.


Referred links:

Filtering an ASP.NET GridView control with jQuery by Joe Stagner


http://tablesorter.com/docs/

https://github.com/riklomas/quicksearch


Getting Started

Drag and drop any control that to be rendered into an HTML Table. Here we are using a GridView because most developers

use this control by default. Bind a SqlDataSource to the GridView control and also add a TextBox control above the GridView.

This TextBox is used to filter the records. Now download jQuery plugins and respective files from above posted links.

Check the screen shot for the listed files.

11.jpg

Drag and drop those script files to the .aspx source page in the same order as shown below. Also write down the JavaScript function

that is called on document.ready event.

22.jpg

33.jpg

Filter or Search

Now run the page and put some text in the TextBox control to search or filter the GridView output..

For example enter "Germany" in the TextBox; it will filter out all matching rows from the table as below.

44.jpg

Note: We need to avoid post backs of pages for successful implementation of these enhancements. Because if a post back is done

then every time the page is reloaded, the filter table is again rebound with new records. The link no. 1 will do a full post back and

the other link is a simple anchor tag used to avoid post back. Both links will open a new pop up window. Just an example of how to

avoid post backs.

55.jpg66.jpg

Sorting

Now for the table sorting part. Click on the table row header and we can see how fast the tables get sorted in ascending/descending order.

The good part is we can sort all the columns of the table without writing any extra line of code. So forget about AllowSorting property and

the code behind logic for sorting the columns.


Paging

Now the paging part. We can set the the number of rows to be displayed initially when the page loads and also after paging.

These settings can be updated on below default size value and also by changing option values.

We can also have multiple different option values  but not for default size.

For e.g:

<option selected="selected" value="5">5</option>
<option selected="selected" value="10">10</option>
<option selected="selected" value="15">15</option>

77.jpg
88.jpg


How the jQuery Plugins works?

When a GridvView get rendered as HTML, the source file looks like below..

<table>
  <tr>
     <td>CustomerId</td>
     <td>Order Date</td>
     <td>Required Date</td>
  </tr>
 <tr>
      <td> ---  respective row values --- </td>
 </tr>
</table>


Now check above jQuery method named quicksearch (1), which having three input values/tags (table, tbody, tr).

The jQuery plugin will take these inputs and search for following tags in our HTML source file.

Now if any matching records found that entered in TextBox then, those rows with the records pertaining will get displayed to users and

rest of the rows get hided (item.display='').

Thus showing the filtered rows as output. But, what happens when no matching records/rows found?

All the rows will get hided, even the table headers also. But, we need to show the table header also, even there is no matching rows found.

How to over come this issue. How will the GridView get rendered with those tags?

To achieve that we needto call the GridView1_PreRender event and set the TableSelection accessibility.

Also set the property UseAccessibleHeader="true". This will gets or sets a value indicating whether a GridView control renders its header in

an accessible format. Re-run the web page and check the View Source; you will see the rendered HTML tags with <thead> and <tbody>.

Now the jQuery will search for table, tbody, tr tags only and thead tags will get ignored.

99.jpg

Export to CSV File

On a button click event calling jQuery $('#GridView1').table2CSV(), contains a method which will then call a Handler (.ashx) file to do a

Response.Write and export the table to .csv.

100.jpg101.jpg

Depending upon the data size it can require a significant amount of time to export to a CSV file. Also, if you are using paging then the export

will work for only those rows depending on the paging size. For example the total rows count is 100 and for paging the size applied is 25. Then

during export only 25 records are saved to the file. If all the records needs to be exported then we need to change the default size of paging.

This export to functionality is not that good, but we can to an extent to avoid writing code behind codes.


Conclusion

Hope you all liked the power of jQuery with GridView for Filtering, Sorting, Paging and Export to CSV.

Post your comments and rate the article. If you have any questions, please post to the Forums.

Sample pages, files and code are attached for reference. Download it and test at your end.


Thank You!

COMMENT USING

Trending up