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

Has anyone got this working with a SQL server datasource? I've tried adding the Page Number filter to an External Content Type, but as soon as I do the list goes dead and creating a new one also fails. I would like SQL to perform my paging because my table is quite large and slow if it really needs to return everything each time.

Answer 3

r u using sharepoint designer or Visual studio to create the external list? I get the same in SharePOint Deisgner, when ever i add page number filter the list disappear from the "Create new external list" list. I've managed to do it in visual studio thought. Used LINQ to for all the operations and the method described by Vitaly Baum above. Everything works perfect now....

sunny0183

Answer 4

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.