Paging and sorting with BCS

Question

I going to use the new exciting External Content Type feature to display a list of data from an external LOB system. The list is potentially long, so paging with filtering at the LOB system side is preferable. http://msdn.microsoft.com/en-us/library/ee556392(office.14).aspx implies that BCS might have some provisions for paging (PageNumber filter). But the documentation is yet incomplete. Here is a sample how to do paging for IdEnumerator method for Search Crawler, but has no samples to paging with SPView. Anyone has some experience with BCS paging?

---

• How does BCS get to know the total number of items in the list?
• How does BCS pass paging parameters to the LOB system?
• How to make work SPView with BCS server-side paging and sorting?

Answer 1

Thanks, guys, I'm already create paging and sorting with BCS. In my case BCS works fine with WCF services. At first I'm develop web service with follow contract:

[OperationContract]
IEnumerable<Employee> GetEmployeesPaged(int startRowNumber, int pageCount, string sortColumn, string sortDir);

then I'm created BDC definition with Read List Method for this WCF-method with Filters for all this parameters. I'm created Comparison filters, but FilterType has no sence in this case. After that I'm created External List with View for this Method. Opened this View in SharePoint Designer and change some parameters for View into XsltListViewWebPart:

        <View Name="{50E1E936-0A3F-4096-84D2-FBDC194B4BAE}" DefaultView="TRUE" MobileView="TRUE" Type="HTML" DisplayName="Employee Read List Paged" Url="/Lists/Contracts/GetEmployeesPaged.aspx" Level="1" BaseViewID="1" ContentTypeID="0x" ImageUrl="/_layouts/images/generic.png">
            <Method Name="GetEmployeesPaged">
                <Filter Name="FilterRowNum" Value="{dvt_firstrow}"/>
                <Filter Name="FilterPageCount" Value="30"/>
                <Filter Name="FilterSortColumn" Value="{dvt_sortfield}"/>
                <Filter Name="FilterSortDir" Value="{dvt_sortdir}"/>
            </Method>
            <RowLimit Paged="TRUE">30</RowLimit>
            <Aggregations Value="Off"/>
        </View>

After all my WCF service works fine with this parameters and that make possible server-side paging and sorting.

The one specific of this solution - is that the count of items, retrieved from the BCS must be encrease on every page on the number of [page size]+1. "+1" made SharePoint to add "next button". For example: you need to retrieve 121 items for BCS if you want to get 4th page and the page size is 30 items

Answer 2

Forget about any third party tools. I was able to handle this very problem by modifying the sql call in the WCF service and using filtering that's built in to the BCS via SharePoint Designer. I've blogged about it here.

http://www.spcrew.com/blogs/Lists/Posts/Post.aspx?ID=47

Here are the basic steps:

1. Create a Method in WCF Service to call the database using some parameter to return row limits.
2. Create a List Read Function for the WCF using SharePoint Designer. Make sure the filter type is Page Number
3. Set a Filter for that method in SharePoint Designer. Make sure that you pass the PageNumber filter as a parameter to that method

public IEnumerable getAllItemsPaged(int PageNum) {}

4. In your WCF method, accept that filter parameter and perform some calculation to determine which set of data to query. Your sql query will be something like:

string SqlQueryStr = @"SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= " + ((1000 * PageNum) + 1) +" AND RowNum < "+ upperlimit+" ORDER BY RowNum";

so basically you're going to crawling items 1000 at a time.

4. Crawl. When you crawl this, SharePoint will call this method and pass the PageNum to this method as 0 then will call it again and pass "1", and then "2", ... so on and so forth until it crawls everything.

Hope that helps

Answer 3

Here is the server side paging sample from Lightning Tools:

http://lightningtools.com/blog/archive/2010/06/25/sharepoint-2010-external-list-paging-ndash-server-side.aspx

It is .NET Connectivity sample, so you can make it work with any back end including SQL Server. It is the best we could find, but it isn't perfect since it is doing TOP N as opposed to a page a time.

Keep in mind that it will have an effect on filtering as the same 'ReadList' method is being called when the filter is populated, so the filter data is paged too. You can workaround this with inspecting form variables, but it is a lenghty and hacky process.