using System;
using System.Collections.Generic;
using System.Data.Odbc;

namespace mysql_example
{
    class Program
    {
        // globals

        public static List<person> people = new List<person> { };
        public static OdbcConnection db   = new OdbcConnection();

        public static String conString =
            "DRIVER={MySQL ODBC 5.1 Driver};" +
            "SERVER=localhost;"  +
            "DATABASE=csharpdb;" +
            "USER=csharpuser;"   +
            "PASSWORD=password;";

        // main function

        static void Main(string[] args)
        {
            // update and show users

            refreshPeople();
            listPeople();

            // promote Lister and kill Kryten

            Console.WriteLine("Promoting Lister and taking Kryten out of service...");

            db = new OdbcConnection(conString);
            db.Open();

            String sql = @"UPDATE people SET jobTitle=""Chef"" WHERE name=""Lister""";
            OdbcCommand update = new OdbcCommand(sql, db);
            update.ExecuteNonQuery();

            sql = @"DELETE FROM people WHERE name=""Kryten""";
            update = new OdbcCommand(sql, db);
            update.ExecuteNonQuery();

            db.Close();

            // update and show users

            refreshPeople();
            listPeople();

            Console.Read();
        }

        public static void refreshPeople()
        {
            people.Clear();

            // connect to database

            db = new OdbcConnection(conString);
            db.Open();

            // create and execute query

            String         sql          = "SELECT id, name, employed, jobtitle, age FROM people";
            OdbcCommand    query        = new OdbcCommand(sql, db);
            OdbcDataReader queryResults = query.ExecuteReader();

            while (queryResults.Read())
            {
                person newPerson = new person();

                try { newPerson.id = queryResults.GetInt16(0); }
                catch (InvalidCastException) { }

                try { newPerson.name = queryResults.GetString(1); }
                catch (InvalidCastException) { }

                int isEmployed = 0;
                isEmployed = queryResults.GetInt16(2);

                if (isEmployed == 1) newPerson.employed = true;
                else newPerson.employed = false;

                try { newPerson.jobTitle = queryResults.GetString(3); }
                catch (InvalidCastException) { }

                try { newPerson.age = queryResults.GetInt16(4); }
                catch (InvalidCastException) { }

                people.Add(newPerson);
            }

            // close connection

            db.Close();
        }

        public static void listPeople()
        {
            Console.WriteLine("\nJMC Personnel\n");
            Console.WriteLine("==========================================\n");

            foreach (person p in people)
                p.getDetails();

            Console.WriteLine();
        }
    }

    public class person
    {
        // class to match the SQL person table

        public int    id       { get; set; }
        public String name     { get; set; }
        public bool   employed { get; set; }
        public String jobTitle { get; set; }
        public int    age      { get; set; }

        public person() { }

        public String empStatus()
        {
            return this.employed ? "employed" : "not employed";
        }

        public void getDetails()
        {
            Console.WriteLine(String.Format("{0,-10} {1,-4} {2,-15} {3,-15}", 
                name, age, jobTitle, empStatus()));
        }
    }
}