public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Max(column, null)); cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); DataTable result = cte.ExecuteSelect(conn); return result.Rows[0][0]; }
public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Count(null)); cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); DataTable result = cte.ExecuteSelect(conn); int count = Convert.ToInt32(result.Rows[0][0], CultureInfo.InvariantCulture); return count; }
public int UpdateTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); // update target set ... where exists ... and ... RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Update = new UpdateStatement(rootEntity.Table); cte.Update.UpdateList = setExpressions; cte.Update.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); int rowsAffected = cte.ExecuteUpdate(conn); return rowsAffected; }
public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation) { EnsureRecursiveRelation(recursiveRelation); // select max(level) as maxLevelIndex from hierarchy RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(cte.Hierarchy, AggregateFunctionFactory.Max(cte.Hierarchy.Level, null)); DataTable results = cte.ExecuteSelect(conn); int highestLevel = (results.Rows[0][0] != DBNull.Value) ? Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture) : 0; int depth = highestLevel + 1; return depth; }
public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); // select target.* from target where exists ... and ... RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(rootEntity.Table, rootEntity.Table.Columns); cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); if (sortOnDb && !OrderByClause.IsNullOrEmpty(sorter)) cte.Select.OrderBy = sorter; DataTable data = cte.ExecuteSelect(conn); if (!sortOnDb && !OrderByClause.IsNullOrEmpty(sorter)) data = GenericHierarchicalQueryExecutor.SortDataTable(data, sorter); return data; }
private static SearchCondition CreateWhere(IEntity rootEntity, RecursiveStatement cte, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { // where exists (select hierarchy.id from hierarchy where hierarchy.id = target.id and level betwen beginAtLevel and endAtLevel) // and status = ... SelectStatement existsInHierarchy = new SelectStatement(cte.Hierarchy, cte.GetHierarchyPK()); foreach (IDbColumn targetPkField in rootEntity.Table.PrimaryKey) { IDbColumn hierarchyPkField = cte.Hierarchy.Columns.GetByColumnName(targetPkField.ColumnName); existsInHierarchy.Where.Add(PredicateFactory.Compare(hierarchyPkField, "=", targetPkField)); } bool hasLevelConstraints = (beginAtLevel > 0) || (endAtLevel < int.MaxValue); if (hasLevelConstraints) existsInHierarchy.Where.Add(PredicateFactory.Between(cte.Hierarchy.Level, beginAtLevel, endAtLevel)); SearchCondition where = (SearchCondition)PredicateFactory.Exists(existsInHierarchy); if (leafFilter != null && !leafFilter.IsEmpty) where.Add(leafFilter); return where; }