public string GetPager(string sql, PagerParameter parameter)
{
string pagerSql = "SELECT * from(select DENSE_RANK() OVER (ORDER by " + parameter.OrderBy + (parameter.Desc ? " desc" : "") + " ) as num,z.* from (" + sql + ")z )t where t.num>";
pagerSql += (parameter.PageIndex - 1) * parameter.PageSize + " and t.num<=" + (parameter.PageIndex) * parameter.PageSize;
return(pagerSql);
}
public PagerResultSet <T> GetModelList <T>(string sql, object pars, PagerParameter pager, string Connection = null)
{
setBaseSql <T>();
extend(Connection);
DapperSqlHelper dsh = new DapperSqlHelper();
string sqlPager = dsh.GetPager(sql, pager);
string sqlCount = dsh.GetCount(sql, pager);
int count = _dbConnection.ExecuteScalar <int>(sqlCount, pars);
List <T> rs = new List <T>(_dbConnection.Query <T>(sqlPager, pars));
PagerResultSet <T> prs = new PagerResultSet <T>(rs, count, pager);
return(prs);
}
public void SetDefaultValue(PagerParameter pageParameter)
{
if (pageParameter.PageIndex == 0)
{
pageParameter.PageIndex = 1;
}
if (pageParameter.PageSize == 0)
{
pageParameter.PageSize = 15;
}
if (string.IsNullOrEmpty(pageParameter.OrderBy))
{
pageParameter.OrderBy = "ID";
}
}
public PagerResultSet <T> GetModelList <T>(string sql, object pars, PagerParameter pager, Func <System.Data.IDataReader, T> Transformation, string Connection = null)
{
extend(Connection);
DapperSqlHelper dsh = new DapperSqlHelper();
string sqlPager = dsh.GetPager(sql, pager);
string sqlCount = dsh.GetCount(sql, pager);
int count = _dbConnection.ExecuteScalar <int>(sqlCount, pars);
var reader = _dbConnection.ExecuteReader(sql, pars);
List <T> list = new List <T>();
while (reader.Read())
{
list.Add(Transformation(reader));
}
reader.Close();
PagerResultSet <T> prs = new PagerResultSet <T>(list, count, pager);
return(prs);
}
public PagerInfor(int total, PagerParameter pagerParameter)
{
Total = total;
PagerParameter = pagerParameter;
PageCount = (int)Math.Ceiling(((double)Total / pagerParameter.PageSize));
HasNext = PageCount > PagerParameter.PageIndex;
HasPrevious = PagerParameter.PageIndex > 1;
StartRow = PagerParameter.PageSize * (PagerParameter.PageIndex - 1);
EndRow = PagerParameter.PageIndex * PagerParameter.PageSize;
if (EndRow > Total)
{
EndRow = Total;
}
bool canShowPreviousPageIndexs = PageCount - PagerParameter.PageIndex > ViewPageCount / 2;
bool canShowNextPageIndexs = PagerParameter.PageIndex > ViewPageCount / 2;
if (canShowNextPageIndexs && canShowPreviousPageIndexs)
{
ViewStartPageIndex = (PagerParameter.PageIndex) - ViewPageCount / 2;
ViewEndPageIndex = (PagerParameter.PageIndex) + ViewPageCount / 2;
}
else if (canShowPreviousPageIndexs)
{
ViewStartPageIndex = 1;
ViewEndPageIndex = ViewPageCount > PageCount ? PageCount : ViewPageCount;
}
else if (canShowNextPageIndexs)
{
ViewEndPageIndex = PageCount;
ViewStartPageIndex = PageCount - ViewPageCount > 1 ? PageCount - ViewPageCount + 1 : 1;
}
else
{
ViewStartPageIndex = 1;
ViewEndPageIndex = PageCount;
}
}
public PagerResultSet <T1> GetModelList <T1, T2>(string sql, object obj, PagerParameter page, string Connection = null)
{
extend(Connection);
ManyToOneConfigs configs = new ManyToOneConfigs(_cache);
var manyToOne = configs.GetConfig <T1, T2>();
Dictionary <object, T1> dicts = new Dictionary <object, T1>();
var list = GetModelList <T1, T2, T1>(sql, obj, manyToOne.ManyName, page, (x, y) =>
{
T1 to;
object key = manyToOne.GetKeyFunc(x);
if (!dicts.TryGetValue(key, out to))
{
dicts.Add(key, to = x);
}
List <T2> data = manyToOne.GetListFunc(dicts[key]);
if (y != null)
{
data.Add(y);
}
return(to);
}, Connection);
return(new PagerResultSet <T1>(new List <T1>(dicts.Values), list.Pager));
}
public PagerResultSet <T3> GetModelList <T1, T2, T3>(string sql, object pars, string splitOn, PagerParameter pager, Func <T1, T2, T3> map, string Connection = null)
{
extend(Connection);
DapperSqlHelper dsh = new DapperSqlHelper();
string sqlPager = dsh.GetPager(sql, pager);
string sqlCount = dsh.GetCount(sql, pager);
int count = _dbConnection.ExecuteScalar <int>(sqlCount, pars);
List <T3> rs = new List <T3>(_dbConnection.Query <T1, T2, T3>(sqlPager, map, pars, null, false, splitOn));
PagerResultSet <T3> prs = new PagerResultSet <T3>(rs, count, pager);
return(prs);
}
public PagerResultSet(IList <T> list, int total, PagerParameter pagerParameter)
{
this.Rows = new List <T>(list);
this.Pager = new PagerInfor(total, pagerParameter);
}
public string GetCount(string sql, PagerParameter parameter)
{
string countSql = " select count(*) from (select " + parameter.OrderBy + " from (" + sql + ")as t1 group by " + parameter.OrderBy + ") as t2";
return(countSql);
}
