SqlServer分頁方法

標籤：

/// <summary>/// 使用虛擬表進行分頁查詢,不適用明確知道列名的查詢/// </summary>/// <param name="sql">sql 如"select * from name where 1=1"</param>/// <param name="pageIndex">頁碼 如"1"</param>/// <param name="pageSize">條數 如"100"</param>/// <returns>DataTable</returns>public static DataTable SelectPaging(String sql, int pageIndex, int pageSize){    //定義虛擬表名稱    string temporaryTable = "#" + randString();    //建立您虛擬表    string sqlPaging = "select identity(int) as ZZZZZ, * into " + temporaryTable + " from (select TOP 100 Percent * from ( " + sql + ") a) a ";    //使用虛擬表進行分頁查詢    sqlPaging += "select * from " + temporaryTable + " a WHERE 1=1 "        + "AND a.ZZZZZ >= " + pageIndex * pageSize + " AND a.ZZZZZ <= " + ((pageIndex + 1) * pageSize - 1) + " ";    //刪除虛擬表    sqlPaging += "DROP TABLE " + temporaryTable;    //執行Sql語句 SelectCustomSql為執行sql的方法需自己定義    DataTable dt = SelectCustomSql(sqlPaging);    //判斷返回結果是否為空白    if (dt != null)    {        //移除識別欄位        dt.Columns.Remove("ZZZZZ");    }    //返回結果    return dt;}

其他分頁方法百度上比較多。

SqlServer分頁方法