Link Video Bai Giang

Nội dung kiến thức:
- Xây dựng ứng C# kết nối với CSDL (MySQL)

Mini Project: Ung dung quan ly PC (Computer)
- Lay danh thiet bi may tinh
- Them/sua/xoa
:CRUD

B1. Xay dung database (CSDL) + tables
B2. Tao project
B3. Add thu vien can thiet vao du an (CSDL)

Ung dung (C#) ->   Lib   -> CSDL (MySQL)

B4. Tao models (Mapping tables)
B5. DAO (truy toi CSDL)

#Program.cs

using System;
using Lesson09.DAO;
using Lesson09.Models;
using Lesson09.Utils;
using System.Collections.Generic;

namespace Lesson09
{
    delegate void SwitchCase();

    class Program
    {
        static void Main(string[] args)
        {
            SwitchCase[] options = { Display, Input, Update, Delete, ExitProgram };
            int choose;

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

                if(choose > 0 && choose <= options.Length)
                {
                    options[choose - 1]();
                } else
                {
                    Console.WriteLine("Nhap sai!!!");
                }
            } while (choose != options.Length);
        }

        private static void Display()
        {
            List<Computer> computerList = ComputerDAO.GetComputerList();
            Console.WriteLine("=== Danh sach thiet bi may tinh");
            foreach(Computer computer in computerList)
            {
                computer.Display();
            }
        }

        private static void Input()
        {
            Computer computer = new Computer();
            computer.Input();

            ComputerDAO.Insert(computer);
            Console.WriteLine("Insert thanh cong!!!");
        }

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

            Computer computerFind = ComputerDAO.FindByID(id);

            if(computerFind != null)
            {
                computerFind.Input();

                ComputerDAO.Update(computerFind);
            } else
            {
                Console.WriteLine("Khong tim thay ID = {0} trong CSDL", id);
            }
        }

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

            Computer computerFind = ComputerDAO.FindByID(id);

            if (computerFind != null)
            {
                ComputerDAO.Delete(computerFind.ID);
                Console.WriteLine("Xoa thanh cong!!!");
            }
            else
            {
                Console.WriteLine("Khong tim thay ID = {0} trong CSDL", id);
            }
        }

        private static void ExitProgram()
        {
            Console.WriteLine("Thoat chuong trinh");
        }

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

#Utils/Utility.cs

using System;
namespace Lesson09.Utils
{
    public class Utility
    {
        public static int ReadInt()
        {
            int value;

            while(true)
            {
                try
                {
                    value = int.Parse(Console.ReadLine());
                    return value;
                }
                catch
                {
                    Console.WriteLine("Nhap lai!!!");
                }
            }
        }
    }
}

#Models/Computer.cs

using System;
using Lesson09.Utils;
using MySql.Data.MySqlClient;

namespace Lesson09.Models
{
    public class Computer
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string DistributeName { get; set; }
        public string Producer { get; set; }
        public string ProduceYear { get; set; }
        public string ImportedDate { get; set; }
        public int BuyPrice { get; set; }
        public int SellPrice { get; set; }

        public Computer()
        {
        }

        public Computer(int iD)
        {
            ID = iD;
        }

        public Computer(int id, string name, string distributeName,
            string producer, string produceYear, string importedDate,
            int buyPrice, int sellPrice)
        {
            ID = id;
            Name = name;
            DistributeName = distributeName;
            Producer = producer;
            ProduceYear = produceYear;
            ImportedDate = importedDate;
            BuyPrice = buyPrice;
            SellPrice = sellPrice;
        }

        public void ReadData(MySqlDataReader reader)
        {
            ID = int.Parse(reader["id"].ToString());
            Name = reader["name"].ToString();
            DistributeName = reader["distribute_name"].ToString();
            Producer = reader["producer"].ToString();
            ProduceYear = reader["produce_year"].ToString();
            ImportedDate = reader["imported_date"].ToString();
            BuyPrice = int.Parse(reader["buy_price"].ToString());
            SellPrice = int.Parse(reader["sell_price"].ToString());
        }

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

