i am trying to write a data iterator for SQL, it looks like the best approach is to cook up some dynamic sql for this problem.
I want the iterator to support paging, sorting, and filtering of the data, and ideally not iterate over a memory copy but to not even select the data in the first place, perhaps LINQ to SQL or Entity Framework would provide somthing similar?
The funny thing is I already wrote a nice data iterator for SQLCE of all databases it supports a SqlCeResultSet, and an ExecuteResultSet concept making this very easy but this has yet to make it to the full featured server database product. I can imagine why this is so basically as an embedded DB you can lock it for single-user mode, and there is a reduced language support as well, making it easier.
Perhaps i am just too tired or not educated enough to understand an acceptable way of doing this. I think doing a SQL Data Reader may be the ticket but if i am not mistaken you need to keep the connection open as you iterate which doesn't seem to make much sense, the other approach is to just select the page of data return it and offer an iterator over that collection, when you get to the end it pulls down next page, this would work but means i have to cook up a lot of SQL constructs to support sorting, filtering and paging but in the end that may be the solution.
Thanks