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 Chris on Disabling XAML Hot Reload
10 Jun 2020, 15:23 PM
By Ben on Raspberry Pi - Running Java app on Raspbian
24 May 2020, 09:01 AM
By BP on Disabling XAML Hot Reload
14 May 2020, 21:59 PM
By Andy on Xamarin Forms - Working with threads
12 May 2020, 09:16 AM
By mitchell on Xamarin forms - Stop/Cancel back button event
29 Apr 2020, 18:16 PM
By Paolo on Raspberry Pi - Running Java app on Raspbian
19 Apr 2020, 11:04 AM
By Alexander Gontar on Xamarin Forms - Working with threads
13 Mar 2020, 07:10 AM
By Arthur Richardson on Disabling XAML Hot Reload
12 Mar 2020, 17:52 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development