public ISqlBuilder GetPageList <T>(string tableNameFormat, IRepository <T> repository, int pageNum = 0, int pageSize = 0, string whereString = null, object param = null, object order = null, bool asc = false)
where T : class, IEntity
{
SqlBuilder sqlBuilder = new SqlBuilder();
var sbColumnList = new StringBuilder(null);
var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T));
for (var i = 0; i < allProperties.Count; i++)
{
var property = allProperties[i];
AppendColumnName(sbColumnList, SqlMapperExtensions.GetColumnName(property), property.Name);
if (i < allProperties.Count - 1)
{
sbColumnList.Append(", ");
}
}
sqlBuilder.Select(args: sbColumnList.ToString());
sqlBuilder.From(repository.TableName);
if (!string.IsNullOrEmpty(whereString))
{
sqlBuilder.Where(whereString, param);
}
if (order != null)
{
sqlBuilder.OrderBy(order);
sqlBuilder.IsAse(asc);
}
if (pageNum >= 0 && pageSize > 0)
{
int numMin = (pageNum - 1) * pageSize;
sqlBuilder.Append($" limit {numMin},{pageSize}");
}
return(sqlBuilder);
}
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="repository"></param>
/// <param name="pageNum">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="whereString">where语句，不需要携带where</param>
/// <param name="param">where 参数</param>
/// <param name="order"></param>
/// <param name="asc"></param>
/// <returns></returns>
public ISqlBuilder GetPageList <T>(IRepository <T> repository, int pageNum = 0, int pageSize = 0, string whereString = null, object param = null, object order = null, bool asc = false)
where T : class, IEntity
{
SqlBuilder sqlBuilder = new SqlBuilder();
var sbColumnList = new StringBuilder(null);
var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T));
for (var i = 0; i < allProperties.Count; i++)
{
var property = allProperties[i];
AppendColumnName(sbColumnList, SqlMapperExtensions.GetColumnName(property), property.Name);
if (i < allProperties.Count - 1)
{
sbColumnList.Append(", ");
}
}
sqlBuilder.Select(args: sbColumnList.ToString());
if (pageSize > 0)
{
SqlBuilder sqlBuilderRows = new SqlBuilder();
string ascSql = " asc";
if (!asc)
{
ascSql = " desc";
}
string orderSql = "";
if (order == null)
{
var key = SqlMapperExtensions.GetSingleKey <T>(nameof(GetPageList));
orderSql = SqlMapperExtensions.GetColumnName(key);
}
else
{
orderSql = SqlBuilder.GetArgsString("ORDER BY", args: order);
}
if (repository.CurrentDBSetting.DBType == DBType.SqlServer2012)
{
sqlBuilder.From(repository.TableName);
if (!string.IsNullOrEmpty(whereString))
{
sqlBuilder.Where(whereString, param);
}
sqlBuilder.OrderBy(orderSql);
int numMin = (pageNum - 1) * pageSize;
sqlBuilder.Append($"offset {numMin} rows fetch next {pageSize} rows only");
}
else
{
sqlBuilderRows.Select(args: "SELECT ROW_NUMBER() OVER(ORDER BY " + orderSql + " " + ascSql + ") AS row_id,*");
sqlBuilderRows.From(repository.TableName);
if (!string.IsNullOrEmpty(whereString))
{
sqlBuilderRows.Where(whereString, param);
}
sqlBuilder.Append($"From ({sqlBuilderRows.SQL}) as t", sqlBuilderRows.Arguments);
if (pageNum <= 0)
{
pageNum = 1;
}
int numMin = (pageNum - 1) * pageSize + 1,
numMax = pageNum * pageSize;
sqlBuilder.Where("t.row_id>=@numMin and t.row_id<=@numMax", new { numMin, numMax });
}
}
else
{
sqlBuilder.From(repository.TableName);
if (!string.IsNullOrEmpty(whereString))
{
sqlBuilder.Where(whereString, param);
}
if (order != null)
{
sqlBuilder.OrderBy(order);
sqlBuilder.IsAse(asc);
}
}
return(sqlBuilder);
}
public ISqlBuilder GetPageList <T>(string tableNameFormat, IRepository <T> repository, int pageNum = 0, int pageSize = 0, string whereString = null, object param = null, object order = null, bool asc = false)
where T : class, IEntity
{
SqlBuilder sqlBuilder = new SqlBuilder();
var sbColumnList = new StringBuilder(null);
var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T));
var tableName = SqlMapperExtensions.GetTableName(repository.TableName, tableNameFormat);
for (var i = 0; i < allProperties.Count; i++)
{
var property = allProperties[i];
AppendColumnName(sbColumnList, SqlMapperExtensions.GetColumnName(property), property.Name);
if (i < allProperties.Count - 1)
{
sbColumnList.Append(", ");
}
}
sqlBuilder.Select(args: sbColumnList.ToString());
if (pageSize > 0)
{
SqlBuilder sqlBuilderRows = new SqlBuilder();
string ascSql = " asc";
if (!asc)
{
ascSql = " desc";
}
string orderSql = "";
if (order == null)
{
var type = typeof(T);
var keys = SqlMapperExtensions.KeyPropertiesCache(type);
orderSql = keys.Count > 0 ? SqlMapperExtensions.GetColumnName(keys[0]) : string.Empty;
if (string.IsNullOrEmpty(orderSql))
{
throw new Exception($"Need set table poco key [{tableName}]");
}
}
else
{
orderSql = SqlBuilder.GetArgsString("ORDER BY", args: order);
}
sqlBuilderRows.Select(args: $"SELECT ROW_NUMBER() OVER(ORDER BY {orderSql} {ascSql} ) AS row_id,it.*");
sqlBuilderRows.From($"{tableName} it");
if (!string.IsNullOrEmpty(whereString))
{
sqlBuilderRows.Where(whereString, param);
}
sqlBuilder.Append($"From ({sqlBuilderRows.SQL}) TT", sqlBuilderRows.Arguments);
if (pageNum <= 0)
{
pageNum = 1;
}
int numMin = (pageNum - 1) * pageSize + 1,
numMax = pageNum * pageSize;
sqlBuilder.Where("TT.row_id between :numMin and :numMax", new { numMin, numMax });
}
else
{
sqlBuilder.From(tableName);
if (!string.IsNullOrEmpty(whereString))
{
sqlBuilder.Where(whereString, param);
}
if (order != null)
{
sqlBuilder.OrderBy(order);
sqlBuilder.IsAse(asc);
}
}
return(sqlBuilder);
}
