public CategoryTree buildCategoryTree(List <string> data)
{
CategoryTree tree = new CategoryTree();
tree.root = new Category();
tree.root.Id = Guid.NewGuid();
tree.root.Name = "ROOT";
foreach (var row in data)
{
string[] nodes = row.Split('.');
CategoryTree currentTree = tree;
for (int i = 1; i < nodes.Length; i++)
{
var child = currentTree.childrent.FirstOrDefault(c => c.root.Name.Equals(nodes[i]));
if (child == null)
{
child = new CategoryTree();
child.root.Id = Guid.NewGuid();
child.root.Name = nodes[i];
child.root.ParentId = currentTree.root.Id;
currentTree.childrent.Add(child);
}
currentTree = child;
}
}
return(tree);
}
public void saveToDB(String filePath, int worksheetNumber)
{
List <string> data = readDataFromExcel(filePath, worksheetNumber);
CategoryTree tree = buildCategoryTree(data);
using (var sqlBulk = new SqlBulkCopy(_connectionString))
{
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(Guid));
dt.Columns.Add("Name");
dt.Columns.Add("ParentId", typeof(Guid));
Queue <CategoryTree> treeQueue = new Queue <CategoryTree>();
treeQueue.Enqueue(tree);
while (treeQueue.Count != 0)
{
CategoryTree tempTree = treeQueue.Dequeue();
dt.Rows.Add(tempTree.root.Id, tempTree.root.Name, tempTree.root.ParentId);
tempTree.childrent.ForEach(c => treeQueue.Enqueue(c));
}
sqlBulk.DestinationTableName = "Category";
sqlBulk.WriteToServer(dt);
}
}
