Custom Paging issues...

[Rick]

Hi,

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.

[Veli]

Hello Rick,

I think you should be all set if you simply use an SQL query that will return the count of your records in the respective table:

SELECT COUNT(*) FROM [My Table Name]

Now the first column of the first row returned by your data reader would contain the total number of rows in your table.

Sincerely yours,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Rick]

As a developer of 15 years I am already aware of that particular statement, but thanks for pointing it out again for me.

The issue I have with using a separate query to retrieve the row count is that my query is complicated. In this particular instance, 6 tables are joined and the WHERE clause contains about 8 clauses.

To issue two commands would require a lot of SQL to be duplicated. I have already experimented with views and they just lose too much performance.

I'm not using a DataReader now, I move the resultset into a DataTable and I can then read the first row and find the TotalRows value. Performance doesn't seem impacted as I am using Paging hence returning about 20 records at a time.

[Wyatt]

If you are using SQL Server 2005 or greater, you can add the following TotalCount column to get the total row count in the same result set.

select TotalCount = COUNT(*) OVER(), Column1, Column2, ...