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

}