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;
}
