I'm using custom paging with a RadGrid and passing a StartRow and RowCount parameters to a stored procedure to return only my desired rows. The tables I work with are very big so the whole process needs to be optimized as much as possible.
I would like to use an SqlDataReader because I've read that would offer the greatest speed, however I'm having difficulties getting the TotalRows with an SqlDataReader.
I also can't access an Output Parameter because its value isn't set until the DataReader is closed. Therefore I've added the Total Rows as an additional field on my resultset. However, if I read the first row to get the Total Rows, the DataReader moves onto the next row and it isn't displayed in the Grid.
I can get it working nicely by using an SqlDataAdapter to populate a DataTable and passing that to the grid, I just wanted to know if there was any way to get the RowCount by using the SqlDataReader and Paging.