c# - How to link parent and child ids? -

i have following table

pnlparentid  id         operator   12           13         * 12           14         * 12           15         *  20           1          - 20           2          -  13           21         / 13           20         / 

i have created foreach loop rows , add dataset.then dataset ones children...but lost of how link them ??

string dbconnection = "data source=test;initial catalog=test;integrated security=sspi;"; string mycommand = "select pnlparentid , operator [test].[dbo].[dimpnl] group pnlparentid,operator";  dataset ds = getdataset(mycommand, dbconnection); using (sqlconnection connection = new sqlconnection(dbconnection)) {     connection.open();     using (sqlcommand command = new sqlcommand(mycommand, connection))     {         dataset ds1 = new dataset();         datatable dt = new datatable();         sqldataadapter da = new sqldataadapter(command);         da.fill(ds1, "test");         dt = ds1.tables["test"];          foreach (datarow dr in dt.rows)         {             using (sqlcommand command1 = new sqlcommand("select pnlid [test].[dbo].[dimpnl]  @pnlparentid =pnlparentid , @operator=operator" , connection))             {                 command1.parameters.add(                     new sqlparameter(                         "@pnlparentid", dr["pnlparentid"].tostring()));                  command1.parameters.add(                     new sqlparameter(                         "@operator", dr["operator"].tostring()));                  dataset ds2 = new dataset();                 datatable dt1 = new datatable();                 sqldataadapter da1 = new sqldataadapter(command1);                 da1.fill(ds2, "test1");                 dt1 = ds2.tables["test1"];                  foreach (datarow dr1 in dt1.rows)                 {                     console.writeline(dr1["pnlid"].tostring());                 }             }         }  

this kind of problem best done @ object level. i.e. suppose you've got object

class node {   public int id { get; set; }   public int? parentid { get; set; }   public string operator { get; set; }   public node parent { get; set; }   public ilist<node> children { get; set; }   public node() {     children = new list<node>();   } } 

then you'll 2 iterations - 1 loading data, 1 assigning parent/child hierarchy.

  // dt1 rows columns id, parentid, operator   datatable dt1 = null;    var map = new dictionary<int, node>();   var rootnodes = new list<node>();   foreach(datarow row in dt1.rows) {     int id = convert.toint32(row["id"]);     int? parentid = null;     if (!row.isnull("parentid")) {       parentid = convert.toint32(row["parentid"]);     }     string op = convert.tostring(row["operator"]);     map[id] = new node {       id = id,       parentid = parentid,       operator = op     };   }   foreach (var pair in map) {     if (pair.value.parentid.hasvalue) {       var parent = map[pair.value.parentid.value];       pair.value.parent = parent;       parent.children.add(pair.value);     } else {       rootnodes.add(pair.value);     }   }