private void LoadProductTypesRelations() { if (ProductTypesRelations == null) { lock (_myLocker) { if (ProductTypesRelations == null) { ProductTypesRelations = new Dictionary<ProductType, ProductTypeValue> (); string cmdText = @" WITH PTHierarchy ([id], [parent_id], [name], [level]) AS ( SELECT PT.id, PT.parent_id, PT.name, 0 AS level FROM [ProductType] AS PT WHERE PT.parent_id IS NULL UNION ALL SELECT PT.id, PT.parent_id, PT.name, level + 1 AS lecel FROM [ProductType] AS PT INNER JOIN PTHierarchy AS PTH ON PTH.id = PT.parent_id ) SELECT [id], [parent_id], [name] FROM PTHierarchy ORDER BY level, parent_id ASC"; Action<SqlDataReader> fetcher = reader => { ProductTypeOridinal pto = new ProductTypeOridinal (reader); pto.LoadOrdinals (); while (reader.Read ()) { ProductTypeValue ptv = pto.GetProductTypeValue (); ProductTypesRelations[ptv.ProductType] = ptv; } }; ExecuteQuery (cmdText, null, fetcher); } } } }
private IEnumerable<ProductTypeNode> LoadProductTypesHierarchy() { string cmdText = @" SELECT * FROM ProductType AS PT ORDER BY PT.[parent_id]"; List<ProductTypeNode> nodes = new List<ProductTypeNode> (); Action<SqlDataReader> fetcher = reader => { ProductTypeOridinal pto = new ProductTypeOridinal (reader); pto.LoadOrdinals (); while (reader.Read ()) { ProductTypeValue ptv = pto.GetProductTypeValue (); ProductTypeNode node = new ProductTypeNode (); node.ProductTypeValue = ptv; nodes.Add (node); } }; ExecuteQuery (cmdText, null, fetcher); var lookup = nodes.ToLookup(ptv => ptv.ProductTypeValue.ParentType); nodes.Clear (); var parents = lookup[(ProductType?)null]; nodes.AddRange (parents); foreach (var item in lookup) { if (!item.Key.HasValue) continue; ProductTypeNode parent = nodes.FirstOrDefault (ptn => ptn.ProductTypeValue.ProductType == item.Key.Value); if (parent == null) continue; parent.AddRange (lookup[item.Key]); } return nodes; }