locked
How to use paging in BCS ? RRS feed

  • Question

  • Hi All;

    How to apploy server side paging in BCS in sharepoint foundation using VS2010.

    Thank's in Advance

    Wednesday, August 11, 2010 1:09 PM

Answers

  • You need to use a PageNumber Filter. The PageNuber Filter will allow you to send in the page number you are looking for. The signature in your service class will look something like this:

            public IList<Document> ReadFolder(int parentFolderID, int pageNumber)
            {
     
            }

    See the pageNumber? Use that to page against the External System. Don't expect the External List to use this, however. You will need to create a custom web part that uses the BDC Server Runtime API to call this Finder method and display the results. The External List OOB won't do it.

    Here's the model that works with the above function signature.

    Scot

                <Method Name="ReadFolder" DefaultDisplayName="Read Folder">
                  <FilterDescriptors>
                    <FilterDescriptor Name="FolderFilter" Type="Comparison" DefaultDisplayName="Folder ID" FilterField="ParentID" />
                    <FilterDescriptor Name="PageFilter" Type="PageNumber" FilterField="ID" DefaultDisplayName="Page Number" />
                  </FilterDescriptors>
                  <Parameters>
                    <Parameter Name="documentList" Direction="Return">
                      <TypeDescriptor Name="DocumentList" TypeName="System.Collections.Generic.IList`1[[ExternalDMSConnector.Document, ExternalDMSLobSystem]]" IsCollection="true">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Document" TypeName="ExternalDMSConnector.Document, ExternalDMSLobSystem">
                            <TypeDescriptors>
                              <TypeDescriptor Name="ID" IdentifierName="ID" TypeName="System.Int32" />
                              <TypeDescriptor Name="Name" TypeName="System.String" IsCollection="false" />
                              <TypeDescriptor Name="Type" TypeName="System.String" />
                              <TypeDescriptor Name="ParentID" TypeName="System.Int32" />
                              <TypeDescriptor Name="Version" TypeName="System.String" />
                              <TypeDescriptor Name="Url" TypeName="System.String" />
                              <TypeDescriptor Name="Extension" TypeName="System.String" />
                              <TypeDescriptor Name="Author" TypeName="System.String" />
                              <TypeDescriptor Name="Created" TypeName="System.DateTime">
                                <Interpretation>
                                  <NormalizeDateTime LobDateTimeMode="Local" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor Name="Modified" TypeName="System.DateTime" IsCollection="false">
                                <Interpretation>
                                  <NormalizeDateTime LobDateTimeMode="Local" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor Name="Description" TypeName="System.String" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                    <Parameter Name="parentFolderID" Direction="In">
                      <TypeDescriptor Name="ParentFolderId" TypeName="System.Int32" AssociatedFilter="FolderFilter" IsCollection="false" />
                    </Parameter>
                    <Parameter Name="pageNumber" Direction="In">
                      <TypeDescriptor Name="PageNumber" TypeName="System.Int32" AssociatedFilter="PageFilter" />
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="ReadFolder" Type="Finder" ReturnParameterName="documentList" ReturnTypeDescriptorPath="DocumentList" DefaultDisplayName="ReadFolder" />
                  </MethodInstances>
                </Method>

     

    Friday, August 27, 2010 10:46 AM

