DB-side paging & sorting datasource?DB-side paging & sorting datasource?
Old forum URL: forums.lhotka.net/forums/t/309.aspx
Ridge posted on Wednesday, June 07, 2006
Has anyone implemented a DB-side paging & sorting data source and read-only list base yet? I'm needing to provide a bindable data source for my web developers to slap onto a gridview. On the DB side I'll likely use the handy new ROW_NUMBER function through procs in MS SQL 2k5... If not can anyone think of any sticky issues?
RockfordLhotka replied on Wednesday, June 07, 2006
I don't think you'd need a special object for this purpose would you? You'd just have a read-only root collection with a factory method like:
Public Shared Function GetList(startRow As Integer, pageSize As Integer) As MyCoolList
Or whatever values are needed for your DB algorithm to identify the start row and number of rows to retrieve. Given the stateless nature of the web you'd almost certainly have to keep at least the startRow (or lastRow or whatever) in the page itself so it is available on each postback.
The page size value should be available through the data source or grid controls.
Ridge replied on Wednesday, June 07, 2006
Thanks, I guess I was over-complicating it, I figured it'd be a mess. I don't generally diddle on the web server... I'll give it a whirl tomorrow.
Ridge replied on Thursday, June 08, 2006
So I spent a while this morning trying this out. Implementing the paged calls out to the DB is not the issue. The problem I'm running into is that data source view's ExecuteSelect is calculating the total row count as the number of entries in the list. Assuming you're populating a paged list, the list size is a subset of the total row count, as such the gridview isn't able to calculate the number of pages.
Brent replied on Thursday, June 08, 2006
Take a look at the SelectCountMethod on the datasource to specify a count method to return the total row count that you are paging. Ridge replied on Thursday, June 08, 2006
That's my point, ObjectDataSource provides a SelectCountMethod, the CslaDataSource does not.
y0mbo replied on Thursday, June 08, 2006
We created an abstract class called PagedCollectionBase which has CurrentPage, RecordsPerPage and TotalRecords.
The TotalRecords is retrieved in the DataPortal_Fetch as a second result set. CurrentPage and RecordsPerPage are specified in the Factory method call to get the collection.
RockfordLhotka replied on Friday, June 09, 2006
I see. Based on this, and your other email, I wonder if a
general solution isn't to add an IReportTotalRows interface to CSLA. The
CslaDataSource could then give preference to that interface to return the total
rows, falling back to the current behavior. In that way, any object that is a
data source could opt to "override" the way the total row count is provided to
CslaDataSource?
That still doesn't address the need to have the CanPage
property return true though. Ideally it would only return true if the object
actually does do paging. So perhaps your IPage interface is a better idea - and
could incorporate (or inherit from) IReportTotalRows.
Rocky
So I spent a while this morning trying this out. Implementing
the paged calls out to the DB is not the issue. The problem I'm running
into is that data source view's ExecuteSelect is calculating the total row
count as the number of entries in the list. Assuming you're populating a
paged list, the list size is a subset of the total row count, as such the
gridview isn't able to calculate the number of pages.
Ridge replied on Friday, June 09, 2006
To explain for folks following along, but who weren't privy to my email here's what I've done so far in my modified CSLA codebase:
public interface IPage { int TotalRowCount { get; } } }
Currently, the interface has only TotalRowCount, which would be better served as your IReportTotalRows, but after some usage I think it might be valuable to add PageIndex and PageSize to the interface and leave it as IPage, or implement those two in IPage and the TotalRowCount in IReportTotalRows... This note would affect the following:
[Serializable]
public abstract class ReadOnlyPagingListBase<T, C> : ReadOnlyListBase<T, C>, IPage where T : ReadOnlyPagingListBase<T, C> {
private int totalRowCount;
public int TotalRowCount {
get { return this.totalRowCount; }
protected set { this.totalRowCount = value; }
}
}
}
The changes to CslaDataSourceView are:
public override bool CanPage { get { return true; } } // Rather than return false, see note below
protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments) {
SelectObjectEventArgs args = new SelectObjectEventArgs();
this.dataSource.OnSelectObject(args);
object result = args.BusinessObject;
if(arguments.RetrieveTotalRowCount) {
int rowCount;
if(result == null) {
rowCount = 0;
}
else if(result is IPage) { // **** The important bit
rowCount = ((IPage) result).TotalRowCount;
}
else if(result is IList) {
rowCount = ((IList) result).Count;
}
else if(result is IEnumerable) {
IEnumerable temp = (IEnumerable) result;
int count = 0;
foreach(object item in temp) {
count++;
}
rowCount = count;
}
else {
rowCount = 1;
}
arguments.TotalRowCount = rowCount;
}
if(!(result is IEnumerable)) {
ArrayList list = new ArrayList();
list.Add(result);
result = list;
}
return (IEnumerable) result;
}
This seems to all work ok, but again, there may be value with the PageSize and PageIndex as mentioned before.
What I think should be done is to set up an allowpaging attribute for the data source asp tag to control CanPage on the datasource, similar to how ObjectDataSourceView works rather than hardcoding it one way or the other. I suspect CanSort should work in a similar fashion. In fact, when using ROW_NUMBER sorting and paging are somewhat tied together... :/
Currently, I'm setting up a criteria for a ReadOnlyPagingList that takes pageIndex, pageSize, and sortExpression, though this seems somewhat klunky to do per paged object...
Ridge replied on Friday, June 09, 2006
To explain for folks following along, but who weren't privy to my email here's what I've done so far in my modified CSLA codebase:
public interface IPage { int TotalRowCount { get; } } }
Currently, the interface has only TotalRowCount, which would be better served as your IReportTotalRows, but after some usage I think it might be valuable to add PageIndex and PageSize to the interface and leave it as IPage, or implement those two in IPage and the TotalRowCount in IReportTotalRows... This note would affect the following:
[Serializable]
public abstract class ReadOnlyPagingListBase<T, C> : ReadOnlyListBase<T, C>, IPage where T : ReadOnlyPagingListBase<T, C> {
private int totalRowCount;
public int TotalRowCount {
get { return this.totalRowCount; }
protected set { this.totalRowCount = value; }
}
}
}
The changes to CslaDataSourceView are:
public override bool CanPage { get { return true; } } // Rather than return false, see note below
protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments) {
SelectObjectEventArgs args = new SelectObjectEventArgs();
this.dataSource.OnSelectObject(args);
object result = args.BusinessObject;
if(arguments.RetrieveTotalRowCount) {
int rowCount;
if(result == null) {
rowCount = 0;
}
else if(result is IPage) { // **** The important bit
rowCount = ((IPage) result).TotalRowCount;
}
else if(result is IList) {
rowCount = ((IList) result).Count;
}
else if(result is IEnumerable) {
IEnumerable temp = (IEnumerable) result;
int count = 0;
foreach(object item in temp) {
count++;
}
rowCount = count;
}
else {
rowCount = 1;
}
arguments.TotalRowCount = rowCount;
}
if(!(result is IEnumerable)) {
ArrayList list = new ArrayList();
list.Add(result);
result = list;
}
return (IEnumerable) result;
}
This seems to all work ok, but again, there may be value with the PageSize and PageIndex as mentioned before.
What I think should be done is to set up an allowpaging attribute for the data source asp tag to control CanPage on the datasource, similar to how ObjectDataSourceView works rather than hardcoding it one way or the other. I suspect CanSort should work in a similar fashion. In fact, when using ROW_NUMBER sorting and paging are somewhat tied together... :/
Currently, I'm setting up a criteria for a ReadOnlyPagingList that takes pageIndex, pageSize, and sortExpression, though this seems somewhat klunky to do per paged object...
Copyright (c) Marimer LLC
