Link Video Bai Giang

Tools Import/Export JSON into Database C# - Nhập xuất JSON File vào CSDL C# - Quản lý sinh viên C#

B1. Thiết kế database (tables)
B2. Tao project + add thu vien mysql
B3. Models (Mapping tables trong CSDL)
B4. DAO (Config + Select/Them/Sua/Xoa du lieu)

#Program.cs

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

namespace BT2473
{
    delegate void SwitchCase();

    class Program
    {
        static void Main(string[] args)
        {
            SwitchCase[] options = {Display, Input, Export, Import, ExitProgram};

            int choose;

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

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

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

        private static void Import()
        {
            //Note yeu cau: doc noi dung json -> ID ton tai ->update | ID ko ton tai => insert
            string content = System.IO.File.ReadAllText(@"students.json");

            List<Student> studentList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Student>>(content);

            StudentDAO.BATCH = true;
            StudentDAO.OpenConnection();
            foreach(Student std in studentList)
            {
                Student stdFind = StudentDAO.FindByID(std.ID);

                if(stdFind == null)
                {
                    StudentDAO.Insert(std);
                } else
                {
                    StudentDAO.Update(std);
                }
            }
            StudentDAO.CloseConnection();
            StudentDAO.BATCH = false;
        }

        private static void Export()
        {
            //B1. Lay du lieu tu database
            List<Student> studentList = StudentDAO.GetStudentList();

            //B2. Chuyen List -> json string
            string json = Newtonsoft.Json.JsonConvert.SerializeObject(studentList);

            //B3. Luu students.json
            System.IO.File.WriteAllText(@"students.json", json);
        }

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

            StudentDAO.Insert(std);
        }

        private static void Display()
        {
            List<Student> studentList = StudentDAO.GetStudentList();
            Console.WriteLine("=== Danh sach sinh vien");

            foreach(Student 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. Export CSDL");
            Console.WriteLine("4. Import CSDL");
            Console.WriteLine("5. Thoat");
            Console.WriteLine("Chon: ");
        }
    }
}

#Utils/Utility.cs

using System;
namespace BT2473.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!!!");
                }
            }
        }
    }
}

#Models/Student.cs

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

namespace BT2473.Models
{
    public class Student
    {
        public int ID { get; set; }
        public string RollNo { get; set; }
        public string Fullname { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public string Status { get; set; }
        public string FatherName { get; set; }
        public string FatherPhone { get; set; }
        public string MotherName { get; set; }
        public string MotherPhone { get; set; }

        public Student()
        {
        }

        public Student(int id, string rollNo, string fullname, string phone,
            string email, string address, string status,
            string fatherName, string fatherPhone,
            string motherName, string motherPhone)
        {
            ID = id;
            RollNo = rollNo;
            Fullname = fullname;
            Phone = phone;
            Email = email;
            Address = address;
            Status = status;
            FatherName = fatherName;
            FatherPhone = fatherPhone;
            MotherName = motherName;
            MotherPhone = motherPhone;
        }

        public void Read(MySqlDataReader reader)
        {
            ID = int.Parse(reader["id"].ToString());
            RollNo = reader["rollno"].ToString();
            Fullname = reader["fullname"].ToString();
            Phone = reader["phone"].ToString();
            Email = reader["email"].ToString();
            Address = reader["address"].ToString();
            Status = reader["status"].ToString();
            FatherName = reader["father_name"].ToString();
            FatherPhone = reader["father_phone"].ToString();
            MotherName = reader["mother_name"].ToString();
            MotherPhone = reader["mother_phone"].ToString();
        }

        public void Input()
        {
            Console.WriteLine("Nhap MSV: ");
            RollNo = Console.ReadLine();

            Console.WriteLine("Nhap ten: ");
            Fullname = Console.ReadLine();

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

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

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

            Console.WriteLine("Nhap trang thai: ");
            Status = Console.ReadLine();

            Console.WriteLine("Nhap ten cha: ");
            FatherName = Console.ReadLine();

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

            Console.WriteLine("Nhap ten me: ");
            MotherName = Console.ReadLine();

            Console.WriteLine("Nhap SDT me: ");
            MotherPhone = Console.ReadLine();
        }

        public void Display()
        {
            Console.WriteLine("ID: {0}, ten: {1}, msv: {2}, sdt: {3}, email: {4}, " +
                "dia chi: {5}, trang thai: {6}, ten cha: {7}, sdt cha: {8}, " +
                "ten me: {9}, sdt me: {10}", ID, Fullname, RollNo, Phone, Email,
                Address, Status, FatherName, FatherPhone, MotherName, MotherPhone);
        }
    }
}

#DAO/StudentDAO.cs

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

namespace BT2473.DAO
{
    public class StudentDAO
    {
        static MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
        public static bool BATCH = false;

