How To: Use DataRelation to perform a join on two DataTables in a DataSet?

How do I perform a join between two DataTables in a Dataset?

I created a DataRelation between two tables….then what?

I'm looking at one explanation on how to do it (http://www.emmet-gray.com/Articles/DataTableJoins.htm) which involves copying rows from tables to a result table?

Is there a better way to do this?

From stackoverflow

• DataTables support selection only on columns they own, joining many tables isn't supported.

• See if this helps

  DataTable person = new DataTable();
  person.Columns.Add("Id");
  person.Columns.Add("Name");
  
  DataTable pet = new DataTable();
  pet.Columns.Add("Id");
  pet.Columns.Add("Name");
  pet.Columns.Add("OwnerId");
  
  DataSet ds = new DataSet();
  ds.Tables.AddRange(new[] { person, pet });
  
  ds.Relations.Add("PersonPet",person.Columns["Id"], pet.Columns["OwnerId"]);
  
  DataRow p = person.NewRow();
  p["Id"] = 1;
  p["Name"] = "Peter";
  person.Rows.Add(p);
  
  p = person.NewRow();
  p["Id"] = 2;
  p["Name"] = "Alex";
  person.Rows.Add(p);
  
  p = pet.NewRow();
  p["Id"] = 1;
  p["Name"] = "Dog";
  p["OwnerId"] = 1;
  pet.Rows.Add(p);
  
  p = pet.NewRow();
  p["Id"] = 2;
  p["Name"] = "Cat";
  p["OwnerId"] = 2;
  pet.Rows.Add(p);
  
  
  foreach (DataRow personRow in person.Rows)
  {
      Console.WriteLine("{0} - {1}",personRow["Id"], personRow["Name"]);
      foreach (DataRow petRow in personRow.GetChildRows("PersonPet"))
      {
          Console.WriteLine("{0} - {1}", petRow["Id"], petRow["Name"]);
      }
  }
  

• I was finding the solution for the same problem. Well, I have given up and use SQL to do a join.

  Someone has written some custom code to achieve joining DataTable. Maybe they are helpful for you: http://www.emmet-gray.com/Articles/DataTableJoins.htm