Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 24th, 2008, 10:09 PM
Registered User
 
Join Date: Jul 2008
Location: Annandale, va, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sandyl
Default 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
  #2 (permalink)  
Old July 26th, 2008, 01:25 AM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

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.

  #3 (permalink)  
Old July 26th, 2008, 11:06 PM
Registered User
 
Join Date: Jul 2008
Location: Annandale, va, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sandyl
Default

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?
  #4 (permalink)  
Old July 26th, 2008, 11:48 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.)
  #5 (permalink)  
Old July 27th, 2008, 11:00 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
  #6 (permalink)  
Old July 27th, 2008, 04:31 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
  #7 (permalink)  
Old July 27th, 2008, 11:58 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

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?

  #8 (permalink)  
Old July 28th, 2008, 12:52 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
  #9 (permalink)  
Old July 28th, 2008, 01:57 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
  #10 (permalink)  
Old July 28th, 2008, 02:12 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Sorting for Comments tectrix BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 February 14th, 2008 07:07 AM
custom paging and sorting? asptwodev BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 2 September 22nd, 2007 02:06 AM
Implementing Custom Assemblys dillig BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 September 8th, 2004 07:56 AM



All times are GMT -4. The time now is 05:00 PM.


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