/// <summary>
/// 分页查询
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public PageResult <T> QueryPage(string @where, IDictionary <string, object> paras, string order, int pageIndex,
int pageSize)
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var parasList = new List <SqlParameter>();
var parasListData = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
parasList.Add(new SqlParameter(para.Key, para.Value));
parasListData.Add(new SqlParameter(para.Key, para.Value));
}
}
ISQLContext sqlContext = new SQLContext(Session);
var entity = new T();
string sql = entity.GetQuerySql(@where);
var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray());
int start = (pageIndex - 1) * pageSize;
var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a ";
sql = string.Format(tempsql, sql);
sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn";
var reader = (DbDataReader)sqlContext.ExecuteQueryReader(sql, parasListData.ToArray());
List <T> listdata = new List <T>();
using (reader)
{
var tuple = reader.GetDeserializerState <T>();
while (reader.Read())
{
listdata.Add((T)tuple.Func(reader));
}
}
/*foreach (var data in entities)
* {
* data.ClearStack();
* }*/
return(new PageResult <T>(listdata, total));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public PageResult <T> QueryPage(string @where, IDictionary <string, object> paras, string order, int pageIndex,
int pageSize)
{
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var parasList = new List <SqlParameter>();
var parasListData = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
parasList.Add(new SqlParameter(para.Key, para.Value));
parasListData.Add(new SqlParameter(para.Key, para.Value));
}
}
ISQLContext sqlContext = new SQLContext(Session);
var entity = new T();
string sql = entity.GetQuerySQL(@where);
var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray());
int start = (pageIndex - 1) * pageSize;
var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a ";
sql = string.Format(tempsql, sql);
sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn";
var reader = sqlContext.ExecuteQueryReader(sql, parasListData.ToArray());
var entities = DataReaderHelper.ReaderToList <T>(reader);
return(new PageResult <T>(entities, total));
}
