Navigate a many-to-many relationship. : DataRelation « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. DataRelation

using System;
using System.Data;
using System.Data.SqlClient;

public class AddCustomer
{
  public static void Main() 
  {
    string connectionString = "Data Source=localhost;Initial Catalog=pubs;Integrated Security=SSPI";
    string SQL = "SELECT au_lname, au_fname, au_id FROM Authors";

    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(SQL, con);
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();

    con.Open();
    adapter.Fill(ds, "Authors");

    cmd.CommandText = "SELECT au_id, title_id FROM TitleAuthor";
    adapter.Fill(ds, "TitleAuthor");

    cmd.CommandText = "SELECT title_id, title FROM Titles";
    adapter.Fill(ds, "Titles");
    con.Close();

    DataRelation titles_titleAuthor = new DataRelation("", ds.Tables["Titles"].Columns["title_id"], ds.Tables["TitleAuthor"].Columns["title_id"]);

    DataRelation authors_titleAuthor = new DataRelation("", ds.Tables["Authors"].Columns["au_id"], ds.Tables["TitleAuthor"].Columns["au_id"]);

    ds.Relations.Add(titles_titleAuthor);
    ds.Relations.Add(authors_titleAuthor);

    foreach (DataRow rowAuthor in ds.Tables["Authors"].Rows)
    {
        Console.WriteLine(rowAuthor["au_fname"]);
        Console.WriteLine(rowAuthor["au_lname"]);
        foreach (DataRow rowTitleAuthor in rowAuthor.GetChildRows(authors_titleAuthor))
        {
            foreach (DataRow rowTitle in rowTitleAuthor.GetParentRows(titles_titleAuthor))
            {
                Console.WriteLine(rowTitle["title"]);
            }
        }
    }
  }
}