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

create table bt2473_student (
	id int primary key auto_increment,
	rollno varchar(20),
	fullname varchar(50),
	phone varchar(16),
	email varchar(150),
	address varchar(200),
	status varchar(20),
	father_name varchar(50),
	father_phone varchar(16),
	mother_name varchar(50),
	mother_phone varchar(16)
)

B2. Tao project + add thu vien csdl
B3. Create models (Mapping tables trong CSDL)
B4. DAO -> connection CSDL (Them/sua/xoa/lay danh du lieu trong CSDL)
B5. Import JSON -> CSDL

#Program.cs

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

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

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

                switch(choose)
                {
                    case 1:
                        Input();
                        break;
                    case 2:
                        Display();
                        break;
                    case 3:
                        Import();
                        break;
                    case 4:
                        Export();
                        break;
                    case 5:
                        Console.WriteLine("Thoat!!!");
                        break;
                    default:
                        Console.WriteLine("Nhap sai!!!");
                        break;
                }
            } while (choose != 5);
        }

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

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

            //B3. Luu noi dung content vao trong file students.json
            System.IO.File.WriteAllText(@"students.json", content);

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

        private static void Import()
        {
            string json = null;
            try
            {
                //Lay du lieu tu students.json
                json = System.IO.File.ReadAllText(@"students.json");
            } catch(Exception ex)
            {
                return;
            }

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

            StudentDAO.BATCH = true;
            StudentDAO.OpenConnection();
            //Duyet qua cac phan tu -> them vao database
            foreach(Student std in studentList)
            {
                Student stdFind = StudentDAO.FindByID(std.ID);
                if(stdFind == null)
                {
                    StudentDAO.Insert(std);
                } else
                {
                    StudentDAO.Update(std);
                }
            }
            StudentDAO.CloseConnection();
        }

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

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

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

            StudentDAO.Insert(student);

            Console.WriteLine("Them du lieu vao CSDL thanh cong!!!");
        }

        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;
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 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 Status: ");
            Status = Console.ReadLine();

            Console.WriteLine("Nhap Ho & Ten Cha: ");
            FatherName = Console.ReadLine();

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

            Console.WriteLine("Nhap Ho & Ten Me: ");
            MotherName = Console.ReadLine();

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

        public void Display()
        {
            Console.WriteLine("ID: {0}, MSV: {1}, Ten: {2}, SDT: {3}, " +
                "Email: {4}, Dia Chi: {5}, Status: {6}, Ten Cha: {7}, " +
                "SDT Cha: {8}, Ten Me: {9}, SDT Me: {10}", ID, RollNo,
                Fullname, Phone, Email, Address, Status, FatherName, FatherPhone,
                MotherName, MotherPhone);
        }
    }
}

#DataBase/StudentDAO.cs

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

namespace BT2473.DataBase
{
    public class StudentDAO : BaseDAO
    {
        public static void Insert(Student student)
        {
            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();

            if(!BATCH)
            {
                CloseConnection();
            }
        }

        public static void Update(Student student)
        {
            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();

            if (!BATCH)
            {
                CloseConnection();
            }
        }

        public static Student FindByID(int id)
        {
            if(id == 0)
            {
                return null;
            }

            Student std = null;

            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())
            {
                std = new Student(int.Parse(reader["id"].ToString()), reader["rollno"].ToString(),
                    reader["fullname"].ToString(), reader["phone"].ToString(), reader["email"].ToString(),
                    reader["address"].ToString(), reader["status"].ToString(), reader["father_name"].ToString(),
                    reader["father_phone"].ToString(), reader["mother_name"].ToString(), reader["mother_phone"].ToString());
            }
            reader.Close();

            if (!BATCH)
            {
                CloseConnection();
            }

            return std;
        }

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

            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(int.Parse(reader["id"].ToString()), reader["rollno"].ToString(),
                    reader["fullname"].ToString(), reader["phone"].ToString(), reader["email"].ToString(),
                    reader["address"].ToString(), reader["status"].ToString(), reader["father_name"].ToString(),
                    reader["father_phone"].ToString(), reader["mother_name"].ToString(), reader["mother_phone"].ToString());

                dataList.Add(std);
            }

            if (!BATCH)
            {
                CloseConnection();
            }

            return dataList;
        }
    }
}

#DataBase/Config.cs

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

#DataBase/BaseDAO.cs

using MySql.Data.MySqlClient;

namespace BT2473.DataBase
{
    public class BaseDAO
    {
        public static MySqlConnection conn = null;
        public static bool BATCH = false;

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

        public static void CloseConnection()
        {
            //Close connection
            if (conn != null)
            {
                conn.Close();
                conn = null;
            }
        }
    }
}