Using asp.net GridView with DataTables and Large DataSets

amandak

I have a custom grid control that inherits from the asp.net GridView. In the OnPreRender event it evaluates custom properties on my control and dynamically builds/registers a client script to use DataTables for paging, sorting, etc. client-side. The code-behind of each aspx page sets the grid DataSource and calls the DataBind. It also implements the DataBound and RowDataBound events of the GridView to manipulate the data and/or set additional properties on each row specific to data in the row (based on user permissions or other criteria). That's my set-up.

Here's my problem. When I have large datasets (say 1000 rows and over -- we do limit total number of rows returned to 5000 at the database level), the grid takes forever (can take several minutes if it finishes at all) to render. We set our paging up to chunk rows into page lengths of 10 to 25 rows most of the time, so I really want the rows of the active page to show up quickly. I have attemped a server-side implementation using bServerSide=true and sAjaxSource="some aspx page I built" but I can't get it to work the way I need it to. Since we are using the DataTables to enhance a GridView, the grid has to render before the DataTables script can be applied. I force the render by adding one empty dummy row in the code-behind (if I don't do that then the call to my sAjaxSource never happens). When my sAjaxSource page executes I can use the request params and get the subset of data for just the page we want to display, and return it in the proper JSON format for DataTables to display it on the page just fine. EXCEPT, I only have access to the DataBound and RowDataBound events of the GridView when I add my dummy row, before I ever get to my sAjaxSource page and get my real data. So my data doesn't look right when it displays. I need access during the binding events to mask data or add links to data, etc...the data is useless without those features.

I'd really like postback behaviour on paging and sorting, but with all the bells and whistles I've become accustomed to with DataTables. Then I could limit the data to the active grid page in my aspx page before calling the databind and also have access to the binding events for my masking and linking bells and whistles.

I tried going back to strictly client-side behaviour and using the bDeferRender=true property, but the documentation is pretty specific that it only works with Ajax or Javascript datasources so I wasn't surprised that it didn't help (in fact it made have made things worse).

Any advice on some other features or properties I might want to try?

allan

I'm afraid I don't know anything about GridView, but I can of course advise on the DataTables side of things!

I'm really surprised that the render for just 1000 rows takes minutes - really surprised! Is that because the GridView takes a long time to render, or is it actually DataTables taking that long? Do you have the table element in a display:none when loading - we've found in the past, in these forums, that IE does something really odd that kills performance in that state.

Regarding server-side processing, I'm afraid I'd have to suggest asking in an ASP forum to get help with that aspect - however to the data during the binding etc.

And finally with client-side processing and deferred rendering - you say that it only works with Ajax or JS data source, which is absolutely correct, but so does server-side processing. I'm not sure I quite understand which client-side processing with deferred rendering wouldn't work for you?

Allan

amandak

Thanks for the response Allan. Your comment about the table element in display:none gives me something to look into, and sparks a few new ideas.

And I think I'm also a little confused about what an Ajax or JS data source is. I'm primarily a middle tier and database geek, so I don't have all the UI lingo down pat. Here's how my gridview (enhanced by DataTables for client-side paging and sorting) gets its data: aspx page code-behind load event is used to make a call to a middle tier object that then calls a data tier object which executes a stored procedure in a SQLServer db; the data is passed back up to the aspx page code-behind in the form of an enumerable list of custom objects (one object for each row of data returned by the stored procedure, with each data field of the row used to populate a property of the object). That list of objects is then assigned to the DataSource property of the GridView and bound...and then the whole thing of course gets pushed out to the client. In my attempt at using bServerSide=true in DataTables I used that same approach to getting the data in my sAjaxSource page and then iterated through the list of objects pulling the property values out into the aaData array format for the JSON response. I understand the aaData format to be an Ajax data source. I don't think my list of custom objects is. I would really appreciate your opinion and perspective on that though.

I'll post back new findings when I find them.

Thanks!

Amanda

allan

> I used that same approach to getting the data in my sAjaxSource page and then iterated through the list of objects pulling the property values out into the aaData array format for the JSON response

That isn't needed :-). With mDataProp DataTables will accept almost any JSON data: http://datatables.net/blog/Extended_data_source_options_with_DataTables .

Look forward to hearing how you get on.

Allan

amandak

Thanks for the tip Allan.

I've gone back to square one to do some due diligence testing. The application I am working on is used exclusively with IE 7 as the browser, so we never tested it with any other browsers. I've gone back now and tested it with Minefield (Firefox)...4000+ rows load in 9 seconds. The same dataset behaves erratically in IE -- sometimes takes minutes to load, sometimes throws up the "long running script" msgbox, sometimes takes 40 seconds. So now I'm searching forums for IE and large dataset issues.

I did try jcross' fix (http://www.datatables.net/forums/discussion/2326/my-large-table-ie-fix/p1) but saw no difference in IE bahaviour.

Thanks,
Amanda

allan

Try deferred rendering if you are Ajax loading data: http://datatables.net/release-datatables/examples/ajax/defer_render.html . That can give a massive speed boost in older IE versions.

Allan

varaprasadreddy

Hi please look for ASP.Net GridView

http://www.reddyinfosoft.blogspot.in/2012/12/jquery-datatable-plugin-in-aspnet-using_15.html

and For ASP.Net Repeater

http://www.reddyinfosoft.blogspot.in/2012/12/jquery-datatable-plugin-in-aspnet-using.html