sql 分页查询

/// <summary>
/// 获取分页数据（使用临时表）：该方法在处理分页查询的时候使用了临时表技术，可以实现查询效率和页码大小无关，也就是说查询首页和查询尾页效率是相同的。
/// 主要针对大数量下的大页码（尾页）的查询；但是小页码查询效率会有所降低。建议页码比较多的情况下应用此方法
/// </summary>
/// <param name="SqlMapID"></param>
/// <param name="WebParas"></param>
/// <param name="size">每页数据条数</param>
/// <param name="pageNumber">指定页码</param>
/// <returns></returns>
public static Tuple<int, IEnumerable<T>> SqlMapGetPageList(string SqlMapID, Dictionary<string, string> WebParas, int size = 30, int pageNumber = 1, String ordeBySql = null)
{

Tuple<int, IEnumerable<T>> returnValue = default(Tuple<int, IEnumerable<T>>);
try
{
using (IDbConnection _connection = new DataAccess().GetOpenConnection())
{
Dictionary<string, string> tempParas = new Dictionary<string, string>(WebParas);
List<string> SqlParaList = new List<string>();
string sqlStr = ReadSQLXml.CreatSqlParaSql(SqlMapID, tempParas, ref SqlParaList);
var parameters = new DynamicParameters();
foreach (var item in SqlParaList)
{
parameters.Add(item, tempParas[item]);
}

if (!String.IsNullOrWhiteSpace(sqlStr))
{

String paramPageSizeName = "@pageSize" + new Random().Next(1000, 9999);
String paramPageIndexName = "@pageIndex" + new Random().Next(1000, 9999);

String tmpTableName = "#tmpPageTable_" + Math.Abs(Guid.NewGuid().GetHashCode()).ToString() + new Random().Next(1000, 9999);
String numberRowName = "RowNum_" + Math.Abs(Guid.NewGuid().GetHashCode()).ToString() + new Random().Next(1000, 9999);

sqlStr = String.Format(@"
SELECT ROW_NUMBER() OVER({1}) AS {5}, * INTO {4}
FROM ({0}) tmp_page_table;
select count(1) from {4} ;
select * FROM {4} WHERE {5}> {2}*({3}-1) AND {5}<={3}*{2} {1};
DROP TABLE {4} ;
", sqlStr,
String.IsNullOrWhiteSpace(ordeBySql) ? "" : "ORDER BY " + ordeBySql,
paramPageSizeName,
paramPageIndexName,
tmpTableName, numberRowName);
parameters.Add(paramPageSizeName, size);
parameters.Add(paramPageIndexName, pageNumber);

using (var multi = _connection.QueryMultiple(sqlStr, parameters))
{
returnValue = new Tuple<int, IEnumerable<T>>(multi.Read<int>().First(), multi.Read<T>().ToList());
}
}

return returnValue;
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 获取分页数据(没使用临时表)：该方法没有使用临时表。小页码（首页）查询效率会较高，但是大页码（尾页）查询会比小页码查询效率低。
/// 主要针对小页码查询，建议页码比较少的情况下应用此方法
/// </summary>
/// <param name="SqlMapID"></param>
/// <param name="WebParas"></param>
/// <param name="size">每页数据条数</param>
/// <param name="pageNumber">指定页码</param>
/// <returns></returns>
public static Tuple<int, IEnumerable<T>> SqlMapGetPageList_notemp(string SqlMapID, Dictionary<string, string> WebParas, int size = 30, int pageNumber = 1, String ordeBySql = null)
{

Tuple<int, IEnumerable<T>> returnValue = default(Tuple<int, IEnumerable<T>>);
try
{
using (IDbConnection _connection = new DataAccess().GetOpenConnection())
{
Dictionary<string, string> tempParas = new Dictionary<string, string>(WebParas);
List<string> SqlParaList = new List<string>();
string sqlStr = ReadSQLXml.CreatSqlParaSql(SqlMapID, tempParas, ref SqlParaList);
var parameters = new DynamicParameters();
foreach (var item in SqlParaList)
{
parameters.Add(item, tempParas[item]);
}

if (!String.IsNullOrWhiteSpace(sqlStr))
{

String paramPageSizeName = "@pageSize" + new Random().Next(1000, 9999);
String paramPageIndexName = "@pageIndex" + new Random().Next(1000, 9999);

String tmpTableName = "#tmpPageTable_" + Math.Abs(Guid.NewGuid().GetHashCode()).ToString() + new Random().Next(1000, 9999);
String numberRowName = "RowNum_" + Math.Abs(Guid.NewGuid().GetHashCode()).ToString() + new Random().Next(1000, 9999);

sqlStr = String.Format(@"
select count(1) from ({0}) as a;
select * from (
SELECT ROW_NUMBER() OVER({1}) AS {4}, *
FROM ({0}) as a
) as b
WHERE {4}> {2}*({3}-1) AND {4}<={3}*{2}
;
", sqlStr,
String.IsNullOrWhiteSpace(ordeBySql) ? "" : "ORDER BY " + ordeBySql,
paramPageSizeName,
paramPageIndexName,
numberRowName);
parameters.Add(paramPageSizeName, size);
parameters.Add(paramPageIndexName, pageNumber);

using (var multi = _connection.QueryMultiple(sqlStr, parameters))
{
returnValue = new Tuple<int, IEnumerable<T>>(multi.Read<int>().First(), multi.Read<T>().ToList());
}
}

return returnValue;
}
}
catch (Exception e)
{
throw e;
}
}