public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null)
{
var lstResult = new List <T>();
T tItem = default(T);
string tableName = AttrAssistant.GetTableName(typeof(T)); //表名
if (condition.IsNullOrEmpty())
{
condition = "1=1";
}
//起始记录
int startPosition = (pageIndex - 1) * pageSize; //比如第一页是从0开始，而不是1，和sqlserver不同
var builder = new StringBuilder();
//limit 起始行（包括）,记录数
builder.AppendFormat(@"select {0} from {1} as {6} {2} {3} limit {4},{5}",
filter, tableName,
condition.IsNullOrEmpty() ? "" : " where " + condition,
sort.IsNullOrEmpty() ? "" : " order by " + sort, startPosition, pageSize, Utils.SinGooPagerAlias);
var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters);
var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader);
while (reader.Read())
{
tItem = refBuilder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
public override IEnumerable <T> GetPagerList <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null)
{
var lstResult = new List <T>();
T tItem = default(T);
string tableName = AttrAssistant.GetTableName(typeof(T)); //表名
if (string.IsNullOrEmpty(condition))
{
condition = "1=1";
}
//起始记录(不包括)
int startPosition = (pageIndex - 1) * pageSize;
StringBuilder builder = new StringBuilder();
//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
builder.AppendFormat(@"select {0} from {1} as {6} {2} {3} limit {4} offset {5}",
filter, tableName,
condition.IsNullOrEmpty() ? "" : " where " + condition,
sort.IsNullOrEmpty() ? "" : " order by " + sort, pageSize, startPosition, Utils.SinGooPagerAlias);
var reader = GetDataReader(builder.ToString(), conditionParameters);
var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader);
while (reader.Read())
{
tItem = refBuilder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null)
{
if (sort.IsNullOrEmpty())
{
throw new ArgumentNullException("sort"); //sort参数必填
}
var lstResult = new List <T>();
T tItem = default(T);
string tableName = AttrAssistant.GetTableName(typeof(T)); //表名
if (condition.IsNullOrEmpty())
{
condition = "1=1";
}
//起始记录
int startPosition = (pageIndex - 1) * pageSize + 1;
int offsetPosition = (pageIndex - 1) * pageSize;
//截止记录
int endPosition = pageIndex * pageSize;
var builder = new StringBuilder();
if (dbVersionNo >= 11)
{
//sqlserver2012及以上可用
builder.AppendFormat(@"select {0}
from {1} as {6}
where {2}
order by {3}
offset {4} rows fetch next {5} rows only",
filter, tableName, condition, sort, offsetPosition, pageSize, Utils.SinGooPagerAlias);
}
else
{
//sqlserver2005及以上可用
builder.AppendFormat(@"select {0}
from(select row_number() over(order by {3}) as rownum,*
from {1}
where {2}
) as {6}
where rownum between {4} and {5}
order by {3}", filter, tableName, condition, sort, startPosition, endPosition, Utils.SinGooPagerAlias);
}
var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters);
var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader);
while (reader.Read())
{
tItem = refBuilder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
public virtual T GetModel <T>(IDataReader reader) where T : class
{
var model = default(T);
var builder = ReflectionBuilder <T> .CreateBuilder(reader, dbProviderType);
while (reader.Read())
{
model = builder.Build(reader, dbProviderType);
break; //只取一个
}
reader.Close();
return(model);
}
public virtual async Task <IEnumerable <T> > GetListAsync <T>(string sql, DbParameter[] parameters = null) where T : class
{
var lstResult = new List <T>();
var reader = await GetDataReaderAsync(sql, parameters);
var builder = ReflectionBuilder <T> .CreateBuilder(reader, dbProviderType);
while (reader.Read())
{
T tItem = builder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null)
{
var lstResult = new List <T>();
T tItem = default(T);
string tableName = AttrAssistant.GetTableName(typeof(T)); //表名
if (string.IsNullOrEmpty(condition))
{
condition = "1=1";
}
//起始记录
int startPosition = (pageIndex - 1) * pageSize + 1;
//截止记录
int endPosition = pageIndex * pageSize;
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"select {0} from
(
select t.*,rownum as rowno from
(
select * from {1} {2} {3}
) t where rownum<={5}
) {6} where rowno>={4}",
filter, tableName,
condition.IsNullOrEmpty() ? "" : " where " + condition,
sort.IsNullOrEmpty() ? "" : " order by " + sort, startPosition, endPosition, Utils.SinGooPagerAlias);
var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters);
var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader, dbProviderType);
while (reader.Read())
{
tItem = refBuilder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
