1. In your code keep variables startIndex, pageSize and totalRows.
2. On Init, set startIndex = 0.
3. Set pageSize to default e.g. pageSize = 10 or whatever you want/user wants.
4. Set currentPage to 1.
5. Set totalRows to record count from query result.
6. Take the number of source rows and copy to destination table
See example code (sorry for the verbosity but I used dotNetFiddle web site to try it for you).
7. All you need to do is specify the current page and it will get the rows for that range.
NOTE: this will not be very good performance on big sets. Use LINQ or move the paging logic shown here to Database side using parameters and temp table to get the Reference id columns.
using System;
using System.Data;
using System.Collections.Generic;
using System.Xml;
public class Program
{
private static DataTable dt;
private static DataTable refDt;
private static int startIndex;
private static int pageSize = 5;
private static int currentPage = 3;
private static int totalRows;
private static int totalPages;
public static void Main()
{
dt = new DataTable("MyDataTable");
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("col1"),
new DataColumn("col2"),
new DataColumn("col3")
});
List<object[]> data = new List<object[]>
{
new object[] { "1.1", "1.2", "1.3" },
new object[] { "2.1", "2.2", "2.3" },
new object[] { "3.1", "3.2", "3.3" },
new object[] { "4.1", "4.2", "4.3" },
new object[] { "5.1", "5.2", "5.3" },
new object[] { "6.1", "6.2", "6.3" },
new object[] { "7.1", "7.2", "7.3" },
new object[] { "8.1", "8.2", "8.3" },
new object[] { "9.1", "9.2", "9.3" },
new object[] { "10.1", "10.2", "10.3" },
new object[] { "11.1", "11.2", "11.3" },
new object[] { "12.1", "12.2", "12.3" },
};
foreach(object[] dataItems in data)
{
dt.Rows.Add(dataItems);
}
totalRows = dt.Rows.Count;
refDt = new DataTable("RefDt");
refDt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Index"),
new DataColumn("Id")
});
for(int i=0;i<dt.Rows.Count;i++)
{
refDt.Rows.Add(new object[] {Convert.ToString(i), dt.Rows[i]["col1"]});
}
DataTable dest = new DataTable("Destination");
dest = dt.Clone();
startIndex = (currentPage * pageSize) - pageSize;
Console.WriteLine("Reading " + pageSize + " rows starting at row index " + startIndex);
for(int i = startIndex; (pageSize * currentPage) > totalRows ? i < totalRows : i < pageSize * currentPage; i++)
{
string lookupFilter = "Index = '" + i.ToString() + "'";
DataRow dr = (DataRow)refDt.Select(lookupFilter)[0];
string id = Convert.ToString(dr[1]);
string selectFilter = "col1 = '" + id + "'";
DataRow source = dt.Select(selectFilter)[0];
dest.Rows.Add(source.ItemArray);
string output= "";
for(int c = 0; c < dest.Columns.Count; c++)
{
output += Convert.ToString(dest.Rows[dest.Rows.Count-1][c]) + ", ";
}
Console.WriteLine(output.Remove(output.Length-2,2));
}
int remainderRows = dt.Rows.Count % pageSize;
totalPages = remainderRows > 0 ? totalRows/pageSize + 1: totalRows/pageSize;
Console.WriteLine("Last page has " + remainderRows + " rows");
Console.WriteLine("Page " + currentPage + " of " + totalPages + " (Total " + dt.Rows.Count + " rows)");
}
}