With this headline, I would like to make an introduction to the generic PagedCollection<T> class located in the Cuemon.Collections.Generic namespace. If you are curious about the code behind this class, you can check it out on CodePlex, or you can go right ahead and check the sample for quick adaptation of this class.



In the following example I downloaded a copy of AdventureWorks2014 so that we have a way of demonstrating a typical paging scenario. To keep things simple, I chose to initialize an instance of the PagedCollection<T> class, where T is a DataTransferRow object ideal for the role of a simple data transfer carrier. 

Because the PagedCollection class is simple, yet very flexible, I used the constructor overload that takes three arguments; a selector delegate (pointing to AdventureWorksSelector), a PagedSettings object and a counter delegate (pointing to AdventureWorksCounter).

The rest is pretty straight forward and I will let the code example speak for itself. 

  1. using System.Collections.Generic;
  2. using System.Data;
  3. using System.Diagnostics;
  4. using Cuemon.Collections.Generic;
  5. using Microsoft.VisualStudio.TestTools.UnitTesting;
  6.  
  7. namespace Cuemon.Data.SqlClient
  8. {
  9.     [TestClass]
  10.     public class PagedCollectionTest
  11.     {
  12.         private static readonly string Connection = DataManager.ConnectionStringSettings["AdventureWorks"];
  13.         private static readonly SqlDataManager Manager = new SqlDataManager(Connection);
  14.  
  15.         [TestMethod]
  16.         public void SqlDatabasePagingTest()
  17.         {
  18.             PagedSettings settings = new PagedSettings()
  19.             {
  20.                 PageNumber = 1,
  21.                 PageSize = 25,
  22.                 SortOrderBy = "Suffix",
  23.                 SortOrderDirection = SortOrder.Descending
  24.             };
  25.  
  26.             PagedCollection<DataTransferRow> pagedRows = new PagedCollection<DataTransferRow>(AdventureWorksSelector, settings, AdventureWorksCounter);
  27.             
  28.             Trace.WriteLine("Paging information: {0}".FormatWith(pagedRows));
  29.             Trace.WriteLine("");
  30.             Trace.WriteLine("------- Iterate over {0} elements shared among {1} pages -------".FormatWith(pagedRows.Count, pagedRows.PageCount));
  31.             foreach (DataTransferRow row in pagedRows)
  32.             {
  33.                 Trace.WriteLine("{0}{1}{2}{3}{4}".FormatWith(
  34.                     Condition.TernaryIf(row["Title"] null, ConditionNull, ConditionValue, row["Title"].As<string>()),
  35.                     Condition.TernaryIf(row["FirstName"] null, ConditionNull, ConditionValue, row["FirstName"].As<string>()),
  36.                     Condition.TernaryIf(row["MiddleName"] null, ConditionNull, ConditionValue, row["MiddleName"].As<string>()),
  37.                     Condition.TernaryIf(row["LastName"] null, ConditionNull, ConditionValue, row["LastName"].As<string>()),
  38.                     Condition.TernaryIf(row["Suffix"] == null, ConditionNull, ConditionValue, row["Suffix"].As<string>())));
  39.             }
  40.             Trace.WriteLine("------- Ordered by {0} {1} -------".FormatWith(pagedRows.Settings.SortOrderBy, pagedRows.Settings.SortOrderDirection));
  41.         }
  42.  
  43.         private string ConditionValue(string value)
  44.         {
  45.             return value.Trim() + " ";
  46.         }
  47.  
  48.         private string ConditionNull(string value)
  49.         {
  50.             return "";
  51.         }
  52.  
  53.         private int AdventureWorksCounter(PagedSettings settings)
  54.         {
  55.             return Manager.ExecuteScalarAsInt32(new DataCommand("SELECT COUNT(*) FROM [Person].[Person]"));
  56.         }
  57.  
  58.         private IEnumerable<DataTransferRow> AdventureWorksSelector(PagedSettings settings)
  59.         {
  60.             DataCommand command = new DataCommand(@"SELECT * FROM [Person].[Person]
  61. {0}
  62. OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY".FormatWith(
  63.                 settings.HasSortOrderByDefined
  64.                     ? "ORDER BY [{0}] {1}".FormatWith(settings.SortOrderBy, settings.SortOrderDirection.ToOrderByClause())
  65.                     : "",
  66.                 (settings.PageNumber - 1)*settings.PageSize,
  67.                 settings.PageSize));
  68.  
  69.             using (IDataReader reader = Manager.ExecuteReader(command))
  70.             {
  71.                 return reader.ToRows();
  72.             }
  73.         }
  74.     }
  75. }


The above code example should produce something similar to this output:

Paging information: Count: 25, PageCount: 799, PageNumber: 1, PageSize: 25, TotalElementCount: 19972

------- Iterate over 25 elements shared among 799 pages -------
Mr. Lester J. Bowman Sr. 
Mr. David J. Byrnes Sr. 
Mr. Rudolph J. Dillon Sr. 
Mr. Walter J. Mays Sr. 
Mr. Darren M. Watkins Sr. 
Mr. Mike E. White Sr. 
Ms. Jeanie R. Glenn PhD 
Ms. Kerry N. Westguard PhD 
Dan K Bacon Jr. 
Mr. James T. Aguilar Jr. 
Mr. Thomas B. Armstrong Jr. 
Mr. James B. Bailey Jr. 
Mr. Douglas A. Baldwin Jr. 
Mr. Christopher R. Beck Jr. 
Mr. Stanley A. Alan Jr. 
Mr. Richard M. Bentley Jr. 
Mr. John M. Berger Jr. 
Mr. Willie P. Brooks Jr. 
Mr. Dick R. Brummer Jr. 
Mr. Timothy B. Burnett Jr. 
Mr. Johnny A. Caprio Jr. 
Mr. John T. Donovan Jr. 
Mr. Duane R. Fitzgerald Jr. 
Mr. James B. Haugh Jr. 
Mr. Edward J. Kozlowski Jr. 
------- Ordered by Suffix Descending -------


If you have questions, please do not hesitate starting a discussion under comments .. and as always; happy coding :-)