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?

3 Answers 3


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:

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}"/>
            <RowLimit Paged="TRUE">30</RowLimit>
            <Aggregations Value="Off"/>

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

  • I've implemented the solution from your answer but I get errors on the {dvt_....} values. Are these supposed to be replaced with the request values?
    – JohnDoDo
    Apr 3, 2012 at 11:08

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.


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. enter image description here Make sure the filter type is Page Number enter image description here
  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) {}

  1. 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.

  1. 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

  • Could you cover what you did at least at a high level? The community generally prefers answers that contain at least a discussion of the steps taken so that if the blog post is deleted or the link becomes inactive for some reason people who need a solution are able to get an idea of the steps taken from the answer alone. Aug 30, 2013 at 17:07
  • And good post, BTW! I've bookmarked it for later. Have my upvote! Aug 30, 2013 at 17:09
  • 1
    Cheers and thanks for the comment. I did go ahead and update my answer in here to provide more steps, but for some reason it hasn't shown up yet. Maybe it is pending approval. But I agree with you a 100%
    – Charles
    Sep 3, 2013 at 18:43
  • 1
    I've updated my answer, hope that helps
    – Charles
    Sep 18, 2013 at 16:26
  • Hey. I'm trying to follow the steps but my "Filter Field" options only let me select members of the return type. Can you give me a bit of advice?
    – Vinnyq12
    May 28, 2014 at 12:27

Here is the server side paging sample from Lightning Tools:


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.

  • Yeah, but I found it several months early and shared this solution with them :) Mar 19, 2011 at 20:00
  • Shame They didn't give you any credit in their posting. It sure looks alike now that I read it again. The only thing I would say and I posted that to their site is that I wouldn't use comparison filters as They pop up in the entity pickers afterwards and there is no easy way to hide them. Limit filters might server you better...
    – Tom
    Mar 21, 2011 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.