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.
這個可能那一天你有一個大型分散式系統時會有用.
Maxi's blog 