生成SQL语句分页的通用C#函数

悟静 2010-08-06

展开全文

生成SQL语句分页的通用C#函数

2007年04月12日星期四下午 03:16

               /// <summary>
               /// 生成取得记录总数的语句
               /// </summary>
               /// <param name="sql">原查询语句</param>
               /// <returns>取得记录总数的语句</returns>
               private string     MakeRecordCountsSql(string sql)
               {
                   string sqlGetRecordCounts = "select Count(*) from (" + sql + ") as TempTable";
                   return sqlGetRecordCounts;
               }

               /// <summary>
               /// 生成分页查询语句
               /// </summary>
               /// <param name="sql">原查询语句</param>
               /// <param name="orderField">用于分页排序的字段</param>
               /// <param name="pageNumber">页码</param>
               /// <param name="pageSize">页面记录数量</param>
               /// <param name="recordCounts">记录总数</param>
               /// <param name="pageCounts">页面总数</param>
               /// <returns>分页查询语句</returns>
               private string MakePagingSql(string sql, string orderField, int pageNumber, int pageSize, int recordCounts, out int pageCounts)
               {
                   // 计算页面数量
                   if (Convert.ToInt32(pageNumber) < 1) pageNumber = 1;
                   if (Convert.ToInt32(pageSize) < 1) pageSize = 1;
                   pageCounts = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(recordCounts) / Convert.ToDouble(pageSize)));

                   string sqlQuery = "";
                   if (pageNumber == 1)
                   {
                       sqlQuery = "select top " + pageSize + " * from (" + sql + ") as TempTable order by " + orderField     + " desc";
                   }
                   else
                   {
                       sqlQuery = "select top " + pageSize + " * from (" + sql + ") as TempTable where " + orderField     + " < (select min(" + orderField     + ") as MinID from ( select top " + (pageNumber - 1) * pageSize + "     * from (" + sql + ") as MaxTempTable order by " + orderField     + " desc) as MinTempTable) order by " + orderField     + " desc";
                   }
                   return sqlQuery;
               }