Paging query in Sql Server 2005 or above 用Sql Server2005做分頁查詢

MS Sql server do not have none standard features like SQL_CALC_FOUND_ROWS and LIMIT in MySql.
That makes paging by SQL a little difficult. Luckly start from Sql Server 2005, MS provide a kind of work-around.

微軟的Sql Server並沒有像MySql一樣有提供非標準的SQL_CALC_FOUND_ROWS和LIMIT去做分頁動作.
這樣令用SQL做分頁困難了一點,還好Sql server從2005版開始提供了一個解決方法.

We can use the ROW_NUMBER() method to add an incremental column with some ordering and use the row number we’ve just created to achieve paging.
Note that this doesn’t mean the data you retrieve is lesser, NO, we are doing this to save memory and time for the web server by making paging happen in SQL server.


SELECT ROW_NUMBER() OVER (ORDER BY SortColumn ASC) AS RowNumber, ColumnName
FROM TableName
WHERE RowNumber > 20 AND RowNumber < 50
ORDER BY RowNumber

The above example shows how to get one column ColumnName from table with TableName, order by SortColumn where row number between 20 and 50.
We use the nested query to generate a temporary view, this view will contain a new column call RowNumber. We will use it to do paging in the next example.

public static DataTable QueryDatabaseForDataTable(string queryStr, int pageNumber, int RowsPerPage, string OrderByColumn)
SqlConnection l_sqlConn = GetSqlConnection();
//modify query string
string l_rowNumberColumn = string.Format(@"SELECT ROW_NUMBER() OVER (ORDER BY {0:s} ASC) AS RowNumber, ", OrderByColumn);
queryStr = queryStr.Replace("SELECT ", l_rowNumberColumn);
string l_wrapQueryStr = string.Format("SELECT * FROM ({0:s}) AS TEMPVIEW WHERE RowNumber Between @LowerBoundIndex AND @UpperBoundIndex ORDER BY RowNumber", queryStr);
SqlCommand l_sqlCmd = new SqlCommand(l_wrapQueryStr, l_sqlConn);
l_sqlCmd.Parameters.Add("LowerBoundIndex", SqlDbType.Int).Value = pageNumber * RowsPerPage;
l_sqlCmd.Parameters.Add("UpperBoundIndex", SqlDbType.Int).Value = (pageNumber + 1) * RowsPerPage;
SqlDataAdapter l_sqlDA = new SqlDataAdapter(l_sqlCmd);
DataTable l_dt = new DataTable();
return l_dt;

In this example, it is a method to do general paging retrieve, it can be even more general, we will leave this to the readers.
We pass the query string, page number, number of rows in one page and the ordering column as parameters. The method will return the data in that page for us.

This might comes in handy someday when you have a large distrubuted system.


C# and M$ SQL server co-operation

C float vs C# float

There is one thing worth pointing out.

I learned C language before Java and C#, C is not object-oriented and it’s datatypes are simple. C uses ‘float’ for decimal number and it’s arithmetic operation.

But for OO language Java and C#, ‘float’ has a different meaning. Because ‘float’ is used to represent a decimal number with many decimal places, just like double, it will lose some precision due to its data structure, if you are doing financial application with a few decimal places, you should use ‘decimal’ for exact precision. let’s look at an example.

float   input(program)    outcome(database)

C           1.1                      1.1                                            

C#         1.1                      1.100000012357486…

you can still get exact result by rounding up to 7th places. Double is 15th.

Database datatype vs .Net datatype

When working with database, this mapping will help you. (reference)

SQL type                  CLR type(sql server)               CLR type(.NET)

decimal                     SqlDecimal                             Decimal

float                          SqlDouble                               Double

Real                           SqlSingle                                 Single

Using Real+Single will have the effect simular to C, input 1.1 -> outcome 1.1

for example, working with SqlDataReader

Double d = reader.GetDouble(“SQL float type”)

but strangly there isn’t a GetSingle() method present, I use GetSqlSingle() instead, seems no problem. 

Null Handling

1. for Null value return from a query method

let’s take Guid as an example for Nullable.

you can use a Guid? for shorthand Nullable<Guid>, use it as a return type for your database query method.

and the use if(guid.HasValue) to determind where the query returns a value or not.

2. for null value inside the query method

use if(DBNull.Value.Equals(row[i])) to determind where a field in a row retrieve from the database is null.

These two are very handy method for handling database query. ^^