All replies

  • Hi,

     

           Did you create .net type BSC extension in your SharePoint site?

           You could set paging functionality in list View setting.

           In List-->List Setting-->Views-->Choose a View--> Item Limit, set the Number of items to display property.

           Hope this can help.

          

    Best Regards,

    Aaron

    Tuesday, August 17, 2010 10:13 AM
  • Hi Aaron,

    Thanks for your reply.

    I have SharePoint Foundation 2010 and I create BCS project using VS2010. This BCS show my external data which is stored in SQL Server 2008 Database. For accessing data from sql server  I have one Custom DLL. I am using this DLL to access data in SharePoint BCS Project. In my database has 100000 records. When we show data in external list BCS access all records and show only those records which is in my view page limit. For Example.

    I am Creating Vendor BCS in VS2010. In Vendor BCS has two methods ReadList & ReadItem. In ReadList Method I Get all the data from database and return it but I want to access only selected page data from my database in ReadList.

    So how to get selected page, page size in ReadList Method.

     

    Please Help me.

    Thanks in Advance.

     

    Wednesday, August 18, 2010 12:12 PM
  • You can edit your model file (open in XML editor) to include filter descriptors. Something like this:

     

    <FilterDescriptors>
     <FilterDescriptor Type="Limit" FilterField="ProductPhotoID" Name="Filter">
      <Properties>
       <Property Name="UsedForDisambiguation" Type="System.Boolean">false</Property>
       <Property Name="IsDefault" Type="System.Boolean">true</Property>
       <Property Name="CaseSensitive" Type="System.Boolean">false</Property>
      </Properties>
     </FilterDescriptor>
    </FilterDescriptors>
    <Parameters>
     <Parameter Direction="In" Name="@ProductPhotoID">
      <TypeDescriptor TypeName="System.Int64" AssociatedFilter="Filter" Name="ProductPhotoID">
       <DefaultValues>
        <DefaultValue MethodInstanceName="PhotoReadList" Type="System.Int64">10</DefaultValue>
       </DefaultValues>
      </TypeDescriptor>
     </Parameter>
    


    Regards,
    Chakkaradeep || SharePoint Solutions Specialist - MCTS SharePoint Dev, WSS Dev, MCPD SharePoint Developer 2010 || http://www.intergen.co.nz || Twitter: http://twitter.com/chakkaradeep || http://www.chakkaradeep.com
    Wednesday, August 18, 2010 12:15 PM
  • Hi Aaron,

    Thanks for your reply.

    Soory for late reply. How can we enable paging in the BCS for a custom .NET assembly? 

     

    Thank's In Adavance.

    Thursday, August 26, 2010 11:15 AM
  • You need to use a PageNumber Filter. The PageNuber Filter will allow you to send in the page number you are looking for. The signature in your service class will look something like this:

            public IList<Document> ReadFolder(int parentFolderID, int pageNumber)
            {
     
            }

    See the pageNumber? Use that to page against the External System. Don't expect the External List to use this, however. You will need to create a custom web part that uses the BDC Server Runtime API to call this Finder method and display the results. The External List OOB won't do it.

    Here's the model that works with the above function signature.

    Scot

                <Method Name="ReadFolder" DefaultDisplayName="Read Folder">
                  <FilterDescriptors>
                    <FilterDescriptor Name="FolderFilter" Type="Comparison" DefaultDisplayName="Folder ID" FilterField="ParentID" />
                    <FilterDescriptor Name="PageFilter" Type="PageNumber" FilterField="ID" DefaultDisplayName="Page Number" />
                  </FilterDescriptors>
                  <Parameters>
                    <Parameter Name="documentList" Direction="Return">
                      <TypeDescriptor Name="DocumentList" TypeName="System.Collections.Generic.IList`1[[ExternalDMSConnector.Document, ExternalDMSLobSystem]]" IsCollection="true">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Document" TypeName="ExternalDMSConnector.Document, ExternalDMSLobSystem">
                            <TypeDescriptors>
                              <TypeDescriptor Name="ID" IdentifierName="ID" TypeName="System.Int32" />
                              <TypeDescriptor Name="Name" TypeName="System.String" IsCollection="false" />
                              <TypeDescriptor Name="Type" TypeName="System.String" />
                              <TypeDescriptor Name="ParentID" TypeName="System.Int32" />
                              <TypeDescriptor Name="Version" TypeName="System.String" />
                              <TypeDescriptor Name="Url" TypeName="System.String" />
                              <TypeDescriptor Name="Extension" TypeName="System.String" />
                              <TypeDescriptor Name="Author" TypeName="System.String" />
                              <TypeDescriptor Name="Created" TypeName="System.DateTime">
                                <Interpretation>
                                  <NormalizeDateTime LobDateTimeMode="Local" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor Name="Modified" TypeName="System.DateTime" IsCollection="false">
                                <Interpretation>
                                  <NormalizeDateTime LobDateTimeMode="Local" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor Name="Description" TypeName="System.String" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                    <Parameter Name="parentFolderID" Direction="In">
                      <TypeDescriptor Name="ParentFolderId" TypeName="System.Int32" AssociatedFilter="FolderFilter" IsCollection="false" />
                    </Parameter>
                    <Parameter Name="pageNumber" Direction="In">
                      <TypeDescriptor Name="PageNumber" TypeName="System.Int32" AssociatedFilter="PageFilter" />
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="ReadFolder" Type="Finder" ReturnParameterName="documentList" ReturnTypeDescriptorPath="DocumentList" DefaultDisplayName="ReadFolder" />
                  </MethodInstances>
                </Method>

     

    Friday, August 27, 2010 10:46 AM
  • Hi Scot,

    In the above approach do you suggest creating a custom webpart which will contain:

    1. A UI to display the resultant rows (say if we set pagination equal to 100, this UI should display 100 resultant rows after pagination)
    2. A Button / image click to capture the page number and send it to the ECT

    Sorry but can you please elaborate as I am not able to visualize it for my scenario.

    What I need is:

    1. I have an SQL table having 300,000 rows
    2. I need to display these rows- 100 at a time, such that ECT should not fetch all 300,000 rows on every button click (which takes a lot of time), instead, get only 100 rows at a time (In short I want to replicate what an ASP.Net GridView control does when pagination is set on).

    I have created a Stored Procedure in SQL that takes the page number as input parameter and results in the desired set of results. I have created my External Content Type using SharePoint Designer 2010 and attached this Stored Procedure as the data source. How do I need to proceed?

    Thanks much!

    Friday, November 26, 2010 12:58 PM
  • Yes. I think you are going to want to create a custom web part that calls the BDC Server Runtime API. Use the OM to call to the Finder method and pass the paging information.

    Something like this

    //Connect to BDC Service Application
    BdcService service = SPFarm.Local.Services.GetValue<BdcService>();

    if (service != null)
    {
    //Get Metadata elements
      DatabaseBackedMetadataCatalog catalog =
        service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
      IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
      ILobSystem lob = ect.GetLobSystem();
      ILobSystemInstance lobi =
        lob.GetLobSystemInstances()[LobSystemInstanceName];
    }

    IMethodInstance mi = ect.GetMethodInstance(FinderMethodInstanceName,
                                               MethodInstanceType.Finder);
    IFilterCollection filters = mi.GetFilters();

    (filters[0] as LimitFilter).Value = 10;
    (filters[1] as PageNumberFilter).Value = 2;
    (filters[3] as WildcardFilter).Value = "Bike";
    (filters[4] as ComparisonFilter).Value = "CN123720";


    IEntityInstanceEnumerator items =
      ect.FindFiltered(filters, FinderMethodInstanceName);

    while (items.MoveNext())
    {
      Console.WriteLine(items.Current[FieldName].ToString());
    }

    items.Close();

    Scot

    Friday, November 26, 2010 10:18 PM
  • Correct me if i'm wrong, but doesn't the external content type already do this by default? The SQL Data is not stored in the external content type list, it merely calls the requested data from the database and displays it.
    Thursday, February 3, 2011 5:24 PM
  • There is a difference between the paging implemented by the External List and custom paging for the ECT.

    Scot

    Thursday, February 3, 2011 8:59 PM
  • Hi Could you please tell me in order to put a page filter, do we require a type descriptor that should have a datatype as "Int64/Int32"?
    Tuesday, January 10, 2012 11:17 AM