Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?
share|improve this question
add comment

5 Answers 5

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

share|improve this answer
    
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. –  Robert Kaucher Aug 30 '13 at 17:07
    
And good post, BTW! I've bookmarked it for later. Have my upvote! –  Robert Kaucher Aug 30 '13 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 '13 at 18:43
1  
I've updated my answer, hope that helps –  Charles Sep 18 '13 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 at 12:27
add comment

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.

share|improve this answer
    
Yeah, but I found it several months early and shared this solution with them :) –  Vitaly Baum Mar 19 '11 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 '11 at 13:31
add comment

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

share|improve this answer
add comment
up vote 6 down vote accepted

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

share|improve this answer
    
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 '12 at 11:08
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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