using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace _313_spring_2008_ADO_NET
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = new DataSet();
            DataTable dt1 = new DataTable("Students");
            DataColumn dc = new DataColumn("SID", System.Type.GetType("System.Int32"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("FirstName", System.Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("LastName", System.Type.GetType("System.String"));
            dt1.Columns.Add(dc);
            dc = new DataColumn("GPA", System.Type.GetType("System.Double"));
            dt1.Columns.Add(dc);

            string[] col_names = {"CRN", "CourseName", "CourseNumber"};
            Type[] col_types = {System.Type.GetType("System.Int32"),
                                System.Type.GetType("System.String"),
                                System.Type.GetType("System.String")};
            
            DataTable dt2 = new DataTable("Courses");
            for(int i=0; i < 3; i++)
            {
                dt2.Columns.Add(col_names[i], col_types[i]);
            }

            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            DataRow dr = ds.Tables["Students"].NewRow();
            dr["SID"] = 555123333;
            dr["FirstName"] = "Jon";
            dr["LastName"] = "Preston";
            dr["GPA"] = 3.7;
            ds.Tables["Students"].Rows.Add(dr);

            dr = ds.Tables["Students"].NewRow();
            dr["SID"] = 111779999;
            dr["FirstName"] = "Alice";
            dr["LastName"] = "Smith";
            dr["GPA"] = 4.0;
            ds.Tables["Students"].Rows.Add(dr);

            dr = ds.Tables["Courses"].NewRow();
            dr["CRN"] = 51123;
            dr["CourseName"] = "Intermediate C#";
            dr["CourseNumber"] = "ITFN2313";
            ds.Tables["Courses"].Rows.Add(dr);

            dr = ds.Tables["Courses"].NewRow();
            dr["CRN"] = 32323;
            dr["CourseName"] = "Introduction to C#";
            dr["CourseNumber"] = "ITFN1303";
            ds.Tables["Courses"].Rows.Add(dr);

            dr = ds.Tables["Courses"].NewRow();
            dr["CRN"] = 11234;
            dr["CourseName"] = "Testing and Quality Assurance";
            dr["CourseNumber"] = "ITFN3314";
            ds.Tables["Courses"].Rows.Add(dr);

            PrintTable(ds.Tables["Students"]);

            Console.WriteLine("Before remove");
            PrintTable(ds.Tables["Courses"]);
            ds.Tables["Courses"].Rows.RemoveAt(1);
            Console.WriteLine("After remove");
            PrintTable(ds.Tables["Courses"]);

            string conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb";
            OleDbConnection conn = new OleDbConnection(conn_str);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Contacts", conn);
            da.Fill(ds, "Contacts");

            conn.Close();

            PrintTable(ds.Tables["Contacts"]);
        }

        static void PrintTable(DataTable dt)
        {
            Console.WriteLine("Table: " + dt.TableName);
            Console.WriteLine("-------------------------");

            foreach (DataRow dr in dt.Rows)
            {
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    Console.Write(dr[col] + "\t");
                }
                Console.WriteLine();
            }
            Console.WriteLine();
            Console.WriteLine();
        }
    }
}