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.

我們用ROW_NUMBER()方法以某種排序去增加一個遞增數值的欄位,然後我們靠這個欄位就可以做出分頁.
記住我們並沒有減少你要擷取的資料量,我們只是把網頁伺服器的資料省下來,交給Sql伺服器去處理這些事情.

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY SortColumn ASC) AS RowNumber, ColumnName
FROM TableName
) AS TEMPVIEW
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.
上面的例子是從名為TableName的資料表中取得名為ColumnName的資料,並以SortColumn欄做排序根據.我們用一個巢狀查詢去產生一個暫存資料表,
這個暫存資料表有一個我們新建立的欄叫做RowNumber.我們在下一個範例會用它來做分頁.

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;
l_sqlConn.Open();
SqlDataAdapter l_sqlDA = new SqlDataAdapter(l_sqlCmd);
DataTable l_dt = new DataTable();
l_sqlDA.Fill(l_dt);
l_sqlDA.Dispose();
l_sqlConn.Close();
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.
這個可能那一天你有一個大型分散式系統時會有用.

Advertisements

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. ^^