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.


By Luke Alderton at 17 Dec 2015, 22:44 PM

Tags: SQL,C#

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 dipbleds on Raspberry Pi - Bluetooth using Bluecove on Raspbian
12 Dec 2017, 21:29 PM
By Ich on Replacing the Xamarin Header/Navigation bar with a custom view/template
26 Nov 2017, 17:11 PM
By Ravi Motha on My experience at Umbraco UK Festival 2017
12 Dec 2017, 08:39 AM
By Borges on How to add a Xamarin Forms Loading Screen/Overlay
7 Nov 2017, 19:11 PM
By Rutul Mehta on Auto/Custom height on Xamarin Forms WebView for Android and iOS
4 Nov 2017, 03:15 AM
By Pablo on Xamarin MasterDetailPage has large margin/padding at top
26 Oct 2017, 15:31 PM
By faiza on Using MaryTTS or OpenMary in Java
22 Oct 2017, 12:54 PM
By Nietoperz on Using MaryTTS or OpenMary in Java
29 Sep 2017, 14:34 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development