本文转自：
http://topic.csdn.net/u/20090303/23/16ea3c41-51dc-4ec2-a40a-b1c315fecf15.html
用sql语句分页,下面是sql语句分页类
using System;
using System.Collections.Generic;
using System.Text;
namespace web
{
public class PagerQuery
{
private int _pageIndex;
private int _pageSize = 20;
private string _pk;
private string _fromClause;
private string _groupClause;
private string _selectClause;
private string _sortClause;
private StringBuilder _whereClause;
public DateTime DateFilter = DateTime.MinValue;
public PagerQuery()
{
_whereClause = new StringBuilder();
}
/**/
/// <summary>
/// 主键
/// </summary>
public string PK
{
get { return _pk; }
set { _pk = value; }
}
public string SelectClause
{
get { return _selectClause; }
set { _selectClause = value; }
}
public string FromClause
{
get { return _fromClause; }
set { _fromClause = value; }
}
public StringBuilder WhereClause
{
get { return _whereClause; }
set { _whereClause = value; }
}
public string GroupClause
{
get { return _groupClause; }
set { _groupClause = value; }
}
public string SortClause
{
get { return _sortClause; }
set { _sortClause = value; }
}
/**/
/// <summary>
/// 当前页数
/// </summary>
public int PageIndex
{
get { return _pageIndex; }
set { _pageIndex = value; }
}
/**/
/// <summary>
/// 分页大小
/// </summary>
public int PageSize
{
get { return _pageSize; }
set { _pageSize = value; }
}
/**/
/// <summary>
/// 生成缓存Key
/// </summary>
/// <returns> </returns>
public string GetCacheKey()
{
const string keyFormat = "Pager-SC:{0}-FC:{1}-WC:{2}-GC:{3}-SC:{4}";
return string.Format(keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause);
}
/**/
/// <summary>
/// 生成查询记录总数的SQL语句
/// </summary>
/// <returns> </returns>
public string GenerateCountSql()
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
return string.Format("Select count(0) {0}", sb);
}
/**/
/// <summary>
/// 生成分页查询语句，包含记录总数
/// </summary>
/// <returns> </returns>
public string GenerateSqlIncludeTotalRecords()
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(SelectClause))
SelectClause = "*";
if (string.IsNullOrEmpty(SortClause))
SortClause = PK;
int start_row_num = (PageIndex - 1) * PageSize + 1;
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
string countSql = string.Format("Select count(0) {0};", sb);
string tempSql =
string.Format(
//"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4};",
//SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
return tempSql + countSql;
}
/**/
/// <summary>
/// 生成分页查询语句
/// </summary>
/// <returns> </returns>
public string GenerateSql()
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(SelectClause))
SelectClause = "*";
if (string.IsNullOrEmpty(SortClause))
SortClause = PK;
int start_row_num = (PageIndex - 1) * PageSize + 1;
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
return
string.Format(
"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4}",
SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
}
}
}
调用如下:
/// <summary>
/// 生成分页查询语句，包含记录总数
/// </summary>
/// <param name="PageIndex"> </param>
/// <param name="PageSize"> </param>
/// <param name="PK"> </param>
/// <param name="SelectField"> </param>
/// <param name="FromTable"> </param>
/// <param name="SortField">如“ID DESC”,若无可为null </param>
/// <param name="Where">不带"where",第一个字母必须为空格 </param>
/// <returns> </returns>
public string GenerateSqlIncludePageCount(int PageIndex,int PageSize,string PK,string SelectField,string FromTable,string SortField,string Where)
{
PagerQuery query = new PagerQuery();
query.PageIndex = PageIndex;
query.PageSize = PageSize;
if (string.IsNullOrEmpty(PK))
PK = "id";
query.PK = PK;
query.SelectClause = SelectField;
query.FromClause = FromTable;
query.SortClause = SortField;
if (!string.IsNullOrEmpty(Where))
{
query.WhereClause.Append(Where);
}
return query.GenerateSqlIncludeTotalRecords();
}
转载于:https://www.cnblogs.com/feima-lxl/archive/2009/06/25/1510594.html
