Link Video Bài Giảng

Kết nối CSDL (MySQL) <-> C#

- Viết ứng dụng quản lý người:
	- CRUD: Lấy dánh sách người dùng, thêm/sửa/xoá

B1. Tìm hiểu thư viện hỗ trợ kết tới CSDL
B2. DAO
	- Tao models <-> tables (CSDL)
	- DAO -> lay danh sach/them/sua/xoa -> tables

#Program.cs

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

namespace Lesson09
{
    delegate void SwitchCase();

    class Program
    {
        static void Main(string[] args)
        {
            //List<string> tableList = TestDAO.DumpTables();
            //foreach(string s in tableList)
            //{
            //    Console.WriteLine("Table Name: " + s);
            //}
            //TestDAO.DumpTableFields("users");

            SwitchCase[] options = {Display, Insert, Update, Delete, Search, ExitProgram};

            int choose;

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

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

        private static void Display()
        {
            List<Users> dataList = UserDAO.GetUserList(null);

            Console.WriteLine("=== Danh sach nguoi dung");
            foreach(Users u in dataList)
            {
                u.Display();
            }
        }

        private static void Insert()
        {
            Users u = new Users();
            u.Input();

            UserDAO.Insert(u);
        }

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

            Users userFind = UserDAO.Find(id);
            if(userFind != null)
            {
                userFind.Input();

                UserDAO.Update(userFind);
            } else
            {
                Console.WriteLine("Khong tim thay nguoi dung vs ID = " + id);
            }
        }

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

            Users userFind = UserDAO.Find(id);
            if (userFind != null)
            {
                UserDAO.Delete(userFind.ID);
            }
            else
            {
                Console.WriteLine("Khong tim thay nguoi dung vs ID = " + id);
            }
        }

        private static void Search()
        {
            Console.WriteLine("Nhap ten nguoi dung can tim kiem: ");
            string name = Console.ReadLine();

            List<Users> dataList = UserDAO.GetUserList("%" + name + "%");

            Console.WriteLine("=== Danh sach nguoi dung");
            foreach (Users u in dataList)
            {
                u.Display();
            }
        }

        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. Tim kiem");
            Console.WriteLine("6. Thoat");
            Console.WriteLine("Chon: ");
        }
    }
}

#Users.cs

using System;
using MySql.Data.MySqlClient;

namespace Lesson09.Models
{
    public class Users
    {
        public int ID { get; set; }
        public string Fullname { get; set; }
        public string Email { get; set; }
        public string Birthday { get; set; }
        public string Password { get; set; }
        public string Address { get; set; }

        public Users()
        {
        }

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

        public void ReadData(MySqlDataReader reader)
        {
            ID = int.Parse(reader["id"].ToString());
            Fullname = reader["fullname"].ToString();
            Email = reader["email"].ToString();
            Birthday = reader["birthday"].ToString();
            Password = reader["password"].ToString();
            Address = reader["address"].ToString();
        }

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

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

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

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

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

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

#Config.cs

using System;
namespace Lesson09.DAO
{
    public class Config
    {
        static string HOST = "localhost";
        static string PORT = "3306";
        static string DATABASE = "C2010L";
        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);
        }
    }
}

#TestDAO.cs

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

namespace Lesson09.DAO
{
    public class TestDAO
    {
        public static List<string> DumpTables()
        {
            List<string> dataList = new List<string>();

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

            //Query
            string sql = "SHOW TABLES";
            MySqlCommand command = new MySqlCommand(sql, conn);

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string tableName = reader[0].ToString();
                dataList.Add(tableName);
            }

            //Close Connection
            conn.Close();

            return dataList;
        }

        public static void DumpTableFields(string tableName)
        {
            //Open Connection
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query
            string sql = "SHOW COLUMNS FROM "+tableName;
            MySqlCommand command = new MySqlCommand(sql, conn);

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string v = reader[0].ToString();
                Console.WriteLine(v);
            }

            //Close Connection
            conn.Close();
        }
    }
}

#UserDAO.cs

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

namespace Lesson09.DAO
{
    public class UserDAO
    {
        public static List<Users> GetUserList(string name)
        {
            List<Users> dataList = new List<Users>();

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

            //Query
            string sql = "select * from users";
            if(name != null)
            {
                sql += " where fullname like @name";
            }
            MySqlCommand command = new MySqlCommand(sql, conn);
            if (name != null)
            {
                command.Parameters.AddWithValue("@name", name);
            }

            MySqlDataReader reader = command.ExecuteReader();
            while(reader.Read())
            {
                Users u = new Users();
                u.ReadData(reader);

                dataList.Add(u);
            }

            //Close Connection
            conn.Close();

            return dataList;
        }

        public static void Insert(Users u)
        {
            //Open Connection
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

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

            command.ExecuteNonQuery();

            //Close Connection
            conn.Close();
        }

        internal static void Update(Users u)
        {
            //Open Connection
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

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

            command.ExecuteNonQuery();

            //Close Connection
            conn.Close();
        }

        public static Users Find(int id)
        {
            Users userFind = null;

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

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

            MySqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                userFind = new Users();
                userFind.ReadData(reader);
            }

            //Close Connection
            conn.Close();

            return userFind;
        }
    }
}