            Console.WriteLine("Nhap nha phan phoi: ");
            DistributeName = Console.ReadLine();

            Console.WriteLine("Nhap nha san xuat: ");
            Producer = Console.ReadLine();

            Console.WriteLine("Nhap nam san xuat: ");
            ProduceYear = Console.ReadLine();

            Console.WriteLine("Nhap ngay nhap hang: ");
            ImportedDate = Console.ReadLine();

            Console.WriteLine("Nhap gia mua: ");
            BuyPrice = Utility.ReadInt();

            Console.WriteLine("Nhap gia ban: ");
            SellPrice = Utility.ReadInt();
        }

        public void Display()
        {
            Console.WriteLine("ID: {0}, ten: {1}, nha phan phoi: {2}, nha sx: {3}, " +
                "nam san xua: {4}, ngay nhap hang: {5}, gia mua: {6}, gia ban: {7}", ID, Name,
                DistributeName, Producer, ProduceYear, ImportedDate, BuyPrice, SellPrice);
        }
    }
}

#DAO/ComputerDAO.cs

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

namespace Lesson09.DAO
{
    public class ComputerDAO
    {
        public static List<Computer> GetComputerList()
        {
            List<Computer> dataList = new List<Computer>();

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

            //Query
            string sql = "select * from computer";
            MySqlCommand command = new MySqlCommand(sql, conn);

            MySqlDataReader reader = command.ExecuteReader();

            while(reader.Read())
            {
                Computer computer = new Computer();
                computer.ReadData(reader);

                dataList.Add(computer);
            }

            //Close connection
            conn.Close();

            return dataList;
        }

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

            //Query
            string sql = "insert into computer(name, distribute_name, producer, produce_year, imported_date, buy_price, sell_price) values (" +
                "@name, @distribute_name, @producer, @produce_year, @imported_date, @buy_price, @sell_price)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@name", computer.Name);
            command.Parameters.AddWithValue("@distribute_name", computer.DistributeName);
            command.Parameters.AddWithValue("@producer", computer.Producer);
            command.Parameters.AddWithValue("@produce_year", computer.ProduceYear);
            command.Parameters.AddWithValue("@imported_date", computer.ImportedDate);
            command.Parameters.AddWithValue("@buy_price", computer.BuyPrice);
            command.Parameters.AddWithValue("@sell_price", computer.SellPrice);

            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 computer where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@id", id);

            command.ExecuteNonQuery();

            //Close connection
            conn.Close();
        }

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

            //Query
            string sql = "update computer set name = @name, distribute_name = @distribute_name, producer = @producer, " +
                "produce_year = @produce_year, imported_date = @imported_date, buy_price = @buy_price, " +
                "sell_price = @sell_price where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@name", computer.Name);
            command.Parameters.AddWithValue("@distribute_name", computer.DistributeName);
            command.Parameters.AddWithValue("@producer", computer.Producer);
            command.Parameters.AddWithValue("@produce_year", computer.ProduceYear);
            command.Parameters.AddWithValue("@imported_date", computer.ImportedDate);
            command.Parameters.AddWithValue("@buy_price", computer.BuyPrice);
            command.Parameters.AddWithValue("@sell_price", computer.SellPrice);
            command.Parameters.AddWithValue("@id", computer.ID);

            command.ExecuteNonQuery();

            //Close connection
            conn.Close();
        }

        public static Computer FindByID(int id)
        {
            Computer computer = null;

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

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

            MySqlDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                computer = new Computer();
                computer.ReadData(reader);
            }

            //Close connection
            conn.Close();

            return computer;
        }
    }
}

#DAO/Config.cs

using System;
namespace Lesson09.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()
        {
            return string.Format("SERVER={0};PORT={1};DATABASE={2};UID={3};PASSWORD={4};SSL Mode=None",
                HOST, PORT, DATABASE, USERNAME, PASSWORD);
        }
    }
}