How To Link Parent And Child Ids?

I have the following table PNLParentId id operator 12 13 * 12 14 * 12 15 * 20 1 - 20

Solution 1:

This kind of problem is usually best done at object level. I.E. suppose you've got object

classNode {
  publicint Id { get; set; }
  publicint? ParentId { get; set; }
  publicstring Operator { get; set; }
  public Node Parent { get; set; }
  public IList<Node> Children { get; set; }
  publicNode() {
    Children = new List<Node>();
  }
}

Then you'll do two iterations - one for loading data, one for assigning parent/child hierarchy.

// dt1 is something with all rows with columns Id, ParentId, Operator together
  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);
    }
  }

Solution 2:

You can use @@SCOPE_IDENTITY() to read the last inserted identity value. Just read that after each of your parent inserts and use those when you're inserting the children.

However, I don't see you inserting anything in your code, so it might be that this isn't what you're looking for. If you're only interested in reading the ready data, well...

There's no automatic solution using just SqlCommand etc. The simple way would be to use e.g. a dictionary to hold the parent items, and add children to those based on their parent ID.

Or you could simply switch to e.g. EntityFramework, and let it do the work for you :)