sql2000資料庫 sql語句C#分頁類代碼

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using fjt.DBUtility;

/// <summary>
/// 分頁用sql查詢類 http://www.my400800.cn

/// </summary>
public class SelectTableDataForPage
{
    /// <summary>
    /// 建構函式
    /// </summary>
    public SelectTableDataForPage()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    #region  public static int getSelectNowPageCount(string strTableName,string strSqlWhere, int iPageSize) 指定表指定條件總頁數取得
    /// <summary>
    /// 指定表指定條件總頁數取得
    /// </summary>
    /// <param name="strTableName">表名</param>
    /// <param name="strSqlWhere">查詢條件</param>
    /// <param name="iPageSize">每頁顯示記錄數</param>
    /// <returns></returns>
    public static int getSelectNowPageCount(string strTableName, string strSqlWhere, int iPageSize)
    {
        //如果指定每頁顯示記錄數小於0，每頁顯示記錄數10設定
        if (iPageSize <= 0) iPageSize = 10;
        StringBuilder sbSql = new StringBuilder("select count(*) from ");
        sbSql.Append(strTableName);
        if (!string.IsNullOrEmpty(strSqlWhere))
        {
            sbSql.Append(" where 1=1 ").Append(strSqlWhere);

        }
        int RowCount = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, sbSql.ToString()));
        int pageCount = RowCount % iPageSize > 0 ? RowCount / iPageSize + 1 : RowCount / iPageSize;

        return pageCount;
    }
    #endregion

    #region 取得指定條件指定頁碼指定排序的表資料 public static DataTable SelectNowPageForDS(string strTableName, string strSelFeilds, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
    /// <summary>
    /// 取得指定條件指定頁碼指定排序的表資料
    /// </summary>
    /// <param name="strTableName">表名</param>
    /// <param name="strSelFeilds">取得欄位 多個欄位用逗號分開</param>
    /// <param name="strPk">唯一主鍵</param>
    /// <param name="strOrderBy">排序欄位，多個用逗號分開</param>
    /// <param name="strWhere">查詢條件</param>
    /// <param name="iNowPage">要取得的頁碼資料</param>
    /// <param name="iPageSize">當前頁碼顯示記錄數量</param>
    /// <returns></returns>
    public static DataTable SelectNowPageForDS(string strTableName, string strSelFeilds, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
    {
        //如果指定每頁顯示記錄數小於0，每頁顯示記錄數10設定
        if (iPageSize <= 0) iPageSize = 10;

        //分頁用sql取得
        string strExecSql = GetSelectForPageSql(strTableName, strSelFeilds, strPk, strOrderBy, strWhere, iNowPage, iPageSize);
        DataTable dtSel = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, strExecSql).Tables[0];
        return dtSel;
    }
    #endregion

    #region 取得指定條件指定頁碼指定排序的sql查詢語句

    /// <summary>
    /// 取得指定條件指定頁碼指定排序的sql查詢語句
    /// </summary>
    /// <param name="strTableName">表名</param>
    /// <param name="strSelFeilds">取得欄位 多個欄位用逗號分開</param>
    /// <param name="strPk">唯一主鍵</param>
    /// <param name="strOrderBy">排序欄位，多個用逗號分開</param>
    /// <param name="strWhere">查詢條件</param>
    /// <param name="iNowPage">要取得的頁碼資料</param>
    /// <param name="iPageSize">當前頁碼顯示記錄數量</param>
    /// <returns>sql查詢語句返回</returns>
    public static string GetSelectForPageSql(string strTableName, string strSelFeilds, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
    {

        //如果指定每頁顯示記錄數小於0，每頁顯示記錄數10設定
        if (iPageSize <= 0) iPageSize = 10;

        //拼接後結果
        //http://www.my400800.cn
        //語句形式：
        //        SELECT TOP 頁大小 *
        //FROM TestTable
        //WHERE (ID NOT IN
        //          (SELECT TOP 頁大小*頁數 id
        //         FROM 表
        //         ORDER BY id))
        //ORDER BY ID
        //檢索欄位內容如果是空 取得所有欄位資訊
        strSelFeilds = string.IsNullOrEmpty(strSelFeilds) ? "*" : strSelFeilds;

        StringBuilder sbSql = new StringBuilder("");
        sbSql.Append("( SELECT ")
            .AppendLine(strSelFeilds)
            .AppendLine(" FROM ")
            .AppendLine(strTableName);
        //查詢條件拼接處理
        if (!string.IsNullOrEmpty(strWhere))
        {
            sbSql.AppendLine(" WHERE  1=1 ").Append(strWhere);
        }
        sbSql.AppendLine(") selForPageTempTable");

        //排序欄位sql用字元取得
        string strSqlOrderBy = "";
        if (!string.IsNullOrEmpty(strOrderBy))
        {
            strSqlOrderBy = " ORDER BY " + strOrderBy;

        }

        StringBuilder sbExecSql = new StringBuilder("SELECT ");
        sbExecSql.Append(" TOP ").Append(iPageSize).Append(" ").Append(strSelFeilds).Append(" FROM ").Append(sbSql).Append(" WHERE (").Append(strPk)
                .Append(" NOT IN ( SELECT TOP ").Append((iNowPage - 1) * iPageSize).Append(" ").Append(strPk).Append(" FROM ").Append(strTableName)
                .Append(strSqlOrderBy).Append(")) ").AppendLine(strSqlOrderBy);

        return sbExecSql.ToString();
    }

    /// <summary>
    /// 取得指定條件指定頁碼指定排序的sql查詢語句
    /// </summary>
    /// <param name="strTableName">表名</param>
    /// <param name="strPk">唯一主鍵</param>
    /// <param name="strOrderBy">排序欄位，多個用逗號分開</param>
    /// <param name="strWhere">查詢條件</param>
    /// <param name="iNowPage">要取得的頁碼資料</param>
    /// <param name="iPageSize">當前頁碼顯示記錄數量</param>
    /// <returns>sql查詢語句返回</returns>
    /// <returns>sql查詢語句返回</returns>
    public static string GetSelectForPageSql(string strTableName, string strPk, string strOrderBy, string strWhere, int iNowPage, int iPageSize)
    {
        return GetSelectForPageSql(strTableName, null, strPk, strOrderBy, strWhere, iNowPage, iPageSize);
    }

    /// <summary>
    /// 取得指定條件指定頁碼指定排序的sql查詢語句
    /// </summary>
    /// <param name="strTableName">表名</param>
    /// <param name="strPk">唯一主鍵</param>
    /// <param name="strOrderBy">排序欄位，多個用逗號分開</param>
    /// <param name="iNowPage">要取得的頁碼資料</param>
    /// <param name="iPageSize">當前頁碼顯示記錄數量</param>
    public static string GetSelectForPageSql(string strTableName, string strPk, string strOrderBy, int iNowPage, int iPageSize)
    {
        return GetSelectForPageSql(strTableName, null, strPk, strOrderBy, null, iNowPage, iPageSize);
    }
    #endregion

}