Handling very large tables in a database using C# SqlDataAdapter

Paging Through a Query Result

Paging through a query result is the process of returning the results of a query in smaller subsets of data, or pages. This is a common practice for displaying results to a user in small, easy-to-manage chunks.

The DataAdapter provides a facility for returning only a page of data, through overloads of the Fill method. However, this might not be the best choice for paging through large query results because, although the DataAdapter fills the target DataTable or DataSet with only the requested records, the resources to return the entire query are still used. To return a page of data from a data source without using the resources to return the entire query, specify additional criteria for your query that reduce the rows returned to only those required.

To use the Fill method to return a page of data, specify a startRecord parameter, for the first record in the page of data, and a maxRecords parameter, for the number of records in the page of data.

http://msdn.microsoft.com/en-us/library/tx1c9c2f%28v=vs.110%29.aspx

SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server

It's more efficient to do the paging in the database instead of inside the C# code.

http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/

SQL2012 and 2014 have some new neat "tricks" to do this function.

--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO