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:
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.
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.
Filter or SearchNow 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.
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.
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>
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.
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.
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!
Sridhar JohnPosted May 31, 2018, 5:02 AM
Very nice..,
Humayun Kabir MamunPosted Mar 29, 2016, 5:49 AM
Nice...
Suthish NairPosted May 30, 2013, 3:53 AM
Sorry Atul.. I never tried this on sharepoint..
Atul WalekarPosted May 29, 2013, 2:26 AM
I am tring to incarporate this code in sharepoint Online 365. Sorting is work but filtering and export in CSV not work. While exporting only blank window open
Suthish NairPosted Apr 26, 2013, 7:11 AM
chnadu, yes remove the pagination...
Suthish NairPosted Apr 26, 2013, 7:11 AM
sindu, It should work.. debug the handler having context Request information's.
sindu cseeditedPosted Apr 25, 2013, 11:11 AMEdited Apr 25, 2013, 11:14 AM
using only sorting and export but the csv is empty used the jquery table sorter with custom parsers worked fine and export also worked fine but the CSV is empty. am i missing anything?
chnadu yeditedPosted Jan 17, 2013, 3:31 AMEdited Jan 17, 2013, 3:35 AM
gre8!! its working on current Page fine. is it possible search and sort all pages?
Rajamanikkam SPosted Apr 2, 2012, 2:45 AM
Thanks dude....
JEGANPosted Mar 22, 2012, 1:30 AM
Just now i see your article for Asp.net grid view sorting, paging and filter using jquery really nice. I need that functionality for my application but when i try to add your code in my application its not working because i am using row command event for the grid view and another thing is when i use the filter text box its filtering only the 10 records(excluding the paging records) but i am in need of filtering all records including the paging records. Can you help me by send the sample codes Contact me : [email protected]
BaluReddy KovvurPosted Nov 16, 2011, 5:04 AM
Hi, I have data in table (sql srevre 2008 back end). i want to display data in jquaey grid(with update, delete ,sorting,searching and paging) please do the needful(without using any dll). Regards, Balu Reddy
BaluReddy KovvurPosted Nov 16, 2011, 4:46 AM
Hi, i have used gridview in all forms in my project. i want to migrate gridview to jquery grid with all options(update ,delete,sorting,paging). previously my collegue worked on this he found a dll but it is not open source.(below is the code for jquery grid using that dll). <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server"> <!-- The jQuery UI theme that will be used by the grid --> <link rel="stylesheet" type="text/css" media="screen" href="../js/jquery-ui.css" /> <!-- The jQuery UI theme extension jqGrid needs --> <link href="../js/ui.jqgrid.css" rel="stylesheet" type="text/css" /> <!-- jQuery runtime minified --> <script src="../js/jquery-1.5.2.min.js" type="text/javascript"></script> <!-- The localization file we need, English in this case --> <script src="../js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script> <!-- The jqGrid client-side javascript --> <script src="../js/trirand/jquery.jqDatePicker.min.js" type="text/javascript"></script> <script src="../js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script> <script src="../js/trirand/jquery.jqAutoComplete.min.js" type="text/javascript"></script> <style type="text/css"> body, html { font-size: 80%; } </style> </asp:Content> <table width="100%"> <tr> <td align="center"> <h3> Add Company</h3> </td> </tr> <tr> <td align="right"> <%--<asp:Button runat="server" ID="BtnExportExcel" OnClick="BtnExportExcelClick" Text="Export To Excel" />--%> <asp:ImageButton runat="server" ID="BtnExportExcel" ImageUrl="~/Images/ExportExcel1.jpeg" Height="21px" ToolTip="Export To Excel" AlternateText="Export To Excel" Width="119px" OnClick="BtnExportExcelClick" /> </td> </tr> <tr> <td> <asp:Label runat="server" ID="LblError" ForeColor="Red"></asp:Label> </td> </tr> <tr> <td> <div> <trirand:JQGrid runat="server" ID="JqGridCompany" AutoWidth="true" Visible="true" EnableViewState="false" OnRowAdding="JqGridCompany_RowAdding" OnUnload="JqGridContractsUnload" Width="100%" Height="100%"> <Columns> <trirand:JQGridColumn DataField="CompanyName" HeaderText="Company Name" Editable="true" Searchable="true" SearchType="AutoComplete" SearchControlID="AutoComplete1" SearchToolBarOperation="Contains"> <EditClientSideValidators> <trirand:RequiredValidator /> </EditClientSideValidators> </trirand:JQGridColumn> <trirand:JQGridColumn DataField="Address" HeaderText="Address" Editable="true" Searchable="false" /> <trirand:JQGridColumn DataField="City" HeaderText="City" Editable="true" Searchable="false" /> <trirand:JQGridColumn DataField="State" HeaderText="State" Editable="true" Searchable="false" /> </Columns> <ToolBarSettings ShowRefreshButton="True" ShowAddButton="true" ToolBarPosition="TopAndBottom" /> <AppearanceSettings ShowRowNumbers="true" ScrollBarOffset="0" HighlightRowsOnHover="true" /> <AddDialogSettings CloseAfterAdding="true" Caption="Add Company" Width="450" Height="300" SubmitText="Save" /> <PagerSettings PageSize="50" PageSizeOptions="[50,100,150,200,250,300]" /> <SortSettings InitialSortColumn=""></SortSettings> <ToolBarSettings ShowSearchToolBar="true" /> </trirand:JQGrid> <trirand:JQAutoComplete DisplayMode="ControlEditor" DataTextField="CompanyName" runat="server" ID="AutoComplete1" /> </div> </td> </tr> </table> i have table in sqlserver 2008 .i want to display data in jquery grid (update ,delete,sorting,paging)on that. please suggest alternate for this without using dll. Regards, Balu Reddy
Suthish NairPosted Aug 23, 2011, 7:43 AM
Thank You !!!
ritesh assawaPosted Aug 23, 2011, 7:16 AM
Nice article!!!keep posted like this!!!.Thanks
Ravi K KashyapeditedPosted Jun 28, 2011, 3:56 AMEdited Jun 28, 2011, 4:45 AM
thanks sir for the information :)