SQL - Select records/rows with paging and result count

You'll notice that I run two queries on the database, one to find out how many results there are, and another to get the actual records/rows.

I've tested other queries with only one select, but they were around the same in execution time as the below example with two selects.

The code given in the example below will create a stored procedure called getSearch, that, when run, will return the paged result set and a parameter that defines the total records/rows.

CREATE PROCEDURE [dbo].[getSearch]
	@RecordId int,
	-- Paging
	@PageNumber int = 1,
	@PageSize int = 50,
	@SortOrder int = 0
AS
SET NOCOUNT ON;

DECLARE @RowStart int
SET @RowStart = ((@PageNumber - 1) * @PageSize)

-- Find total search result count
DECLARE @RowCount int
SELECT
	@RowCount = COUNT(*)
FROM (
	SELECT
		TOP(400) recordId
	FROM MyTable
	WHERE
		-- Where result is not current member
		(recordId <> @RecordId) 
) AS T

SELECT
	recordId, myOtherColumnNames
FROM MyTable
WHERE
	(recordId <> @RecordId)
ORDER BY
	recordId DESC,
	-- Within specified page
	OFFSET @RowStart ROWS
	FETCH NEXT @PageSize ROWS ONLY

RETURN @RowCount

You might also see that I'm limiting my result count to 400 records/rows so I don't get too many pages in my basic paging bar, you can take this out if you want the full count returned.

Using a bit of C# magic, you're then able to run the stored procedure using the normal method of reading the returned results or rows or records or whatever you choose to call them. The clever bit comes when you want to get the total records parameter. Add the following parameter to your SqlCommand object:

objCmd.Parameters.Add("@RowCount", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

Where objCmd is a SqlCommand object, you can call the parameter anything you want as long as the Direction is set to ReturnValue.

Now you have to read your SqlDataReader and when you're finished with it, close it, because you can only read the parameter after the reader has been closed. Once you've done that, you can get the value using the below code:

Int32 intRowCount = (Int32)objCmd.Parameters["@RowCount"].Value;

And that's it, you've just retrived a paged set of records, read them and then read the total row count. With all of this, you can render your set of results within your application and calculate how many pages there are so that you can render a paging bar.


Published at

Tags: SQL,C#

Luke Alderton

Comments

Post a comment
Sit tight...
We're adding your comment.
Thank you.
Your comment has been added.
There's been a problem.
Please try again later.
Share with
Tags
Latest Comments
By Gerry on Replacing the Xamarin Header/Navigation bar with a custom view/template
11 Jul 2019, 17:12 PM
By steve choi on Xamarin Forms multiple instances of same app open
30 Jun 2019, 09:09 AM
By esrgr on JQuery Unobtrusive Validation for dynamically created elements
11 Jun 2019, 06:01 AM
By annualmars on Raspberry Pi - Running Java app on Raspbian
10 May 2019, 15:48 PM
By Shahriar H. Razi on Xamarin Forms - Working with threads
7 May 2019, 09:25 AM
By Person on MVC forms in Umbraco
30 Apr 2019, 13:33 PM
By christopher on How to add a Xamarin Forms Loading Screen/Overlay
27 Mar 2019, 11:59 AM
By Rezi on Xamarin forms - Stop/Cancel back button event
22 Mar 2019, 13:08 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development