通用分页取数据

本方法是基于：select top 10 * from (select top 32  * from TD_Articles order by ID desc) where  order by ID desc产生的，此方法已经过测试

?

1.方法如下： #region 获取当前页显示记录        /// <summary>        /// 获取当前页显示记录        /// </summary>        /// <param name="pageIndex">当前页码</param>        /// <param name="pageSize">分页容量</param>        /// <param name="strFieldName">显示的字段</param>        /// <param name="strTableName">表名列表</param>        /// <param name="strWhere">条件列表</param>        /// <param name="strOrder">排序列表</param>        /// <param name="strAscDesc">排序方式：1为升序，0为降序</param>        /// <param name="pageCount">传出参数:总页数统计</param>        /// <param name="recordCount">传出参数:总记录数统计</param>        /// <returns></returns>        public static DataSet GetPager(int pageIndex, int pageSize, string strKey, string strFieldName, string strTableName, string strWhere, string strOrder, string strAscDesc, out int pageCount, out int recordCount)        {            DataSet ReturnDs = new DataSet();            OleDbConnection connection = new OleDbConnection(connectionString);            StringBuilder cmdText = new StringBuilder();            string strOrdert = string.Empty;            if (pageIndex < 0) pageIndex = 1;            if (pageSize < 0) pageSize = 10;            if (string.IsNullOrEmpty(strKey)) strKey = "*";            if (string.IsNullOrEmpty(strFieldName)) strFieldName = "*";            try            {                if (string.IsNullOrEmpty(strWhere))                {                    strWhere = "";                }                else                {                    strWhere = " where " + strWhere;                }                if (!string.IsNullOrEmpty(strOrder))                {                    strOrdert = strOrder = " order by " + strOrder;                }                if (!string.IsNullOrEmpty(strAscDesc))                {                    if (strAscDesc == "1")                    {                        strOrder += " Asc";                        strOrdert += " Desc";                    }                    else if (strAscDesc == "0")                    {                        strOrder += " Desc";                        strOrdert += " Asc";                    }                }                recordCount = Convert.ToInt32(DbHelperOleDb.ExecuteSclar(string.Format("select count({0}) from {1} {2}", strKey, strTableName, strWhere)));                if ((recordCount % pageSize) > 0)                {                    pageCount = recordCount / pageSize + 1;                }                else                {                    pageCount = recordCount / pageSize;                }                if (pageIndex == 1 && pageIndex != pageCount)//第一页                {                    cmdText.Append("select top " + pageSize + " " + strFieldName + " from " + strTableName + "" + strWhere + "" + strOrder + "");                }                else if (pageIndex > pageCount)//超出总页数                {                    cmdText.Append("select top " + pageSize + " " + strFieldName + " from " + strTableName + " where 1=2 " + strOrder + "");                }                else if (pageIndex == pageCount && pageIndex != 1)//是最后一页                {                    string recordT = string.Format("select top {0} {1} from {2} {3} {4} ", pageSize * pageIndex, strFieldName, strTableName, strWhere, strOrder);                    cmdText.Append("select top " + (recordCount - pageSize * (pageIndex - 1)) + " * from (" + recordT + ")" + strOrdert + "");                }                else//不是第一页                {                    string recordT = string.Format("select top {0} {1} from {2} {3} {4} ", pageSize * pageIndex, strFieldName, strTableName, strWhere, strOrder);                    cmdText.Append("select top " + pageSize + " * from (" + recordT + ")" + strOrdert + "");                }                if (connection.State == ConnectionState.Closed)                {                    connection.Open();                }                OleDbCommand cmd = new OleDbCommand(cmdText.ToString(), connection);                OleDbDataAdapter da = new OleDbDataAdapter(cmd);                if (connection.State == ConnectionState.Open)                {                    connection.Close();                }                da.Fill(ReturnDs, "ds");            }            catch (System.Data.OleDb.OleDbException e)            {                throw new Exception(e.Message);            }            return ReturnDs;        }        #endregion2.dal层： /// <summary>        /// 分页获得DataTable        /// </summary>        public DataTable GetDataTable(int pageIndex, int pageSize, string strKey, string strFieldName, string strTableName, string strWhere, string strOrder,string strAscDesc, out int pageCount, out int recordCount)        {            return DbHelperOleDb.GetPager(pageIndex, pageSize, strKey, strFieldName, strTableName, strWhere, strOrder,strAscDesc, out pageCount, out recordCount).Tables[0];        }bll层： /// <summary>        /// 分页获得DataTable        /// </summary>        public DataTable GetDataTable(int pageIndex, int pageSize, string strKey, string strFieldName, string strTableName, string strWhere, string strOrder, string strAscDesc, out int pageCount, out int recordCount)        {            return dal.GetDataTable(pageIndex, pageSize, strKey, strFieldName, strTableName, strWhere, strOrder,strAscDesc, out pageCount, out recordCount);        }3.调用示例： int pageCount = 0;            int recordCount = 0;            DataTable dt = bllArticles.GetDataTable(curPage,                                                       15,                                                       "TD_Articles.ID",                                                       "TD_Articles.*,TD_Base_Class.ClassName",                                                       "TD_Articles left join TD_Base_Class on TD_Articles.ClassID=TD_Base_Class.ID",                                                       sTj,                                                       "CreateDate",                                                       "0",                                                       out pageCount,                                                       out recordCount);