Nội dung kiến thức:
	Kết nối CSDL MySQL <-> C# (C Sharp)

Mini Project:
	Viết 1 chương trình quản lý sinh
	Menu chương trình:
		1. Xem danh sách sinh viên
		2. Thêm sinh viên vào CSDL
		3. Sửa thông tin sinh viên
		4. Xoá
		5. Thoát


B1. Chuẩn bị database + tables
B2. Tạo dự án + add thư viện hỗ trợ kết CSDL MySQL <-> C#
B3. Mapping Models <-> Tables (CSDL)

#Models/Students.cs

using System;
using Lesson08.Utils;

namespace Lesson08.Models
{
    public class Students
    {
        public int ID { get; set; }
        public string Fullname { get; set; }
        public string Gender { get; set; }
        public string Email { get; set; }
        public string PhoneNumber { get; set; }
        public int Age { get; set; }

        public Students()
        {
        }

        public Students(int id, string fullname, string gender, string email, string phoneNumber, int age)
        {
            ID = id;
            Fullname = fullname;
            Gender = gender;
            Email = email;
            PhoneNumber = phoneNumber;
            Age = age;
        }

        public void Input()
        {
            Console.WriteLine("Nhap ten: ");
            Fullname = Console.ReadLine();

            Console.WriteLine("Nhap gioi tinh: ");
            Gender = Console.ReadLine();

            Console.WriteLine("Nhap email: ");
            Email = Console.ReadLine();

            Console.WriteLine("Nhap SDT: ");
            PhoneNumber = Console.ReadLine();

            Console.WriteLine("Nhap tuoi: ");
            Age = Utility.ReadInt();
        }

        public void Display()
        {
            Console.WriteLine("Ten: {0}, gioi tinh: {1}, email: {2}, sdt: {3}, " +
                " tuoi: {4}", Fullname, Gender, Email, PhoneNumber, Age);
        }
    }
}

#Utils/Utility.cs

using System;
namespace Lesson08.Utils
{
    public class Utility
    {
        public static int ReadInt()
        {
            int value;
            while(true)
            {
                try
                {
                    value = int.Parse(Console.ReadLine());
                    return value;
                } catch(Exception e)
                {
                    Console.WriteLine("Nhap lai!!!");
                }
            }
        }
    }
}

#DataBase/StudentDAO.cs

using System;
using System.Collections.Generic;
using Lesson08.Models;
using MySql.Data.MySqlClient;

namespace Lesson08.DataBase
{
    public class StudentDAO
    {
        public static List<Students> GetStudentList()
        {
            List<Students> dataList = new List<Students>();

            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            //Query
            string sql = "select * from students";
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while(reader.Read())
            {
                int id = int.Parse(reader["id"].ToString());
                string fullname = reader["fullname"].ToString();
                string gender = reader["gender"].ToString();
                int age = int.Parse(reader["age"].ToString());
                string email = reader["email"].ToString();
                string phoneNumber = reader["phone_number"].ToString();

                Students std = new Students(id, fullname, gender, email, phoneNumber, age);
                dataList.Add(std);
            }

            //Close connection
            conn.Close();

            return dataList;
        }

        public static Students FindByID(int id)
        {
            Students std = null;

            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            //Query
            string sql = "select * from students where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@id", id);

            MySqlDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                string fullname = reader["fullname"].ToString();
                string gender = reader["gender"].ToString();
                int age = int.Parse(reader["age"].ToString());
                string email = reader["email"].ToString();
                string phoneNumber = reader["phone_number"].ToString();

                std = new Students(id, fullname, gender, email, phoneNumber, age);
            }

            //Close connection
            conn.Close();

