Link Video Bài Giảng

Viết chương trình quản lý khách hàng (CSDL <-> C#)
- CRUD:
	Lấy danh sách khách hàng
	Thêm
	Sửa
	Xoá

Các bước triển khai 1 dự án có kết nối CSDL:
1. Chuẩn bị database (tables)
2. Tạo project
3. Add thu vien ket noi CSDL <-> C#
4. DAO
	- Models (Entities) <-> Mapping Tables
	- DAO

#Program.cs

using System;
using System.Collections.Generic;
using Lesson08.Models;
using Lesson08.DAO;

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

            do
            {
                ShowMenu();
                choose = int.Parse(Console.ReadLine());

                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 xoa thong tin: ");
            int id = int.Parse(Console.ReadLine());

            //CustomerDAO.Delete(id);
            Customer customerFind = CustomerDAO.FindByID(id);

            if (customerFind != null)
            {
                CustomerDAO.Delete(id);
            }
            else
            {
                Console.WriteLine("Khong tim thay khach hang vs ID = " + id);
            }
        }

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

            Customer customerFind = CustomerDAO.FindByID(id);

            if(customerFind != null)
            {
                customerFind.Input();
                CustomerDAO.Update(customerFind);
            } else
            {
                Console.WriteLine("Khong tim thay khach hang vs ID = " + id);
            }
        }

        private static void Input()
        {
            Customer customer = new Customer();
            customer.Input();

            CustomerDAO.Insert(customer);
        }

        private static void Display()
        {
            List<Customer> dataList = CustomerDAO.GetCustomerList();

            foreach(Customer customer in dataList)
            {
                customer.Display();
            }
        }

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

#Models/Customer.cs

using System;
namespace Lesson08.Models
{
    public class Customer
    {
        public int ID { get; set; }
        public string Fullname { get; set; }
        public string Email { get; set; }
        public string PhoneNumber { get; set; }
        public string Birthday { get; set; }
        public string Address { get; set; }
        public string Password { get; set; }

        public Customer()
        {
        }

        public Customer(int id, string fullname, string email,
            string phoneNumber, string birthday, string address, string password)
        {
            ID = id;
            Fullname = fullname;
            Email = email;
            PhoneNumber = phoneNumber;
            Birthday = birthday;
            Address = address;
            Password = password;
        }

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

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

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

            Console.WriteLine("Nhap ngay sinh: ");
            Birthday = Console.ReadLine();

            Console.WriteLine("Nhap dia chi: ");
            Address = Console.ReadLine();

            Console.WriteLine("Nhap mat khau: ");
            Password = Console.ReadLine();
        }

        public void Display()
        {
            Console.WriteLine("Ten: {0}, email: {1}, sdt: {2}, ngay sinh: {3}, " +
                "dia chi: {4}, mat khau: {5}", Fullname, Email, PhoneNumber,
                Birthday, Address, Password);
        }
    }
}

#DAO/CustomerDAO.cs

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

namespace Lesson08.DAO
{
    public class CustomerDAO
    {
        public static List<Customer> GetCustomerList()
        {
            List<Customer> dataList = new List<Customer>();

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

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

            while(reader.Read())
            {
                Customer customer = new Customer(
                    int.Parse(reader["id"].ToString()),
                    reader["fullname"].ToString(),
                    reader["email"].ToString(),
                    reader["phone_number"].ToString(),
                    reader["birthday"].ToString(),
                    reader["address"].ToString(),
                    reader["password"].ToString()
                    );

                dataList.Add(customer);
            }

            //Close connection
            conn.Close();

            return dataList;
        }

        public static void Insert(Customer customer)
        {
            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query
            string sql = "insert into customer(fullname, email, phone_number, birthday, address, password) " +
                "values(@fullname, @email, @phone_number, @birthday, @address, @password)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@fullname", customer.Fullname);
            command.Parameters.AddWithValue("@email", customer.Email);
            command.Parameters.AddWithValue("@phone_number", customer.PhoneNumber);
            command.Parameters.AddWithValue("@birthday", customer.Birthday);
            command.Parameters.AddWithValue("@address", customer.Address);
            command.Parameters.AddWithValue("@password", customer.Password);

            command.ExecuteNonQuery();

            //Close connection
            conn.Close();
        }

        public static void Update(Customer customer)
        {
            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query
            string sql = "update customer set fullname = @fullname, email = @email, phone_number = @phone_number, birthday = @birthday, address = @address, password = @password where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@fullname", customer.Fullname);
            command.Parameters.AddWithValue("@email", customer.Email);
            command.Parameters.AddWithValue("@phone_number", customer.PhoneNumber);
            command.Parameters.AddWithValue("@birthday", customer.Birthday);
            command.Parameters.AddWithValue("@address", customer.Address);
            command.Parameters.AddWithValue("@password", customer.Password);
            command.Parameters.AddWithValue("@id", customer.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 customer where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@id", id);

            command.ExecuteNonQuery();

            //Close connection
            conn.Close();
        }

        public static Customer FindByID(int id)
        {
            List<Customer> dataList = new List<Customer>();
            Customer customer = null;

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

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

            MySqlDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                customer = new Customer(
                    int.Parse(reader["id"].ToString()),
                    reader["fullname"].ToString(),
                    reader["email"].ToString(),
                    reader["phone_number"].ToString(),
                    reader["birthday"].ToString(),
                    reader["address"].ToString(),
                    reader["password"].ToString()
                    );
            }

            //Close connection
            conn.Close();

            return customer;
        }
    }
}

#DAO/Config.cs

using System;
namespace Lesson08.DAO
{
    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;
        }
    }
}