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); }
/// <summary> /// 分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableNameFormat">Table Name Format placeholder</param> /// <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>(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()); 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]) : "ID"; } 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); }