            return std;
        }

        public static void Insert(Students std)
        {
            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            //Query
            string sql = "insert into students(fullname, email, age, gender, phone_number) " +
                "values(@fullname, @email, @age, @gender, @phone_number)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@fullname", std.Fullname);
            command.Parameters.AddWithValue("@email", std.Email);
            command.Parameters.AddWithValue("@age", std.Age);
            command.Parameters.AddWithValue("@gender", std.Gender);
            command.Parameters.AddWithValue("@phone_number", std.PhoneNumber);

            command.ExecuteNonQuery();
            //Close connection
            conn.Close();
        }

        public static void Update(Students std)
        {
            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            //Query
            string sql = "update students set fullname = @fullname, email = @email, age = @age, gender = @gender, phone_number = @phone_number where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@fullname", std.Fullname);
            command.Parameters.AddWithValue("@email", std.Email);
            command.Parameters.AddWithValue("@age", std.Age);
            command.Parameters.AddWithValue("@gender", std.Gender);
            command.Parameters.AddWithValue("@phone_number", std.PhoneNumber);
            command.Parameters.AddWithValue("@id", std.ID);

            command.ExecuteNonQuery();
            //Close connection
            conn.Close();
        }

        public static void Delete(int id)
        {
            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            //Query
            string sql = "delete from students where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@id", id);

            command.ExecuteNonQuery();
            //Close connection
            conn.Close();
        }
    }
}

#DataBase/Config.cs

using System;
namespace Lesson08.DataBase
{
    public class Config
    {
        static string HOST = "localhost";
        static string PORT = "3306";
        static string DATABASE = "C2010G";
        static string USERNAME = "root";
        static string PASSWORD = "";

        public static string getConnectionString()
        {
            string connString = String.Format("SERVER={0};PORT={1};DATABASE={2};UID={3};PASSWORD={4};SSL Mode=None",
                HOST, PORT, DATABASE, USERNAME, PASSWORD);
            return connString;
        }
    }
}

#Program.cs

using System;
using System.Collections.Generic;
using Lesson08.Utils;
using Lesson08.DataBase;
using Lesson08.Models;

namespace Lesson08
{
    class Program
    {
        static void Main(string[] args)
        {
            int choose;

            do
            {
                ShowMenu();
                choose = Utility.ReadInt();

                switch(choose)
                {
                    case 1:
                        Display();
                        break;
                    case 2:
                        Input();
                        break;
                    case 3:
                        Update();
                        break;
                    case 4:
                        Delete();
                        break;
                    case 5:
                        Console.WriteLine("Thoat!!!");
                        break;
                    default:
                        Console.WriteLine("Nhap sai!!!");
                        break;
                }
            } while (choose != 5);
        }

        private static void Delete()
        {
            Console.WriteLine("Nhap ID can sua thong tin: ");
            int id = Utility.ReadInt();

            Students stdFind = StudentDAO.FindByID(id);

            if (stdFind != null)
            {
                StudentDAO.Delete(id);
            }
            else
            {
                Console.WriteLine("Khong tim thay sinh vien phu hop");
            }
        }

        private static void Update()
        {
            Console.WriteLine("Nhap ID can sua thong tin: ");
            int id = Utility.ReadInt();

            Students stdFind = StudentDAO.FindByID(id);

            if(stdFind != null)
            {
                stdFind.Input();
                StudentDAO.Update(stdFind);
            } else
            {
                Console.WriteLine("Khong tim thay sinh vien phu hop");
            }
        }

        private static void Input()
        {
            Console.WriteLine("Nhap thong tin sinh vien: ");
            Students std = new Students();
            std.Input();

            StudentDAO.Insert(std);

            Console.WriteLine("Them thanh cong!!!");
        }

        private static void Display()
        {
            List<Students> studentList = StudentDAO.GetStudentList();

            Console.WriteLine("====== Danh sach sinh vien");
            foreach(Students std in studentList)
            {
                std.Display();
            }
        }

        static void ShowMenu()
        {
            Console.WriteLine("1. Hien thi danh sach sinh vien");
            Console.WriteLine("2. Them sinh vien");
            Console.WriteLine("3. Sua thong tin sinh vien");
            Console.WriteLine("4. Xoa thong tin sinh vien");
            Console.WriteLine("5. Thoat");
            Console.WriteLine("Chon: ");
        }
    }
}