using System;

using System.Data;

using System.Data.DataSetExtensions;

using System.Text;

using System.Linq;

using System.Data.Linq;

using System.Data.SqlClient;

​

public class Program

{

    DataTable Students=new DataTable();

    DataTable Courses =new DataTable();

    DataTable Professors =new DataTable();

    DataTable Enrollment=new DataTable();

    SqlConnection connection;

    string connectionString="Server=SQLSERVERNAME;Initial catalog=Students;Integrated Security=true;";

    public void Main()

    {

        //SqlDependency.Start(connectionString, "DefaultQueue");

        FillDataTables();

        //Initialize();

        //printTable(Students);

        printTable(Initialize());       

    }

    void Initialization()

    {

    // Create a dependency connection.

        connection=new SqlConnection(connectionString);

    }

​

    void SomeMethod()

    {

    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.

    using (SqlCommand command=new SqlCommand(

        "Update Query for students (move from one course to other)",

        connection))

    {

​

        // Create a dependency and associate it with the SqlCommand.

        SqlDependency dependency=new SqlDependency(command);

        // Maintain the reference in a class member.

​

        // Subscribe to the SqlDependency event.

        dependency.OnChange+=new

           OnChangeEventHandler(OnDependencyChange);

​

        // Execute the command.

        using (SqlDataReader reader = command.ExecuteReader())

        {

            // Process the DataReader.

        }

    }

    }

​

​

    // Handler method

    void OnDependencyChange(object sender,

    SqlNotificationEventArgs e )

    {

        // Handle the event (for example, invalidate this cache entry).

    }

​

    void Termination()

    {

    // Release the dependency.

    SqlDependency.Stop(connectionString, "DefaultQueue");

    }

    DataTable Initialize(){

        DataTable resultTbl=new DataTable();

        string sList=string.Empty;

        string cList=string.Empty;

        string profName=string.Empty;

        resultTbl.Clear();

        resultTbl.Columns.Add("ProfessorName",typeof(string));

        resultTbl.Columns.Add("Students List",typeof(string));

        resultTbl.Columns.Add("Course List",typeof(string));

        foreach(var row in Professors.AsEnumerable().GroupBy(x => x.Field<string>("ProfessorName")))

        {

            profName=row.First().Field<string>("ProfessorName");

            foreach(var prof in Professors.AsEnumerable().Where(x => x.Field<string>("ProfessorName")==profName))

            {

                //Console.WriteLine(prof.Field<int>("CourseId"));

                cList += Courses.AsEnumerable().Where(r => r.Field<int>("CourseId") == prof.Field<int>("CourseId")).FirstOrDefault()["CourseName"] + ",";

                foreach(var enrollData in Enrollment.AsEnumerable().Where(e => e.Field<int>("CourseId")==prof.Field<int>("CourseId")))

                {

                    sList += Students.AsEnumerable().Where(s => s.Field<int>("StudentId") == enrollData.Field<int>("StudentId")).Single()["StudentName"] + ",";

                }

            }

            //Console.WriteLine(profName + " | " + cList + " | " + sList);

            resultTbl.Rows.Add(new object[]{profName,sList,cList});

            cList=string.Empty;

            sList=string.Empty;

        }

        return resultTbl;

    }

    void FillDataTables()

    {

        string[] Sname= {"A","B","C","D","E","F","G","H","I","J"}; 

        string[] Cname= {"English","Mathematics","Physics","Chemistry","Political Science","History","Computer Administration","Geography"};

        DataTable dt = new DataTable();

        //Fill Students data

        dt.Clear();

        dt.Columns.Add("StudentId",typeof(int)).AllowDBNull = false;

        dt.Columns.Add("StudentName", typeof(string));

        for(int i=1;i<=10;i++){

            dt.Rows.Add(new object[] { i,Sname[i-1] });

        }

        Students=dt;

        //Fill Course data

        //dt.Clear();

        dt=new DataTable();

        dt.Columns.Add("CourseId",typeof(int)).AllowDBNull = false;

        dt.Columns.Add("CourseName", typeof(string));

        int j=1;

        for(int i=101;i<=108;i++){

            dt.Rows.Add(new object[] { i,Cname[j-1] });

            j++;

        }

        Courses=dt;

        //Fill Professors data

        //dt.Clear();

        dt=new DataTable();

        dt.Columns.Add("ProfessorName", typeof(string));

        dt.Columns.Add("CourseId",typeof(int));

        dt.Rows.Add(new object[] { "Anil",101});

        dt.Rows.Add(new object[] { "Anil",105});

        dt.Rows.Add(new object[] { "Anil",106});

        dt.Rows.Add(new object[] { "Suresh",102});

        dt.Rows.Add(new object[] { "Suresh",103});

        dt.Rows.Add(new object[] { "Mohan",104});

        dt.Rows.Add(new object[] { "Mohan",107});

        dt.Rows.Add(new object[] { "Abhay",108});

        Professors=dt;

        //Fill Enrollment Data

        dt=new DataTable();

        dt.Columns.Add("StudentId",typeof(int));

        dt.Columns.Add("CourseId",typeof(int));

        dt.Rows.Add(new object[] { 1,101 });

        dt.Rows.Add(new object[] { 2,101 });

        dt.Rows.Add(new object[] { 2,104 });

        dt.Rows.Add(new object[] { 3,103 });

        dt.Rows.Add(new object[] { 3,101 });

        dt.Rows.Add(new object[] { 3,107 });

        dt.Rows.Add(new object[] { 4,105 });

        dt.Rows.Add(new object[] { 4,106 });

        dt.Rows.Add(new object[] { 4,102 });

        dt.Rows.Add(new object[] { 5,103 });

        dt.Rows.Add(new object[] { 5,107 });

        dt.Rows.Add(new object[] { 6,108 });

        dt.Rows.Add(new object[] { 7,108 });

        dt.Rows.Add(new object[] { 7,103 });

        dt.Rows.Add(new object[] { 7,105 });

        dt.Rows.Add(new object[] { 8,104 });

        dt.Rows.Add(new object[] { 9,104 });

        dt.Rows.Add(new object[] { 9,101 });

        dt.Rows.Add(new object[] { 10,102 });

        dt.Rows.Add(new object[] { 10,106 });

        Enrollment=dt;

    }

    void printTable(DataTable table)

    {

        string data = string.Empty;

            StringBuilder sb = new StringBuilder();

​

            if (null != table && null != table.Rows)

            {

                foreach(DataColumn dataColumn in table.Columns)

                {

                    sb.Append(dataColumn.ColumnName);

                    sb.Append(" | ");

                }

                sb.Remove(sb.ToString().Length -1,1);

                sb.AppendLine();

                sb.Append("----------------------------------------");

                sb.AppendLine();

                foreach (DataRow dataRow in table.Rows)

                {

                    foreach (var item in dataRow.ItemArray)

                    {

                        sb.Append(item);

                        sb.Append(" | ");

                    }

                    sb.Remove(sb.ToString().Length -2,1);

                    sb.AppendLine();

                }

​

                data = sb.ToString();

            }

        Console.WriteLine(data);

    }

    }

​