分页查询的sql语句

/// <summary>
/// 分页获取数据集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="_pagerIndex"></param>
/// <param name="_pagerSize"></param>
/// <param name="WhereString"></param>
/// <param name="_orderString"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public DataSet GetDataSetByPage<T>(int _pagerIndex, int _pagerSize, string WhereString, string _orderString, ref int totalRecord) where T : new()
{
var type = typeof(T);
var name = GetTableName<T>();
string getCount = " select count(*) from " + name;
if (!string.IsNullOrEmpty(WhereString))
getCount += " where " + WhereString;
totalRecord = (int)SqlHelper.ExecuteScalar(conn, CommandType.Text, getCount, null);
T oo = new T();
List<PropertyInfo> PI2list = oo.GetType().GetProperties().ToList();
if (string.IsNullOrEmpty(_orderString))//排序不能为空
{
object idname = "";
for (int j = 0; j < PI2list.Count; j++)
{
if (PI2list[j].Name.ToLower() == "id")
{
idname = PI2list[j].Name;
_orderString = idname + " desc";
break;
}
}
}
string sql = "(select row_number() over (order by " + _orderString + ") as rowId,* from " + name;//查询语句
if (!string.IsNullOrEmpty(WhereString))
{
sql += " where " + WhereString;
}
if (_pagerIndex > 0)
{
_pagerIndex = _pagerIndex - 1;
}
int StartRecord = (_pagerIndex) * _pagerSize + 1;
int EndRecord = StartRecord + _pagerSize - 1;
sql = "select * from " + sql + ") as t where rowId between " + StartRecord + " and " + EndRecord;
if (!string.IsNullOrEmpty(_orderString))
{
sql += " ORDER BY " + _orderString;
}
DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql, null);
return ds;
}

转载于:https://www.cnblogs.com/lucoo/archive/2013/03/05/2944866.html