This post is based on a post by fellow CB blogger Dave Hayden. It deals with paging through a set of sql server data. Dave has a very clear and elegant solution but it did take some puzzling to find out why things have to be done that way. Dave’s solution steered towards a sproc. Trying to stay away from that I want to present a dynamic sql version of the same idea.
Since the 2005 version of MS SQL server has the ROW_NUMBER function. This function returns the sequence number of the result set rows. Like this select statement.
SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Name) AS Row FROM Fleet
The OVER part determines the sort order of the result set.
This row number is an ideal base for retrieving data in pages. With a page size of 20 the first page will contain row numbers 1 to 21, the second 21 to 40, etc. Alas you cannot use the function result directly in the where clause. To retrieve the first page this sql will not work.
SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Name) AS Row FROM Fleet WHERE ROW < 21
To get around this you need an outer select to wrap up. Like this:
SELECT Id, Name FROM
(SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Name) AS Row FROM Fleet) AS tmp
WHERE ROW < 21
There is a pattern in this
SELECT MyCols FROM
(SELECT MyCols, ROW_NUMBER() OVER (ORDER BY MyOrder) AS Row FROM MyView) AS tmp
WHERE ROW < MaxRow
The values for the example query I started with
MyCols=”Id, Name”
MyView=”Fleet”
MyOrder=”Name”
In a more complex scenario the data will be a view on several tables resulting in one result set. Here the list of columns for the result set will not match the list of columns in the view.
Take this query.
SELECT Name,Id, City FROM (SELECT Person.Name,Person.Id, City.City , ROW_NUMBER() OVER (ORDER BY Name) AS Row FROM Persons INNER JOIN City On Person.CityId=City.ID) AS tmp WHERE ROW < 21
The pattern now evolves to
SELECT MyCols FROM (SELECT MyViewCols, ROW_NUMBER() OVER (ORDER BY MyOrder) AS Row FROM MyView) AS tmp WHERE ROW < MaxRow
MyCols=”Name, Id, City”
MyViewCols=”Person.Name,Person.Id, City.City ”
MyView=”Persons INNER JOIN City On Person.CityId=City.ID”
MyOrder=”Name”
To complete the pattern I have to fully parameterize the row numbers. Here I also limit the inner query with a SELECT TOP, no need to retrieve more rows than maxrow.
SELECT MyCols FROM
(SELECT TOP MaxRow ROW_NUMBER OVER (ORDER BY MyOrder ) AS Row, MyViewCols FROM MyView) AS Tmp
WHERE Row > @MinRow AND Row <= @MaxRow
It is cumbersome to assemble this query over and over again. Having to do all sql myself (more on that in a later post) I have built a small C# function which will assemble the paging query from the fragments passed in. It uses the pattern described, builds a sqlCommand and is used like this.
const string columns = "Code, ExpireDate, Description";
const string view = "Permit INNER JOIN ProductCell ON Permit.Code = ProductCell.Code WHERE Permit.LicenceId=@LicenceId";
const string orderBy = "Permit.Code";
const string viewColumns = "Permit.Code, ExpireDate, Description";
var cmd = BuildPagedSelectCommand(pageNo, columns, view, orderBy, viewColumns, pageSize);
cmd.Parameters.AddWithValue("@LicenceId", licenceId);
The function itself is pretty straightforward
protected SqlCommand BuildPagedSelectCommand(int pageNumber, string columnList, string selectFrom, string orderBy, string columnsAs, int pageSize)
{
var minRow = (pageNumber – 1) * pageSize;
var maxRow = minRow + pageSize;
var cmd = new SqlCommand(string.Format("SELECT {0} FROM (SELECT TOP {1} ROW_NUMBER() OVER (ORDER BY {2}) AS Row,{3} FROM {4}) AS tmp WHERE Row > @MinRow AND Row <= @MaxRow",
columnList, maxRow, orderBy, columnsAs, selectFrom), connection);
cmd.Parameters.AddWithValue("@MinRow", minRow);
cmd.Parameters.AddWithValue("@MaxRow", maxRow);
return cmd;
}
All it does is calculate the rownumbers, assemble the sql statement and create the sql command to return.
For the simpler cases where the result columns match the underlying view columns I have created an overload.
protected SqlCommand BuildPagedSelectCommand(int pageNumber, string columnList, string selectFrom, string orderBy, int pageSize)
{
return BuildPagedSelectCommand(pageNumber, columnList, selectFrom, orderBy, columnList, pageSize);
}
It uses the original overload using the columnlist twice.
Paging data is not that complicated but the syntax of the query required is somewhat confusing. I hope this example helps in clarifying it.
