//********************************************************
//   Description: DataSet and relations between tables (DataRelation class)
//********************************************************
//
//1. From the File select New -> Project.
//2. Select template C# -> Console Application.
//3. Name project as DataSetExample3.
//4. Insert the following code.
//*******************************************************/

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

namespace DataSetExample3
{
    class Start
    {
        static void Main(string[] args)
        {
        //DataAdapter instance
        SqlDataAdapter dap = new SqlDataAdapter ("SELECT [ID], ProjectName FROM Projects", "Data Source=SERVER_NAME;Initial Catalog=DB_NAME;User Id=sa;Password=");

        //DataSet instance
        DataSet dts = new DataSet ();
        //DataSet "filling"
        dap.Fill (dts, "Projects");

        dap.SelectCommand.CommandText = "SELECT [ID], StageName, StageDescription, ProjectID FROM Stages";
        dap.Fill (dts, "Stages");

        //DataRelation definition
        DataRelation rel = dts.Relations.Add("ProjectStages", dts.Tables["Projects"].Columns["ID"], dts.Tables["Stages"].Columns["ProjectID"]);


        //display Projects table data and childs table data
        foreach (DataRow row in dts.Tables["Projects"].Rows)
        {
            Console.WriteLine (row["ProjectName"]);
            foreach (DataRow row_ in row.GetChildRows(rel))
               {
                 Console.Write(" > ");
                 Console.WriteLine(row_["StageName"]);
               }
        }

        Console.ReadLine ();
        }
    }
}