RelationTest.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DatabaseProject
{
  public class RelationTest
  {
    public void Test() { 
      string connectionString = "server=localhost;DataBase=CourseDatabase;uid=sa;pwd=godoro";
      SqlConnection connection = new SqlConnection(connectionString);
      connection.Open();

      DataSet dataSet = new DataSet();

      string sqlStudent = "select * from Student";
      SqlCommand commandStudent = new SqlCommand(sqlStudent, connection);
      SqlDataAdapter adapterStudent = new SqlDataAdapter();
      adapterStudent.SelectCommand = commandStudent;
      adapterStudent.Fill(dataSet, "Student");

      string sqlSchool = "select * from School";
      SqlCommand commandSchool = new SqlCommand(sqlSchool, connection);
      SqlDataAdapter adapterSchool = new SqlDataAdapter();
      adapterSchool.SelectCommand = commandSchool;
      adapterSchool.Fill(dataSet, "School");

      DataColumn studentSchoolIdColumn = dataSet.Tables["Student"].Columns["SchoolId"];
      DataColumn schoolSchoolIdColumn = dataSet.Tables["School"].Columns["SchoolId"];
      DataRelation relation = new DataRelation("SchoolStudentRelation", schoolSchoolIdColumn, studentSchoolIdColumn);
      dataSet.Relations.Add(relation);

      connection.Close();

      Console.WriteLine("Row Count : " + dataSet.Tables["School"].Rows.Count);
      foreach (DataRow schoolRow in dataSet.Tables["School"].Rows) {
        string schoolName=(string)schoolRow["SchoolName"];
        Console.WriteLine("Students of the School " + schoolName);
        foreach (DataRow studentRow in schoolRow.GetChildRows("SchoolStudentRelation")) {
          int studentId = (int)studentRow["StudentId"];
          string firstName = (string)studentRow["FirstName"];
          string lastName = (string)studentRow["LastName"];
          Console.WriteLine(studentId + " , " + firstName + "," + lastName);
        }
      }


    }
  }
}