public HierarchyHandler()
{
var appleStructure = DbContext;
if (appleStructure != null)
{
_dataSource = appleStructure.Hierarchies;
}
_searchPattern = new NullableHierarchy();
Fields = new NullableHierarchy();
}
public List <NullableHierarchy> GetChild()
{
List <NullableHierarchy> child = null;
var connectionString = GetConnectionString();
if (connectionString != null && Id.HasValue)
{
using (var getChildConnection = new SqlConnection(connectionString))
{
var entityIdObject = (object)Id.Value.ToString(CultureInfo.InvariantCulture);
var getChildCommand = getChildConnection.CreateCommand();
getChildCommand.CommandText = string.Format(@"
WITH HierarchyCTE (Id, Name, Parent, LEVEL)
AS (SELECT HRoot.Id, HRoot.Name, HRoot.Parent, 1
FROM Hierarchy HRoot
WHERE HRoot.Id = {0}
UNION ALL
SELECT HChild.Id, HChild.Name, HChild.Parent, CTE.LEVEL + 1
FROM Hierarchy HChild
INNER JOIN HierarchyCTE CTE ON HChild.Parent = CTE.Id
WHERE HChild.Parent IS NOT NULL)
SELECT DISTINCT HierarchyCTE.Id AS {1} , HierarchyCTE.Name AS {2}, HierarchyCTE.Parent AS {3}, LEVEL
FROM HierarchyCTE
LEFT JOIN [Content] C ON HierarchyCTE.Id = C.Hierarchy
EXCEPT
SELECT HRoot.Id, HRoot.Name, HRoot.Parent, 1
FROM Hierarchy HRoot
WHERE HRoot.Id = {0}
ORDER BY LEVEL DESC
", NodeParameterName, IdColumn, NameColumn, ParentColumn);
var nodeId = new SqlParameter(NodeParameterName, entityIdObject);
getChildCommand.Parameters.Add(nodeId);
getChildConnection.Open();
var reader = getChildCommand.ExecuteReader();
while (reader.Read())
{
var entity = new NullableHierarchy(reader);
if (child == null)
{
child = new List <NullableHierarchy>();
}
child.Add(entity);
}
}
}
return(child);
}
public NullableHierarchy Set()
{
var fileds = Fields;
Hierarchy record = null;
if (fileds != null && DataSource != null)
{
if (fileds.Id != null)
{
record = DataSource.First(x => x.Id == fileds.Id.Value);
}
}
var perform = false;
if (record != null)
{
if (fileds.Parent.HasValue)
{
record.Parent = fileds.Parent.Value;
}
record.Name = fileds.Name;
perform = true;
}
var isSuccess = false;
if (perform && DataSource.Context != null)
{
DataSource.Context.SubmitChanges();
isSuccess = true;
}
NullableHierarchy result = null;
if (isSuccess)
{
result = new NullableHierarchy
{
Id = record.Id,
Parent = record.Parent,
Name = record.Name
};
}
return(result);
}
public static List <NullableHierarchy> GetAllHierarchies()
{
List <NullableHierarchy> records = null;
var connectionString = GetConnectionString();
if (!string.IsNullOrEmpty(connectionString))
{
using (var connection = new SqlConnection(connectionString))
{
var getHierarchyStructure = connection.CreateCommand();
getHierarchyStructure.CommandText = string.Format(@"
WITH HierarchyCTE (Id, Name, Parent, LEVEL)
AS (SELECT HRoot.Id, HRoot.Name, HRoot.Parent, 0
FROM Hierarchy HRoot
WHERE HRoot.Parent IS NULL
UNION ALL
SELECT HChild.Id, HChild.Name, HChild.Parent, CTE.LEVEL + 1
FROM Hierarchy HChild
INNER JOIN HierarchyCTE CTE ON HChild.Parent = CTE.Id
WHERE HChild.Parent IS NOT NULL)
SELECT HierarchyCTE.Id AS {0}, HierarchyCTE.Parent AS {1} , HierarchyCTE.Name AS {2}
FROM HierarchyCTE
ORDER BY HierarchyCTE.LEVEL
", IdColumn, ParentColumn, NameColumn);
connection.Open();
var reader = getHierarchyStructure.ExecuteReader();
while (reader.Read())
{
var entity = new NullableHierarchy
{
Id = (long?)(reader[IdColumn] == DBNull.Value ? null : reader[IdColumn]),
Name = (string)reader[NameColumn],
Parent = (long?)(reader[ParentColumn] == DBNull.Value ? null : reader[ParentColumn])
};
if (records == null)
{
records = new List <NullableHierarchy>();
}
records.Add(entity);
}
}
}
return(records);
}
public List <NullableHierarchy> Get()
{
IQueryable <Hierarchy> records = DataSource;
var pattern = SearchPattern;
if (records != null &&
pattern != null)
{
var parent = pattern.Parent;
if (parent.HasValue)
{
records = records.Where(x => x.Parent == parent.Value);
}
var id = pattern.Id;
if (id.HasValue)
{
records = records.Where(x => x.Id == id.Value);
}
var name = pattern.Name;
if (name != null)
{
records = records.Where(x => x.Name.Contains(name));
}
}
List <NullableHierarchy> result = null;
if (records != null)
{
foreach (var record in records)
{
var resultRecord = new NullableHierarchy(record);
if (result == null)
{
result = new List <NullableHierarchy>();
}
result.Add(resultRecord);
}
}
return(result);
}
public NullableHierarchy Add()
{
Hierarchy record = null;
var dataSource = DataSource;
var fields = Fields;
if (dataSource != null && fields != null)
{
record = new Hierarchy();
}
if (record != null)
{
record.Name = fields.Name;
}
if (record != null && fields.Id != null)
{
record.Id = fields.Id.Value;
}
if (fields != null)
{
if (fields.Parent != null && record != null)
{
record.Parent = fields.Parent.Value;
}
}
if (record != null && dataSource.Context != null)
{
dataSource.InsertOnSubmit(record);
dataSource.Context.SubmitChanges();
}
var result = new NullableHierarchy(record);
return(result);
}
