What is the best way to sort a datagrid (asp.net) that uses stored procedures?

I have a page that displays data in a datagrid. In the page_load event, after looking into Request.Params(), I determine an appropriate stored procedure to use. I invoke the storeproc, using MS application blocks, and bind the datagrid's datasource to a SqlDataReader.

I could think of the following options:

1) Use a dataview and bind the grid to the dataview. Sorting can be accomplished by sorting the dataview. <b>My question here is how do I create a dataview from SqlDataReader. I am not using DataSet.</b>

2) Based on the sort column, call the storeproc with the sort column as a parameter. The downside to this option is, I would have to determine again which stored procedure to invoke.

Which option do you like, and are there other easier options available?

I could
Who is Participating?
shovavnikConnect With a Mentor Commented:
First of all, you should *not* call the stored procedure just to resort it.  There are exceptions to this rule, but chances are your needs at this point don't require always completely live data.

Is there a reason you're not using a DataSet or a DataTable?

It's true that DataSets are slower than DataReaders, but that speed is exchanged for functionality which is invaluable, especially when you need to sort or page (or both) a datagrid.

I strongly advise you to use a DataSet or a DataTable.

Pseudo-code for Page_Load (assuming c#):

Page_Load() {
  if( IsPostBack ) {
    // try to load your datatable from wherever it happens to be cached.
    // if it's successully loaded it will have data, and if not, it will be null
  if (!IsPostBack || MyDataTable == null) {
    // create your datatable from scratch by loading data from the database
    // save it to the cache
  // bind to your datatable

Now, figuring out where to cache your datatable is the interesting part.  You have lots of options, not only the viewstate.
mdamico is aboslutely correct about the viewstate.
If you don't want to use the viewstate (because you have a large datatable, or the data is sensitive, or just because), you have at LEAST the following options in addition:
1. Session
  This is a good option if your datatable isn't too large, or if you only expect a few concurrent users.  It's the fastest of the bunch and the easiest to implement.
2. Serialization to a file
  You'll need to read up a bit on serialization, but this is an excellent option.  It's slower than using the session, but faster than using the database (usually).  It's also the most scalable and generic option.  The idea is that you save the actual data to a file somewhere on the server and then retrieve it from the file later.  You use some form of idenitifier to name the file so many users can have their own caches.  You'll need to maintain it a bit, by deleting the files every so often.
3. Serialization to the database
  This is more complex, and takes a lot of effort to do right.  It can be faster than serialization to a file, but only if done right.  Usually it's not.  It's usually faster than accessing the database for the initial data because you don't have to do any joins or special calculations.

Anyway, I recommend using serialization to a file.  Microsoft has a walkthrough with examples for using the BinarySerialization.

Once you've de-serialized the file, all you need to do to change the sort is modify the DataView:

MyDataTable.DefaultView.Sort = "NewSortColumn"

So it's a bit more complex to do the caching, but it's much simpler to do paging and sorting and a few other things.
KeyboardAuthor Commented:
Also, I don't like the concept of invoking the stored procedure everytime the user sorts a column. Is'nt it bad design to do so?
Can't you just set the "AllowSorting" property of the DataGrid to true ??
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

mdamicoConnect With a Mentor Commented:
Using viewstate with the datagrid significantly increases the page size.  If you are not working with a high speed connection(in both directions), keeping page sizes to a minimum may be more desirable than lowering the workload of your database server a small amount.

With very small datasets or when only supporting users with fast connections, the viewstate option is a good choice.  Otherwise, having your database server do a little extra work is the better option.
A little bit better of an explanation than mine:

KeyboardAuthor Commented:
What I read is that just setting the "AllowSorting" would not do. We have to handle the sort event that will be generated and then bind the data back to the grid.
KeyboardAuthor Commented:
I am not going to be working with highspeed connections. Why do you think ViewState has to be used? What would be a small, medium and large datagrid?

KeyboardAuthor Commented:
I am using DataReader instead of DataSet because of performance reasons. But if there is more functionality then I am ready to change to DataSet/DataTable. I can use dataviews in conjuction.

One thing I did not understand is about 'caching'. Why do I have to use caching here? Rephrasing the question, why do I have to cache the datatable? Are you saying on a postback the datatable will no longer have data in it? I am trying to avoid requering the database, so without caching my purpose would be defeated?
Caching basically means temporary storage.

You're correct.  On postback, the datatable will not have any data in it.  So you need to use temporary storage, that is, caching, to retrieve the data from somewhere other than the db.

Like I said, the DataReader does perform better than the DataSet.  However, it is also far more limited in functionality.  The speed difference is not usually noticeable in any case.

There are many different solutions for sorting and paging as these are very common needs.  In my opinion, the simplest is to use a DataTable and bind the DataGrid.DataSource property to the DataTable.DefaultView property.

Notice that the rest of us take this caching for granted.  madmico immediately thought of the ViewState.  The reason for this is simple.

When working with asp.net (or any internet technology for that matter) you always have to consider that you're working in what's called a "disconnected state".  That means that the client knows nothing about the server and the server knows nothing about the client, simple because they're not on the same machine.  All kinds of solutions have been incorporated into standard technologies to allow the server or the client to "fake it" as though the current connection is the same as the previous.  The Session object is one of those solutions.  So is the ViewState.  So are PostBacks.  They each have advantages and disadvantages.  But the point is, that technically, *nothing* exists, not only the DataTable, and it's ALL recreated from scratch.  You just need to figure out the best way to recreate your data source so you can efficiently bind it to the datagrid without hampering the rest of the application and the rest of your users.
KeyboardAuthor Commented:
Thanks for your explanation. I observed what you are explaining, when I enabled the grid for sorting, and in the SortEvent bound the datagrid to the dataset. Phoof, the grid disappeared :)

I have changed my code to use dataset instead of datareader. I am just moments away from sorting a grid :) I need to implement something fast and I expect my data to be not in millions. Maybe hundreds or say some thousands. How do I use Session to save the datatable then?

It was relatively easy in WinForms :)
Using the session is easiest.

To save something in the session:

Session[ "MYDATA" ] = ds;

and to retrieve it:

DataSet ds = null;
object sessionObject = Session[ "MYDATA" ];
if( sessionObject != null && sessionObject is DataSet ) ds = (DataSet) sessionobject; // type-safe cast

However, this should go with a caution.  Don't abuse the session, because it's stored in server memory between page calls.  In other words, until the user's session is over (by timeout or logout or whatever) or until you clear the session variable explicitly, your dataset will be taking up server memory.  With a few thousand records, this could have a detrimental effect on performance for everyone.  For a few hundred, the effect is probably negligible.

Try it out, see if it's a problem.  If it's not, great.  If it is, use file serialization.  (Just so you know, internally, the session object uses serialization too.  Serializing to a file is exactly like saving to the session object, except that your target is a file instead of server memory.)
One last thing though.  If this is a table that's used by all your users and is not user-specific, then you can conserve on server resources by usingthe Application object instead of the Session object.

The session object is created for each user.  The application object is created only once.

They are both used in the code in the same exact way.

Application[ "MYDATA" ] = MyDataSet;
KeyboardAuthor Commented:
Thanks again. Based on what I was reading, I agree session is a bad idea. How tough is it to create Client side sorting? ASP.Net would have to generate the client-side script, is it not? Is it too difficult?
KeyboardAuthor Commented:
Additionally, being a generic internet page (anonymous access), I would not have user credentials to track the file serilization too. Now it looks like calling the stored procedure would be easier - in terms of implementation and maintenance :-((((
Calling the stored procedure directly is easiest for coding purposes.  But it's not the best way to do it.

You definitely *want* to use caching.

Try to implement caching with the Session.  You'll find that it's very simple to do,once you get the hang of it, the proper logical flow.

Then see if it's problematic for your application or detrimental to performance.  This depends usually on the number of CONCURRENT users and the amount of RAM your server has.  Depending on the amount of data you put in the server, usually it only starts to affect performance after 50 or 100 CONCURRENT, SIMULATANEOUS users with asp.net (at least from my experience).

In any case, once you've put session caching under your belt, tackle file serialization.  It's really not as complicated as it sounds.
Also, even with anonymous access, you have some basic user info, like a session key.  You can also put some random value (a guid for example) into the user's viewstate, and if the user posts back try to retrieve the file named after the value in the viewstate.

To implement sorting on the client-side is rather complex, even though it's much more easier on the server.  You would *not* have to generate the client script using asp.net, but can use javascript scripts, and even use a javascript file (<script src="datasort.js"></script>, for example).  But that's another discussion altogether, as there are a myriad of ways to implement that as well.

I suggest you go for sorting on the server using either the session state or file serialization.
KeyboardAuthor Commented:
Thanks for all the great tips on sorting!!!
You're welcome.  Have fun.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.