In C#, how should I create a csv from parent-child table?

My parent-child table looks like this:

ID, ParentID, Name, Population
1, Null, Asia, 40
2, Null, Africa, 20
3, 1, China, 10
4, 3, Tibet, 5

(around 1000 rows and the levels of the tree can vary. User will keep entering new rows)

I want to achieve a csv file that looks like this:

Level1, Level2, Level3, Population
Asia, Null, Null, 40
Africa, Null, Null, 20
Asia, China, Null, 10
Asia, China, Tibet, 5

How do I create a csv file like this from parent-child table? First challange is to find max number of levels of parent-child table and then create csv headers. Second challange is to then put null for levels that do not have value.

Please note that users will keep entering new lines... not sure if I should load the parent-child table in xml format and then start reading xml fi开发者_开发百科le... please suggest some way. Thank you

It looks like you want to construct a tree from your table and then do a traversal of your tree to construct your csv file. Your tree nodes will contain two values: country name and population.

While constructing your tree you probably want to make note of your maximum leaf node depth. This will give you the number of elements in your header row in your csv. Then during traversal you'll want to keep track of the path of nodes from your root, this will be what you print in your csv, the number of nodes in this path can be subtracted from your maximum depth to give you the number of nulls in a given line.

Your Node class will look something like this:

class Node
{
    string name;
    int population;

    Node parent;
    List<Node> children;

    public Node(Node parent, string name, int population)
    {
        this.parent = parent;
        this.name = name;
        this.population = population;
        children = new List<Node>();
    }


    public void Add(Node child)
    {
        children.Add(child);
    }
}

create table #Populations (ID int PRIMARY KEY, ParentId int null, Name varchar(20), Population int )
insert into #Populations values(1, null, 'Asia', 40)
insert into #Populations values(2, Null, 'Africa', 20)
insert into #Populations values(3, 1, 'China', 20)
insert into #Populations values(4, 3, 'Tibet', 10)

;with FirstLevel(ID, ParentId, Name, Population, Breadcrumb, Level, LevelStr) AS
(
    select ID, ParentId, Name, Population
        , convert(varchar(max),Name) as [Breadcrumb]
        , Level = 1
        , convert(varchar(max),'Level1')
    from #Populations
    where ParentId is null
    union all 
    select p.ID, p.ParentId, p.Name, p.Population
        ,convert(varchar(max),[Breadcrumb] + ', ' + p.Name)
        ,Level + 1, convert(varchar(max)
        ,LevelStr + ', Level' + convert(varchar(2), Level + 1))
    From #Populations p
    inner join FirstLevel on FirstLevel.ID = p.ParentId
)
select top 1 LevelStr + ', Population' as CsvRow
from FirstLevel
where Level = (select max(level) from FirstLevel)
union all
select Breadcrumb + ',' + coalesce(replicate(',',(select max(level) from FirstLevel) - Level - 1), '') + ' ' + convert(varchar(20), Population)
from FirstLevel

drop table #Populations