Link Video Bài Giảng

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. Tao database + tables
B2. Tao project + add thu vien csdl
B3. Models (Mapping tables)
B4. DAO
B5. Code chuc nang

CREATE TABLE `bt2473_student` (
  `id` int(11) NOT NULL,
  `rollno` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fullname` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `father_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `father_phone` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mother_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mother_phone` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL
)

#Program.cs

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

namespace BT2473
{
    delegate void SwitchOption();

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

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

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

        private static void Input()
        {
            Student student = new Student();
            student.Input();

            StudentDAO.Insert(student);

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

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

            foreach(Student student in studentList)
            {
                student.Display();
            }
        }

        private static void Import()
        {
            try
            {
                //B1. Doc noi dung json
                string json = System.IO.File.ReadAllText(@"students.json");

                //B2. Chuyen json string -> List Object
                List<Student> studentList =
                    Newtonsoft.Json.JsonConvert.DeserializeObject<List<Student>>(json);

                //B3. Import data vao CSDL
                foreach (Student student in studentList)
                {
                    //Kiem tra xem student -> ton tai trong CSDL
                    Student find = StudentDAO.FindByID(student.ID);

                    if (find != null)
                    {
                        //update
                        StudentDAO.Update(student);
                    }
                    else
                    {
                        //insert
                        StudentDAO.Insert(student);
                    }
                }
            } catch
            {
                Console.WriteLine("Error -> import data");
            }

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

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

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

                //B3. Luu noi dung vao file students.json
                System.IO.File.WriteAllText(@"students.json", json);
            } catch
            {
                Console.WriteLine("Error trong quan trinh luu file");
            }

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

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

        static void ShowMenu()
        {
            Console.WriteLine("1. Nhap du lieu");
            Console.WriteLine("2. Hien thi du lieu");
            Console.WriteLine("3. Import JSON");
            Console.WriteLine("4. Export JSON");
            Console.WriteLine("5. Thoat");
            Console.WriteLine("Chon: ");
        }
    }
}

#Models/Student.cs

using System;
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 Email { get; set; }
        public string Phone { 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 void ReadData(MySqlDataReader reader)
        {
            ID = int.Parse(reader["id"].ToString());
            RollNo = reader["rollno"].ToString();
            Fullname = reader["fullname"].ToString();
            Email = reader["email"].ToString();
            Phone = reader["phone"].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 email: ");
            Email = Console.ReadLine();

            Console.WriteLine("Nhap sdt: ");
            Phone = 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}, msv: {1}, ten: {2}, email: {3}", ID, RollNo, Fullname, Email);
        }
    }
}

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

#DAO/StudentDAO.cs

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

namespace BT2473.DAO
{
    public class StudentDAO
    {
        public static void Insert(Student student)
        {
            //Open connection
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query
            string sql = "insert into bt2473_student(rollno, fullname, email, phone, address, status, father_name, father_phone, mother_name, mother_phone) values (" +
                "@rollno, @fullname, @email, @phone, @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("@email", student.Email);
            command.Parameters.AddWithValue("@phone", student.Phone);
            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
            conn.Close();
        }

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

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

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

            MySqlDataReader reader = command.ExecuteReader();

            while(reader.Read())
            {
                Student student = new Student();
                student.ReadData(reader);

                dataList.Add(student);
            }

            //Close connection
            conn.Close();

            return dataList;
        }

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

            //Query
            string sql = "update bt2473_student set rollno = @rollno, fullname = @fullname, " +
                "email = @email, phone = @phone, 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("@email", student.Email);
            command.Parameters.AddWithValue("@phone", student.Phone);
            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
            conn.Close();
        }

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

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

            //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.ReadData(reader);
            }

            //Close connection
            conn.Close();

            return student;
        }
    }
}