        public static void OpenConnection()
        {
            conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();
        }

        public static void CloseConnection()
        {
            conn.Close();
        }

        public static List<Student> GetStudentList()
        {
            List<Student> studentList = new List<Student>();

            //Open connection
            if(!BATCH) OpenConnection();

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

            while(reader.Read())
            {
                Student std = new Student();
                std.Read(reader);

                studentList.Add(std);
            }
            reader.Close();

            //Close connection
            if (!BATCH) CloseConnection();

            return studentList;
        }

        public static Student FindByID(int id)
        {
            Student student = null;

            //Open connection
            if (!BATCH) OpenConnection();

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

            MySqlDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                student = new Student();
                student.Read(reader);
            }
            reader.Close();

            //Close connection
            if (!BATCH) CloseConnection();

            return student;
        }

        public static void Insert(Student student)
        {
            //Open connection
            if (!BATCH) OpenConnection();

            //Query
            string sql = "insert into bt2473_student(rollno, fullname, phone, email, " +
                "address, status, father_name, father_phone, mother_name, mother_phone) values(" +
                "@rollno, @fullname, @phone, @email, " +
                "@address, @status, @father_name, @father_phone, @mother_name, @mother_phone)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@rollno", student.RollNo);
            command.Parameters.AddWithValue("@fullname", student.Fullname);
            command.Parameters.AddWithValue("@phone", student.Phone);
            command.Parameters.AddWithValue("@email", student.Email);
            command.Parameters.AddWithValue("@address", student.Address);
            command.Parameters.AddWithValue("@status", student.Status);
            command.Parameters.AddWithValue("@father_name", student.FatherName);
            command.Parameters.AddWithValue("@father_phone", student.FatherPhone);
            command.Parameters.AddWithValue("@mother_name", student.MotherName);
            command.Parameters.AddWithValue("@mother_phone", student.MotherPhone);

            command.ExecuteNonQuery();
            //Close connection
            if (!BATCH) CloseConnection();
        }

        public static void Update(Student student)
        {
            //Open connection
            if (!BATCH) OpenConnection();

            //Query
            string sql = "update bt2473_student set rollno = @rollno, fullname = @fullname, phone = @phone, email = @email, " +
                "address = @address, status = @status, father_name = @father_name, father_phone = @father_phone, mother_name = @mother_name, mother_phone = @mother_phone where id = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@rollno", student.RollNo);
            command.Parameters.AddWithValue("@fullname", student.Fullname);
            command.Parameters.AddWithValue("@phone", student.Phone);
            command.Parameters.AddWithValue("@email", student.Email);
            command.Parameters.AddWithValue("@address", student.Address);
            command.Parameters.AddWithValue("@status", student.Status);
            command.Parameters.AddWithValue("@father_name", student.FatherName);
            command.Parameters.AddWithValue("@father_phone", student.FatherPhone);
            command.Parameters.AddWithValue("@mother_name", student.MotherName);
            command.Parameters.AddWithValue("@mother_phone", student.MotherPhone);
            command.Parameters.AddWithValue("@id", student.ID);

            command.ExecuteNonQuery();
            //Close connection
            if (!BATCH) CloseConnection();
        }
    }
}

#DAO/Config.cs

using System;
namespace BT2473.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;
        }
    }
}