/// <summary>
/// 在 sqlce 下，不支持 rowNumber 方案，但是支持 not in 方案。
/// 鉴于实现 not in 方案比较耗时，所以暂时决定使用 IDataReader 分页完成。
///
/// not in 分页，参见以下 Sql：
/// select top 10 [AuditItem].* from
/// [AuditItem] where
/// [AuditItem].id not in
/// (
/// select top 100 [AuditItem].id from [AuditItem] order by LogTime desc
/// )
/// order by LogTime desc
/// </summary>
protected override PagingLocation GetPagingLocation(PagingInfo pagingInfo)
{
if (!PagingInfo.IsNullOrEmpty(pagingInfo) && pagingInfo.PageNumber == 1 && !pagingInfo.IsNeedCount)
{
return PagingLocation.Database;
}
return PagingLocation.Memory;
}
protected override sealed EntityList DoGetAll(PagingInfo paging, EagerLoadOptions eagerLoad)
{
if (!PagingInfo.IsNullOrEmpty(paging)) { throw new NotSupportedException(); }
DataProvider.EnsureStore();
var items = DataProvider._memoryRows.Values.Select(v => DataProvider.FromRow(v));
var list = this.CreateList(items, false);
TreeHelper.MarkTreeFullLoaded(list);
return list;
}
public new ChapterList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as ChapterList;
}
public new OperationACList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as OperationACList;
}
/// <summary>
/// 访问 sql 语法树中的每一个结点，并生成相应的 Sql 语句。
/// </summary>
/// <param name="tree">The tree.</param>
/// <param name="pagingInfo">The paging information.</param>
public void Generate(SqlSelect tree, PagingInfo pagingInfo = null)
{
ISqlSelect res = tree;
if (!PagingInfo.IsNullOrEmpty(pagingInfo))
{
res = ModifyToPagingTree(tree, pagingInfo);
}
base.Visit(res);
}
public new ProductAttachementList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as ProductAttachementList;
}
public new MonthPlanList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as MonthPlanList;
}
public new DbMigrationHistoryList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as DbMigrationHistoryList;
}
/// <summary> /// 判断指定的分页操作，支持在哪个层面进行分页。 /// </summary> /// <param name="pagingInfo">The paging information.</param> /// <returns></returns> protected abstract PagingLocation GetPagingLocation(PagingInfo pagingInfo);
/// <summary>
/// 在内存中对 IDataReader 进行读取。
/// 注意！！！
/// 此方法中会释放 Reader。外层不能再用 Using。
/// </summary>
/// <param name="reader">表格类数据。</param>
/// <param name="readType">是否索引还是名称去读取 IDataReader。</param>
/// <param name="list">需要把读取的实体，加入到这个列表中。</param>
/// <param name="fetchingFirst">是否只读取一条数据即返回。</param>
/// <param name="pagingInfo">如果不是只取一行数据，则这个参数表示列表内存分页的信息。</param>
/// <param name="markTreeFullLoaded">如果某次查询结果是一棵完整的子树，那么必须设置此参数为 true ，才可以把整个树标记为完整加载。</param>
protected void FillDataIntoList(
IDataReader reader, ReadDataType readType,
IList<Entity> list, bool fetchingFirst, PagingInfo pagingInfo, bool markTreeFullLoaded
)
{
if (_repository.SupportTree)
{
this.FillTreeIntoList(reader, readType, list, markTreeFullLoaded, pagingInfo);
return;
}
//如果正在分页，而且支持数据库层面的分页，则不使用内存分页。
if (!PagingInfo.IsNullOrEmpty(pagingInfo) && this.GetPagingLocation(pagingInfo) == PagingLocation.Database)
{
pagingInfo = null;
}
var readByIndex = readType == ReadDataType.ByIndex;
Action<IDataReader> rowReader = dr =>
{
var entity = readByIndex ? this.CreateByIndex(dr) : this.CreateByName(dr);
list.Add(entity);
};
if (fetchingFirst)
{
if (reader.Read())
{
rowReader(reader);
}
}
else
{
PagingHelper.MemoryPaging(reader, rowReader, pagingInfo);
}
}
/// <summary>
/// 如果需要统计，则生成统计语句进行查询。
/// </summary>
/// <param name="dba"></param>
/// <param name="pagingInfo"></param>
/// <param name="parts"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private static void QueryTotalCountIf(IDbAccesser dba, PagingInfo pagingInfo, PagingSqlParts parts, object[] parameters)
{
if (pagingInfo.IsNeedCount)
{
var pagingCountSql = "SELECT COUNT(0) " + parts.FromWhere;
//查询值。（由于所有参数都不会在 OrderBy、Select 语句中，所以把所有参数都传入。
var value = dba.QueryValue(pagingCountSql, parameters);
pagingInfo.TotalCount = Convert.ToInt32(value);
}
}
/// <summary>
/// SqlServer 、 Oracle 都支持在数据库层面进行分页。
/// </summary>
protected override PagingLocation GetPagingLocation(PagingInfo pagingInfo)
{
//虽然本类默认使用数据库分页，但是它的子类可以重写本方法来使用内存分页。
//所以本类中的所有方法，在重新实现时，都会分辨这两种情况。
return PagingLocation.Database;
}
protected virtual void CreatePagingSql(ref PagingSqlParts parts, PagingInfo pagingInfo)
{
/*********************** 代码块解释 *********************************
*
* 注意，这个方法只支持不太复杂 SQL 的转换。
*
* 源格式：
* select ...... from ...... order by xxxx asc, yyyy desc
* 不限于以上格式，只要满足没有复杂的嵌套查询，最外层是一个 Select 和 From 语句即可。
*
* 目标格式：
* select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
**********************************************************************/
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var sql = new StringBuilder("SELECT * FROM (");
//在 Select 和 From 之间插入：
//,row_number() over(order by UPDDATETIME desc) rn
sql.AppendLine().Append(parts.Select)
.Append(", row_number() over(")
.Append(parts.OrderBy);
//query.AppendSqlOrder(res, this);
sql.Append(") dataRowNumber ").Append(parts.FromWhere)
.Append(") x").AppendLine()
.Append("WHERE x.dataRowNumber >= ").Append(startRow)
.Append(" AND x.dataRowNumber <= ").Append(endRow);
parts.PagingSql = sql.ToString();
}
public new OtherStorageInBillList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentIdList(parentIdList, paging, eagerLoad) as OtherStorageInBillList;
}
/// <summary>
/// 在内存中对 IDataReader 进行读取，并以树的方式进行节点的加载。
/// </summary>
/// <param name="reader">表格类数据。</param>
/// <param name="readType">是否索引还是名称去读取 IDataReader。</param>
/// <param name="list">需要把读取的实体中的第一级的节点，加入到这个列表中。</param>
/// <param name="markTreeFullLoaded">如果某次查询结果是一棵完整的子树，那么必须设置此参数为 true ，才可以把整个树标记为完整加载。</param>
/// <param name="pagingInfo">对根节点进行分页的信息。</param>
private void FillTreeIntoList(
IDataReader reader, ReadDataType readType, IList<Entity> list,
bool markTreeFullLoaded, PagingInfo pagingInfo)
{
var entities = this.ReadToEntity(reader, readType);
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
TreeHelper.LoadTreeData(list, entities, _repository.TreeIndexOption);
}
else
{
//在内存中分页。
var tempList =new List<Entity>();
TreeHelper.LoadTreeData(tempList, entities, _repository.TreeIndexOption);
var paged = tempList.JumpToPage(pagingInfo);
foreach (var item in paged) { list.Add(item); }
}
if (markTreeFullLoaded)
{
TreeHelper.MarkTreeFullLoaded(list);
}
}
public new BookCategoryList GetByParentId(object parentId, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentId(parentId, paging, eagerLoad) as BookCategoryList;
}
public new DevLanguageItemList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as DevLanguageItemList;
}
public new DbMigrationHistoryList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentIdList(parentIdList, paging, eagerLoad) as DbMigrationHistoryList;
}
public new DevLanguageItemList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentIdList(parentIdList, paging, eagerLoad) as DevLanguageItemList;
}
public new MonthPlanList GetByParentId(object parentId, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentId(parentId, paging, eagerLoad) as MonthPlanList;
}
public new OrgPositionOperationDenyList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as OrgPositionOperationDenyList;
}
public new ProductAttachementList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentIdList(parentIdList, paging, eagerLoad) as ProductAttachementList;
}
public new OrgPositionOperationDenyList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentIdList(parentIdList, paging, eagerLoad) as OrgPositionOperationDenyList;
}
internal void SetDataLoadOptions(PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
if (!PagingInfo.IsNullOrEmpty(paging))
{
this.PagingInfo = paging;
}
if (eagerLoad != null && eagerLoad.CoreList.Count > 0)
{
if (this.EagerLoadOptions != null)
{
for (int i = 0, c = eagerLoad.CoreList.Count; i < c; i++)
{
var item = eagerLoad.CoreList[i];
this.EagerLoad(item);
}
}
else
{
this.EagerLoadOptions = eagerLoad;
}
}
}
public new TestRoleList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as TestRoleList;
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">pagingInfo</exception>
/// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception>
protected virtual ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); }
if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); }
/*********************** 代码块解释 *********************************
*
* 使用 ROW_NUMBER() 函数，此函数 SqlServer、Oracle 都可使用。
* 注意，这个方法只支持不太复杂 SQL 的转换。
*
* 源格式：
* select ...... from ...... order by xxxx asc, yyyy desc
* 不限于以上格式，只要满足没有复杂的嵌套查询，最外层是一个 Select 和 From 语句即可。
*
* 目标格式：
* select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
**********************************************************************/
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var innerSelect = new SqlSelect();
var selection = new SqlArray();
if (raw.Selection != null)
{
selection.Items.Add(raw.Selection);
}
selection.Items.Add(new SqlNodeList
{
new SqlLiteral { FormattedSql = "row_number() over (" },
raw.OrderBy,
new SqlLiteral { FormattedSql = ") _rowNumber" }
});
innerSelect.Selection = selection;
var subSelect = new SqlSubSelect
{
Select = innerSelect,
Alias = "x"
};
var rowNumberColumn = new SqlTree.SqlColumn
{
Table = subSelect,
ColumnName = "_rowNumber"
};
var pagingSelect = new SqlSelect();
pagingSelect.From = subSelect;
pagingSelect.Where = new SqlTree.SqlBinaryConstraint
{
Left = new SqlTree.SqlColumnConstraint
{
Column = rowNumberColumn,
Operator = SqlColumnConstraintOperator.GreaterEqual,
Value = startRow
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlTree.SqlColumnConstraint
{
Column = rowNumberColumn,
Operator = SqlColumnConstraintOperator.LessEqual,
Value = endRow
}
};
return pagingSelect;
}
public new PBSTypeList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as PBSTypeList;
}
public new OperationACList GetByParentId(object parentId, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentId(parentId, paging, eagerLoad) as OperationACList;
}
public new BookCategoryList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
{
return base.GetAll(paging, eagerLoad) as BookCategoryList;
}
public new ChapterList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
{
return base.GetByParentIdList(parentIdList, paging, eagerLoad) as ChapterList;
}
/// <summary>
/// Indicates is this pagingInfo a nonsence.
/// </summary>
/// <returns></returns>
public static bool IsNullOrEmpty(PagingInfo pagingInfo)
{
return(pagingInfo == null || pagingInfo is EmptyPagingInfo);
}
