[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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

Posted on 2004-09-02
Medium Priority
Last Modified: 2008-03-17
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
Question by:Keyboard
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +1

Author Comment

ID: 11966194
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?
LVL 18

Expert Comment

ID: 11966294
Can't you just set the "AllowSorting" property of the DataGrid to true ??

Assisted Solution

mdamico earned 100 total points
ID: 11966385
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 11966411
A little bit better of an explanation than mine:


Author Comment

ID: 11966449
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.

Author Comment

ID: 11966545
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?


Accepted Solution

shovavnik earned 400 total points
ID: 11966702
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.

Author Comment

ID: 11967085
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?

Expert Comment

ID: 11967370
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.

Author Comment

ID: 11967529
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 :)

Expert Comment

ID: 11967620
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.)

Expert Comment

ID: 11967644
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;

Author Comment

ID: 11967659
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?

Author Comment

ID: 11967856
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 :-((((

Expert Comment

ID: 11973183
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.

Author Comment

ID: 12007496
Thanks for all the great tips on sorting!!!

Expert Comment

ID: 12007556
You're welcome.  Have fun.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question