A new method of paging has been added through the use of SqlCommand's new ExecutePageReader method. This method pages at the SQL Server layer by using a server-side cursor and fetching specific rows. Normally, cursors, if misused, can adversely affect server performance, but in this case Microsoft has implemented it in an acceptable manner.
The ExecutePageReader method also returns a SqlDataReader. Typically, it is used with three parameters: a CommandBehavior (i.e.: close the connection when you are done), start position, and size of page). Here is an example of how it works:
SqlConnection myConnection = new SqlConnection(connectionString);
// open the connection
myConnection.Open();
// create the command
SqlCommand sqlCommand = new SqlCommand("spDoSomething");
// get a paged data reader using the specified startPosition and pageSize
SqlDataReader sqlDataReader = sqlCommand.ExecutePageReader(CommandBehavior.CloseConnection, startPosition, pageSize);
Unfortunately, there is a down side. Behind the scenes, this method makes use of dynamically created T-SQL statements. Although the manner in which it is implemented prevents against SQL Injection attacks, the DBAs would probably never let us use this as it could be inefficient. Even though we may never use it, I figured I would still tell you about it so that you would know.
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=163