Getting more rows in a custom load method

To include the Get More Rows functionality in a custom load method, you must create a bookmarking algorithm. The framework provides and receives relevant information for bookmarking, using these variables:

EnableBookmark: This is set to either True or False, depending on whether the load request wants to apply bunching. If the variable is set to False, the custom load method is expected to ignore bunching.
Bookmark: This is the bookmark value. It is blank for an initial load type, and contains values for bookmarks (which the algorithm must create) for subsequent load calls. A bookmark must uniquely identify a set of rows, referred to as a bunch.
LoadType: This is set to First, Last, Next, or Prev. First and Last are initial load types: When a collection initially loads, a bookmark is not needed and the framework expects either the first or the last bunch to be returned. Next and Prev are used on subsequent loads with a bookmark, and the framework expects the bunch either after or before the bunch identified by the bookmark. For the Last and Prev load types (essentially, browsing backward through the collection), the returned rows are expected to be sorted in reverse order.
RecordCap: This is the maximum number of rows the framework expects to be returned for a single bunch.
Note: The method is expected to return one extra row (RecordCap+1 rows, total), if it is available. The framework uses this information to determine whether there are more rows available to be queried for the next bunch. The bookmark is still expected to identify uniquely the set of rows that will be returned to the user. The extra row is not considered part of the set.

After the custom load method constructs the set of rows to return, it is expected that it will update the Bookmark variable with a unique value that represents the set of rows it is returning.

Custom load methods that are implemented as stored procedures receive these variables through the use of process variables (see Process variables). Before it calls into a custom load method, the framework creates these variables from the load request that generated the call. Upon return, the framework checks the process variable called Bookmark for the new bookmark. Custom load methods that are implemented as hand-coded methods have access to the load-request structure, which contains these variables as properties of the request.

Bookmarking algorithms

Although you must create the bookmarking algorithm for your custom load method, we provide some examples:

Example 1: If you have a collection with a unique, consecutive integer row number property, your bookmark could be a single number, and the custom load method sorts the collection by that property and returns the top RecordCap+1 rows for a load type of First, setting the bookmark to the number of the second-to-last row. Loads of type Next return RecordCap+1 rows, where the RowNumber value is greater than bookmark. The method performs similarly for load types Last and Prev, except that it sorts in reverse order and the RowNumber value is less than bookmark.
The algorithm must have special cases for an empty result set, and for a single row being returned.
Example 2: The algorithm the framework uses for standard loads is more complicated, since it must work with any possible collection, combination of properties being loaded, and so on. The bookmark is an XML fragment with this schema:
```

 ColumnName1ColumnName2…
 <D><f>DescendingFlag1(true or false)</f><f>Flag2</f>…</D>
 <F><v>FirstRowValue1</v><v>FirstRowValue2</v>…</F>
 <L><v>LastRowValue1</v><v>LastRowValue2</v>…

```
This XML fragment is combined with the <RecordCap> and <LoadType> elements in the request. The framework fills the bookmark with the data necessary to uniquely describe the set of rows that it just retrieved. It uses either the RowPointer column, or a set of columns that make up a unique key, together with a descending flag for each column, and the values of those columns in the first and last rows of the set.

This is a working example of a simple custom load method that supports forward-only batching. This is for an IDO that has these properties:

ID (int)
Last (string)
First (string)
Grade (int)

Note: This example is for forward-only batching, not bidirectional batching.

      // 
      // Sample custom load method supporting forward-only bookmark navigation
      // 
      private static readonly (int id, string last, string first, int grade)[] STUDENTS = new [] 
      {
         (112, "Smith",   "Bob",   9),
         (123, "Jones",   "Tim",   12),
         (391, "Adams",   "John",  8),
         (407, "Stark",   "Tony",  12),
         (512, "Tell",    "Bill",  8),
         (663, "Parker",  "Peter", 9),
         (798, "Brooks",  "Mel",   12),
         (812, "Allen",   "Tim",   9),
         (162, "Bird",    "Larry", 9),
         (332, "Baggins", "Frodo", 12)
      };

      [IDOMethod( MethodFlags.CustomLoad )]
      public DataTable LoadStudentsByGrade()
      {
         var dt = new DataTable();
         var request = this.Context.Request as LoadCollectionRequestData;
         var recordCap = IDORuntime.DefaultRecordCap;
         var rows = default( IEnumerable<(int id, string last, string first, int grade)> );
         var bookmarkId = -1;

         dt.Columns.AddRange( new[] 
         {
            new DataColumn( "ID",      typeof(int) ),
            new DataColumn( "Last",    typeof(string) ),
            new DataColumn( "First",   typeof(string) ),
            new DataColumn( "Grade",   typeof(int) )
         } );

         if ( request.RecordCap != -1 )
            recordCap = request.RecordCap;

         rows = STUDENTS.OrderBy( s => s.grade )
                        .ThenBy( s => s.id );

         // uncapped queries are incompatible with bookmarks
         if ( (recordCap != 0) && request.EnableBookmark )
         {
            recordCap++;   // add one row to enable "More Rows"
            if ( request.LoadType == LoadCollectionType.Next )
            {
               if ( int.TryParse( request.Bookmark, out int bookmark ) )
                  bookmarkId = bookmark;
            }
            else if ( request.LoadType != LoadCollectionType.First )
               throw new Exception( "This method does not support bidirectional bookmarks" );

            if ( bookmarkId > 0 )
               rows = rows.SkipWhile( s => s.id != bookmarkId );

            rows = rows.Take( recordCap );
         }

         foreach ( var student in rows )
            dt.Rows.Add( student.id, student.last, student.first, student.grade );

         if ( recordCap != 0 )
         {
            // set the bookmark for the next call if not finished
            if ( (recordCap != 0) && request.EnableBookmark && (dt.Rows.Count == recordCap) )
               request.Bookmark = dt.Rows[dt.Rows.Count - 1][ "ID" ].ToString();
            else
               request.Bookmark = int.MaxValue.ToString();
         }

         return dt;
      }