public override void QueryList(IDbAccesser dba, ISqlSelectArgs args)
{
var pagingInfo = args.PagingInfo;
if (PagingInfo.IsNullOrEmpty(pagingInfo) || this.GetPagingLocation(pagingInfo) == PagingLocation.Memory)
{
base.QueryList(dba, args);
}
else
{
//转换为分页查询 SQL
var parts = ParsePagingSqlParts(args.FormattedSql);
CreatePagingSql(ref parts, pagingInfo);
//读取分页的实体
using (var reader = dba.QueryDataReader(parts.PagingSql, args.Parameters))
{
this.FillDataIntoList(
reader, ReadDataType.ByName,
args.List, args.FetchingFirst, PagingInfo.Empty, args.MarkTreeFullLoaded
);
}
QueryTotalCountIf(dba, pagingInfo, parts, args.Parameters);
}
}
/// <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 override void QueryTable(IDbAccesser dba, ITableQueryArgs args)
{
var pagingInfo = args.PagingInfo;
if (PagingInfo.IsNullOrEmpty(pagingInfo) || this.GetPagingLocation(pagingInfo) == PagingLocation.Memory)
{
base.QueryTable(dba, args);
}
else
{
//转换为分页查询 SQL
var parts = ParsePagingSqlParts(args.FormattedSql);
CreatePagingSql(ref parts, pagingInfo);
//读取分页的数据
var table = args.ResultTable;
var reader = dba.QueryDataReader(parts.PagingSql, args.Parameters);
using (reader)
{
LiteDataTableAdapter.Fill(table, reader);
}
QueryTotalCountIf(dba, pagingInfo, parts, args.Parameters);
}
}
/// <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 entityReader = CreateEntityReader(readType);
Action<IDataReader> rowReader = dr =>
{
var entity = entityReader.Read(dr);
list.Add(entity);
};
if (fetchingFirst)
{
if (reader.Read())
{
rowReader(reader);
}
}
else
{
PagingHelper.MemoryPaging(reader, rowReader, pagingInfo);
}
}
/// <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);
}
/// <summary>
/// 只有一些特定的查询，可以进行分批查询。
/// </summary>
/// <param name="dba">The dba.</param>
/// <param name="args">The arguments.</param>
/// <returns></returns>
private bool TryBatchQuery(IDbAccesser dba, IEntitySelectArgs args)
{
if (!PagingInfo.IsNullOrEmpty(args.PagingInfo))
{
return(false);
}
//分批查询的条件：WHERE 条件中只有 IN 或 NOT IN 语句。
var query = args.Query;
var inClause = query.Where as IColumnConstraint;
if (inClause == null || inClause.Operator != PropertyOperator.In && inClause.Operator != PropertyOperator.NotIn)
{
return(false);
}
var values = inClause.Value as IEnumerable;
var parameters = values as IList ?? values.Cast <object>().ToArray();
var autoSelection = AutoSelectionForLOB(query);
var readType = autoSelection ? ReadDataType.ByIndex : ReadDataType.ByName;
/*********************** 代码块解释 *********************************
* 以下分批进行查询。算法：
* 先临时把树中的条件中的值改成子集合，
* 然后使用新的树生成对应的 Sql 语句并查询实体。
* 所有查询完成后，再把树中的集合还原为原始的大集合。
**********************************************************************/
var maxItemsCount = SqlGenerator.CampatibleMaxItemsInInClause;
var start = 0;
var paramSection = new List <object>(maxItemsCount);
inClause.Value = paramSection;//临时把树中的条件中的值改成子集合。
while (start < parameters.Count)
{
paramSection.Clear();
var end = Math.Min(start + maxItemsCount - 1, parameters.Count - 1);
for (int i = start; i <= end; i++)
{
paramSection.Add(parameters[i]);
}
//生成 Sql
var generator = this.CreateSqlGenerator();
QueryFactory.Instance.Generate(generator, query);
var sql = generator.Sql;
base.QueryDataReader(dba, args, readType, sql);
start += paramSection.Count;
}
return(true);
}
/// <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);
}
/// <summary>
/// 子类重写此方法，查询从持久层加载列表的具体实现。
/// </summary>
/// <param name="args">The arguments.</param>
/// <param name="entityList">The entity list.</param>
protected virtual void QueryListCore(SqlQueryArgs args, EntityList entityList)
{
var dataProvider = RdbDataProvider.Get(Repo);
using (var dba = dataProvider.CreateDbAccesser())
{
//访问数据库
if (args.Filter != null)
{
#region 内存过滤式加载
if (!PagingInfo.IsNullOrEmpty(args.PagingInfo))
{
throw new NotSupportedException("使用内存过滤器的同时，不支持提供分页参数。");
}
args.EntityType = Repo.EntityType;
args.MemoryList = new List <Entity>();
dataProvider.DbTable.QueryList(dba, args);
this.LoadByFilter(args);
#endregion
}
else
{
if (args.FetchType == FetchType.Count)
{
#region 查询 Count
var value = dba.QueryValue(args.FormattedSql, args.Parameters);
var count = RdbTable.ConvertCount(value);
entityList.SetTotalCount(count);
#endregion
}
else
{
//是否需要为 PagingInfo 设置统计值。
var pagingInfoCount = !PagingInfo.IsNullOrEmpty(args.PagingInfo) && args.PagingInfo.IsNeedCount;
//如果 pagingInfoCount 为真，则在访问数据库时，会设置好 PagingInfo 的总行数。
args.EntityType = Repo.EntityType;
dataProvider.DbTable.QueryList(dba, args);
//最后，还需要设置列表的 TotalCount。
if (pagingInfoCount)
{
entityList.SetTotalCount(args.PagingInfo.TotalCount);
}
}
}
}
}
/// <summary>
/// 子类重写此方法，查询从持久层加载列表的具体实现。
/// </summary>
/// <param name="args">The arguments.</param>
/// <param name="entityList">The entity list.</param>
/// <exception cref="System.NotSupportedException">使用内存过滤器的同时，不支持提供分页参数。</exception>
protected override void QueryListCore(EntityQueryArgs args, EntityList entityList)
{
var dp = RdbDataProvider.Get(this.Repo);
using (var dba = dp.CreateDbAccesser())
{
//以下代码，开始访问数据库查询数据。
var dbTable = dp.DbTable;
if (args.Filter != null)
{
#region 内存过滤式加载
if (!PagingInfo.IsNullOrEmpty(args.PagingInfo))
{
throw new NotSupportedException("使用内存过滤器的同时，不支持提供分页参数。");
}
args.MemoryList = new List <Entity>();
dbTable.QueryList(dba, args);
this.LoadByFilter(args);
#endregion
}
else
{
if (args.FetchType == FetchType.Count)
{
#region 查询 Count
var count = dbTable.Count(dba, args.Query);
entityList.SetTotalCount(count);
#endregion
}
else
{
//是否需要为 PagingInfo 设置统计值。
var pi = args.PagingInfo;
var pagingInfoCount = !PagingInfo.IsNullOrEmpty(pi) && pi.IsNeedCount;
//如果 pagingInfoCount 为真，则在访问数据库时，会设置好 PagingInfo 的总行数。
dbTable.QueryList(dba, args);
//最后，还需要设置列表的 TotalCount。
if (pagingInfoCount)
{
entityList.SetTotalCount(pi.TotalCount);
}
}
}
}
}
public override void QueryList(IDbAccesser dba, IEntitySelectArgs args)
{
/*********************** 代码块解释 *********************************
*
* 以下代码用于支持数据库分页
*
**********************************************************************/
//检查分页条件。（如果是树状实体，也不支持在数据库中进行分页。）
var pagingInfo = args.PagingInfo;
bool isPaging = !PagingInfo.IsNullOrEmpty(pagingInfo) &&
this.GetPagingLocation(pagingInfo) == PagingLocation.Database &&
!Repository.SupportTree;
if (isPaging)
{
var query = args.Query;
if (!(query as TableQuery).HasOrdered())
{
throw new InvalidProgramException("分页查询的同时，必须指定排序属性。");
}
var autoSelection = AutoSelectionForLOB(query);
//生成分页 Sql
var pk = query.From.FindTable(Repository).Column(Entity.IdProperty);
var generator = this.CreateSqlGenerator();
var pagedSelect = generator.ModifyToPagingTree(query as SqlSelect, pk as SqlColumn, pagingInfo);
generator.Generate(pagedSelect);
var pagingSql = generator.Sql;
//查询数据库
using (var reader = dba.QueryDataReader(pagingSql, pagingSql.Parameters))
{
//填充到列表中。
this.FillDataIntoList(
reader, autoSelection ? ReadDataType.ByIndex : ReadDataType.ByName,
args.List, false, pagingInfo, args.MarkTreeFullLoaded
);
}
//最后，如果需要，则统计一下总行数。
if (pagingInfo.IsNeedCount)
{
pagingInfo.TotalCount = this.Count(dba, query);
}
}
else
{
base.QueryList(dba, args);
}
}
protected sealed override 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 override void QueryList(IDbAccesser dba, IEntitySelectArgs args)
{
/*********************** 代码块解释 *********************************
*
* 以下代码用于支持数据库分页
*
**********************************************************************/
//检查分页条件。（如果是树状实体，也不支持在数据库中进行分页。）
var pagingInfo = args.PagingInfo;
bool isPaging = !PagingInfo.IsNullOrEmpty(pagingInfo) &&
this.GetPagingLocation(pagingInfo) == PagingLocation.Database &&
!Repository.SupportTree;
if (isPaging)
{
var query = args.Query;
var autoSelection = AutoSelectionForLOB(query);
//生成分页 Sql
var generator = this.CreateSqlGenerator();
generator.Generate(query as SqlSelect, pagingInfo);
var pagingSql = generator.Sql;
//查询数据库
using (var reader = dba.QueryDataReader(pagingSql, pagingSql.Parameters))
{
//填充到列表中。
this.FillDataIntoList(
reader, autoSelection ? ReadDataType.ByIndex : ReadDataType.ByName,
args.List, false, pagingInfo, args.MarkTreeFullLoaded
);
}
//最后，如果需要，则统计一下总行数。
if (pagingInfo.IsNeedCount)
{
pagingInfo.TotalCount = this.Count(dba, query);
}
}
else
{
base.QueryList(dba, args);
}
}
/// <summary>
/// 使用 ROWNUM 来进行分页。
/// </summary>
/// <param name="raw">The raw.</param>
/// <param name="pagingInfo">The paging information.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">pagingInfo</exception>
/// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception>
protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var res = MakePagingTree(raw, startRow, endRow);
return(res);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
var pageNumber = pagingInfo.PageNumber;
var pageSize = pagingInfo.PageSize;
var res = MakePagingTree(raw, pageNumber, pageSize);
return(res);
}
/// <summary>
/// 使用 IDataReader 的内存分页读取方案。
/// </summary>
/// <param name="reader"></param>
/// <param name="rowReader">每一行数据，会调用此方法进行调取。</param>
/// <param name="pagingInfo">分页信息。如果这个参数不为空，则使用其中描述的分页规则进行内存分页查询。</param>
public static void MemoryPaging(IDataReader reader, Action <IDataReader> rowReader, PagingInfo pagingInfo)
{
bool isPaging = !PagingInfo.IsNullOrEmpty(pagingInfo);
bool needCount = isPaging && pagingInfo.IsNeedCount;
int totalCount = 0;
int startRow = 1;//从一开始的行号
int endRow = int.MaxValue;
if (isPaging)
{
startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
endRow = startRow + pagingInfo.PageSize - 1;
}
while (reader.Read())
{
totalCount++;
if (totalCount >= startRow)
{
if (totalCount <= endRow)
{
rowReader(reader);
}
else
{
//如果已经超出该页，而且需要统计行数，则直接快速循环到最后。
if (needCount)
{
while (reader.Read())
{
totalCount++;
}
break;
}
}
}
}
if (needCount)
{
pagingInfo.TotalCount = totalCount;
}
}
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;
}
}
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
private ISqlSelect ModifyToPagingTreeWithNotIn(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
return(new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy
});
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
//先要找到主表的 PK，分页时需要使用此主键列来生成分页 Sql。
//这里约定 Id 为主键列名。
var finder = new FirstTableFinder();
var pkTable = finder.Find(raw.From);
var pkColumn = new SqlColumn {
Table = pkTable, ColumnName = EntityConvention.IdColumnName
};
//先生成内部的 Select
var excludeSelect = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
},
pkColumn
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
protected ISqlSelect ModifyToPagingTreeWithRowNumberOver(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
return(new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy
});
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT * FROM
* (
* SELECT A.*, ROW_NUMBER() OVER (order by Id)_rowNumber
* FROM A
* ) T
* WHERE _rowNumber between 1 and 10
**********************************************************************/
var newRaw = new SqlSelect
{
Selection = new SqlNodeList()
{
new SqlLiteral {
FormattedSql = "ROW_NUMBER() OVER ("
},
raw.OrderBy,
new SqlLiteral {
FormattedSql = ")_rowNumber,"
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
Where = raw.Where,
IsDistinct = raw.IsDistinct,
IsCounting = raw.IsCounting,
};
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var res = MakePagingTree(newRaw, startRow, endRow);
return(res);
}
/// <summary>
/// 通过 IQuery 对象来查询实体。
/// </summary>
/// <param name="args">The arguments.</param>
/// <returns></returns>
/// <exception cref="System.NotSupportedException">使用内存过滤器的同时，不支持提供分页参数。</exception>
/// <exception cref="System.InvalidProgramException"></exception>
protected EntityList QueryList(EntityQueryArgs args)
{
if (args.Query == null)
{
throw new ArgumentException("EntityQueryArgs.Query 属性不能为空。");
}
this.PrepareArgs(args);
this.BuildDefaultQuerying(args);
this.OnQuerying(args);
var entityList = args.EntityList;
var oldCount = entityList.Count;
bool autoIndexEnabled = entityList.AutoTreeIndexEnabled;
try
{
//在加载数据时，自动索引功能都不可用。
entityList.AutoTreeIndexEnabled = false;
using (var dba = Repo.RdbDataProvider.CreateDbAccesser())
{
//以下代码，开始访问数据库查询数据。
var dbTable = Repo.RdbDataProvider.DbTable;
if (args.Filter != null)
{
#region 内存过滤式加载
if (!PagingInfo.IsNullOrEmpty(args.PagingInfo))
{
throw new NotSupportedException("使用内存过滤器的同时，不支持提供分页参数。");
}
args.MemoryList = new List <Entity>();
dbTable.QueryList(dba, args);
this.LoadByFilter(args);
#endregion
}
else
{
if (args.FetchType == FetchType.Count)
{
#region 查询 Count
var count = dbTable.Count(dba, args.Query);
entityList.SetTotalCount(count);
#endregion
}
else
{
//是否需要为 PagingInfo 设置统计值。
var pi = args.PagingInfo;
var pagingInfoCount = !PagingInfo.IsNullOrEmpty(pi) && pi.IsNeedCount;
//如果 pagingInfoCount 为真，则在访问数据库时，会设置好 PagingInfo 的总行数。
dbTable.QueryList(dba, args);
//最后，还需要设置列表的 TotalCount。
if (pagingInfoCount)
{
entityList.SetTotalCount(pi.TotalCount);
}
}
}
}
}
finally
{
entityList.AutoTreeIndexEnabled = autoIndexEnabled;
}
this.EagerLoadOnCompleted(args, entityList, oldCount);
return(entityList);
}
/// <summary>
/// 使用 sql 语句来查询实体。
/// </summary>
/// <param name="args">The arguments.</param>
/// <returns></returns>
/// <exception cref="System.NotSupportedException">使用内存过滤器的同时，不支持提供分页参数。</exception>
protected EntityList QueryList(SqlQueryArgs args)
{
this.PrepareArgs(args);
var entityList = args.EntityList;
var oldCount = entityList.Count;
bool autoIndexEnabled = entityList.AutoTreeIndexEnabled;
try
{
//在加载数据时，自动索引功能都不可用。
entityList.AutoTreeIndexEnabled = false;
var dataProvider = Repo.RdbDataProvider;
using (var dba = dataProvider.CreateDbAccesser())
{
//访问数据库
if (args.Filter != null)
{
#region 内存过滤式加载
if (!PagingInfo.IsNullOrEmpty(args.PagingInfo))
{
throw new NotSupportedException("使用内存过滤器的同时，不支持提供分页参数。");
}
args.EntityType = Repo.EntityType;
args.MemoryList = new List <Entity>();
dataProvider.DbTable.QueryList(dba, args);
this.LoadByFilter(args);
#endregion
}
else
{
if (args.FetchType == FetchType.Count)
{
#region 查询 Count
var value = dba.QueryValue(args.FormattedSql, args.Parameters);
var count = DbTable.ConvertCount(value);
entityList.SetTotalCount(count);
#endregion
}
else
{
//是否需要为 PagingInfo 设置统计值。
var pagingInfoCount = !PagingInfo.IsNullOrEmpty(args.PagingInfo) && args.PagingInfo.IsNeedCount;
//如果 pagingInfoCount 为真，则在访问数据库时，会设置好 PagingInfo 的总行数。
args.EntityType = Repo.EntityType;
dataProvider.DbTable.QueryList(dba, args);
//最后，还需要设置列表的 TotalCount。
if (pagingInfoCount)
{
entityList.SetTotalCount(args.PagingInfo.TotalCount);
}
}
}
}
}
finally
{
entityList.AutoTreeIndexEnabled = autoIndexEnabled;
}
this.EagerLoadOnCompleted(args, entityList, oldCount);
return(entityList);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pkColumn">需要指定主键列</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
raw.Top = pagingInfo.PageSize;
return(raw);
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
var excludeSelect = new SqlSelect
{
Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize,
Selection = pkColumn,
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Top = pagingInfo.PageSize,
Selection = raw.Selection,
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
/// <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);
}