Lazy Loading on a Collection of Objects

Question

i have a sql query that can bring back a large number of rows via a DataReader. Just now I query the DB transform the result set into a List(of ) and data bind the Grid to the List.

This can result occasionally in a timeout due to the size of Dataset.

I currently have a three teir setup where by the UI is acting on the List of objects in the business layer.

Can anyone suggest the best approach to implementing lazy loading in this scenatrio? or is there some other way of implementing this cleanly?

I am currently using Visual Studio 2005, .NET 2.0

EDIT: How would paging be used in this instance?

Answer 1

LINQ to SQL seems to make sense in your situation.

Otherwise if for any reason, you don't want to use LINQ to SQL (e.g. you are on .NET 2.0), consider writing an iterator that reads the DataReader and converts it to the appropriate object:

IEnumerator<MyObject> ReadDataReader() {
  while(reader.MoveNext())
    yield return FetchObject(reader);
}

Answer 2

Do you need to bring back all the data at once? You could considering paging.

Answer 3

Paging might be your best solution. If you are using SQL Server 2005 or greater there was new feature added. ROWNUMBER():

WITH MyThings AS
(
    SELECT ThingID, DateEntered,
    ROW_NUMBER() OVER (ORDER BY DateEntered) AS 'RowNumber'
    FROM dbo.Things 
) 
SELECT * 
FROM ThingDetails
WHERE RowNumber BETWEEN 50 AND 60;

There is an example by David Hayden which is very helpful in demonstrating the SQL .

This method would decrease the number of records returned, reducing the overall load time. It does mean that you will have to do a bit more to track where you are in the sequence of records, but it is worth the effort.
The standard paging technique requires everything to come back from the database and then be filtered at the middle tier, or client tier (code-behind) this method reduces the records to a more manageable subset.