p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 (http://p2p.wrox.com/forumdisplay.php?f=264)
-   -   Sorting Difficulties when implementing Custom Obj (http://p2p.wrox.com/showthread.php?t=69618)

sandyl July 24th, 2008 10:09 PM

Sorting Difficulties when implementing Custom Obj
 
Hi, I've read this book and felt excited about the clever pattern being used. So, I started to implemented Custom Object (ObjectDataSource) for better performance(and of course OOP style) rather than Dataset/DataTable. However, I have difficulties in making the sort function work in my GridView with this approach. I've created Compare() method which implemented IComparable interface to achieve this. I could make it works if I don't use Paging. But I do want to use Paging.

I've tried to look for a solution from the internet. But, still cannot find one satisfied.

Any help on this will be much appreciated.

Sandy

Lee Dumond July 26th, 2008 01:25 AM

Server-side paging can be tricky when using T-SQL, because ObjectDataSource does paging differently that SQL Server. You must remember to take this into account.

Examine the GetPageIndex method in BizObject and you'll see what I mean. ObjectDataSource works with row indexes, while SQL works with page indexes. You can calculate the starting page index from the starting row index using this GetPageIndex method.


sandyl July 26th, 2008 11:06 PM

Sorry, I might not make it clear. My difficulty in here is only when I want to have sorting and paging functions work together. I could have my sorting function worked in Gridview with ObjectDataSource if I don't implement paging function. What I mean is, as soon as I turn on my sorting function in paging, I could only sort records in a certain page what I am at...not the whole records.

What is the best way to achieve this?

Old Pedant July 26th, 2008 11:48 PM

Have you considered doing *both* the sorting and the paging all in SQL Server? Using T-SQL??

It's by far the most efficient way, bar none. Means a little more work on your part, but the end result could well be worthwhile.

No, I've never done this via ObjectDataSource, but I can't think it would be all that hard (you are allowed to make me eat my words, but give it a shot).

4Guys has a good set of articles on this, including how to get the best performance even when paging very large result sets. Take a peek:
http://aspnet.4guysfromrolla.com/webtech/042606-1.shtml

And then Scott Mitchell talks about using this with an Object Data Source in a series of articles. Here are a few links, not sure which are most relevant:
http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
http://aspnet.4guysfromrolla.com/articles/032206-1.aspx
http://aspnet.4guysfromrolla.com/articles/040407-1.aspx
(Articles are dated; 031506 means March 15, 2006, etc.)

Imar July 27th, 2008 11:00 AM

Hi there,

Take a look here:

http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=416

then here:

http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=428

At the bottom of the article you find some hints on Paging in the Comments section.

The problem is of course, that if you page in the database, you only get back the relevant records for the requested page. If you then try to sort, you only sort the current page.

So, either do sorting and paging in the database (fast and efficient, slight difficult to write, especially with dynamic sorting) or do everything in .NET (e.g. get all records, sort, then get the requested page from the results). Easier to write, but most likely will perform not as well as the database version as you need to retrieve all records first.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Old Pedant July 27th, 2008 04:31 PM

I think I have to disagree, slightly, with Imar.

I just don't even begin to understand the point Imar was trying to make here:
    The problem is of course, that if you page in the database,
    you only get back the relevant records for the requested page.
    If you then try to sort, you only sort the current page.


Why???? I certainly would never do that. Why would you want to do that??? (Or is Imar specifically talking about the techniques used in the links he posted? That is, that's a limitation of the code he shows?)

If you want to implement completely dynamic sorting, then every time the sort is changed, you *need* to go back and re-sort the *entire* set of records.

Now, if that set of records is small enough, then the right thing to do is sort them in the browser, without ever going back to the server. Yes, even if the set of records is paged.

To give an example: 100 records, shown via 10 records per page. Absolutely no reason not to do all paging *AND* all sorting completely in the browser, completely in JavaScript. Takes the load off the server. And it's really not hard to do. I thought I had a demo of that, but all I have is a demo of sorting, no paging. Still, take a peek here:
http://www.clearviewdesign.com/clear...bie/demos.html
and especially here:
http://www.clearviewdesign.com/Newbi...TableDemo.html
(You can tell by the dates used that this is a pretty old demo. It's ASP, not ASP.NET, but would be easy to convert to ASP.NET. You could either do it brute force, starting from a DataReader, or by creating a custom component.)

If you do *NOT* opt to go the all-browser route, then I don't understand why you would *NOT* choose to go the all Stored Procedure route. After all, when the user calls for a change of Sort, you really have no choice but to re-order *ALL* the records and then start over again with Page 1 of the re-ordered set. (With only a little more difficulty, you *COULD* allow the user to designate a "current record" and then re-order and then find the page contaiing the "current record" within the new pages. But I don't even know if I've ever seen a site that bothered to do that, and I am doubtful that it's worth the effort.)

Anyway, given that you will go back to Page 1 of the re-ordered set, who cares what the prior set of records was? Why retain them, at all?

I think you will find that the really "big guys" in this business (e.g., the Amazon.com's and EBay's of the world) do it this way. They don't even *attempt* to keep any sort of per-user "state" of recordsets (data tables, whatever). Instead, they just let the DB find the next set of appropriate data, however it is requested.

Lee Dumond July 27th, 2008 11:58 PM

I understood exactly what Imar was saying. In fact, Imar and Pedant are both saying the same exact thing -- you should both sort and page on the database server. Forget about doing both sorting and paging at the same time in the ObjectDataSource.

Write SQL that implements a dynamic query for sorting. Then write a SPROC that grabs a page of records, based on the ObjectDataSource's startRowIndex and maximumRows (converting startRowIndex to page index, as I pointed out before.)

Whenever the sort changes, rerun the dynamic query, then grab a new page of records STARTING AT THE FIRST RECORD, then rebind the Gridview.

Say, for example, the client Gridview is showing Customer names (Name) and the amount of stuff they've purchased (Purchases). You have the GridView sorted by Name. The client pages, pages, pages through the records. Then, the client clicks on the row heading to sort by Purchases. The GridView rebinds and starts showing the FIRST page of records of this sort. The lowest purchase is right on top, without regard to where the client was when the GridView was re-sorted.

Make sense?


Imar July 28th, 2008 12:52 AM

Quote:

quote:Why???? I certainly would never do that. Why would you want to do that??? (Or is Imar specifically talking about the techniques used in the links he posted? That is, that's a limitation of the code he shows?)
It's not a limitation of my code, but of the concept. Consider this data:

A 1
A 2
A 3
B 1
B 2
B 3

Let's say the default sort order for the stored procedure is on the first column (if you don't sort at all it'll be in an unpredictable order which would work fine for this example as well). Now, let's say my page size is 3 and I want the first page. What I get is this:

A 1
A 2
A 3

If I then sort descending on the second column (and stay on the first page in the GridView) at the client or in ASP.NET, what I then get is this:

A 3
A 2
A 1

However, I want to sort the entire set. So what I really need is this:

A 3
B 3
A 2

So, in order to get the right page with sorted records, you have to do all in one place: first sort, then grab the requested page from the result set.
Quote:

quote:Now, if that set of records is small enough, then the right thing to do is sort them in the browser, without ever going back to the server. Yes, even if the set of records is paged.
Not when they are paged; or at least not when they are server side / database paged. You need the entire result set in order to determine how to sort.
Quote:

quote:To give an example: 100 records, shown via 10 records per page. Absolutely no reason not to do all paging *AND* all sorting completely in the browser, completely in JavaScript.
One very good reason to do so: you get incorrect results otherwise. See above.
Quote:

quote:Takes the load off the server. And it's really not hard to do. I thought I had a demo of that, but all I have is a demo of sorting, no paging. Still, take a peek here:
They probably do client side sorting on the full set only (which is fine is that what you need) for the above mentioned reasons.... ;)

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Old Pedant July 28th, 2008 01:57 AM

I think Lee Dummond has it right: We are both saying the same thing.

I was, indeed, saying that if you change the sort order you *MUST* start the paging all over. Meaning you can't use the standard datagrid paging. But I thought the whole point of this was to forget all about standard datagrid paging and "roll your own."

Anyway, yes: If the user changes the sort, then kill the entire paging and start it all over. However you have to force that to happen.

*******************

And Imar; You missed my point about doing both paging and sorting *ALL* in the browser. I was, indeed, saying that *IF* the ENTIRE DATATABLE (or whatever it is you are paging/sorting) is small enough to send to the browser, *THEN* do *ALL* your sorting and paging in the browser. Forget standard junky ASP.NET server-side work, completely. Basically, you create "data rows" in the JS code (object that represent the data rows, of course) in an array, where the array is sorted according to the user's choice. You only show one page's worth of the data rows, and when the user asks for another page, you go get it from the JS array, never even dreaming about going back to the server.

I have successfully done this in JSP code and have passed data-table-equivalents to JS coding that were as large as a few hundred rows. Yes, the first time you hit the page the page load is a little slower, but then after that there are *NO* round trips to the server.

Patently only works when the *TOTAL* amount of data is small enough, but "small enough" can be amazingly large. And well worth the trouble if you have a loaded server.

Old Pedant July 28th, 2008 02:12 AM

Lee Dumond wrote:
Write SQL that implements a dynamic query for sorting.

Excepting that in most situations you don't REALLY need a dynamic query for sorting.

You can usually get away with a parameterized ORDER BY.

Example:
Code:

CREATE PROC foo
    @pagenumber int,
    @pagesize int,
    @orderby varchar(20)
AS
-- I'm going to ignore the pagenumber & pagesize for this demo
-- In "real life" I'd use them as the 4GuysFromRolla article shows

SELECT name, age, salary FROM table
ORDER BY ( CASE WHEN @ordering = 'name'  THEN name  ELSE '' END ),
         ( CASE WHEN @ordering = 'age'    THEN age    ELSE 0  END ),
         ( CASE WHEN @ordering = 'salary' THEN salary ELSE 0.0 END )


You just have to be careful that the ELSE for each of the possible ordering fields matches the datatype of that field.

So now, if (say) the @ordering is 'age', then that query becomes the equivalent of
Code:

SELECT name, age, salary FROM table
ORDER BY '', age, 0.0

And so only the age column enters into the ordering.

And if you are *TRULY* concerned about performance, you'd just use IF ... ELSE IF .... ELSE IF ... in the T-SQL and clone the query code except for the ORDER BY. Tedious SP writing, but mostly simple cut & paste, so why not? Probably cleaner than using dynamic sql in the SP.


All times are GMT -4. The time now is 06:28 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.