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#

#Program.cs

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

namespace BT2473
{
    delegate void SwitchCase();

    class Program
    {
        static void Main(string[] args)
        {
            SwitchCase[] options = {Input, Display, Import, Export, ExitProgarm};
            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 != 5);
        }

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

            StudentDAO.Insert(student);
        }

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

            Console.WriteLine("=== Danh sach sinh vien");

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

        private static void Import01()
        {
            string json = System.IO.File.ReadAllText(@"students.json");

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

            StudentDAO.BATCH = true;
            StudentDAO.OpenConnection();
            try
            {
                foreach (Student student in studentList)
                {
                    Student studentFind = StudentDAO.FindByID(student.ID);

                    if (studentFind != null)
                    {
                        //Update
                        StudentDAO.Update(student);
                    }
                    else
                    {
                        //Insert
                        StudentDAO.Insert(student);
                    }
                }
            } catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
            StudentDAO.CloseConnection();
        }

        private static void Import()
        {
            string json = System.IO.File.ReadAllText(@"students.json");

            List<Student> studentList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Student>>(json);
            List<int> idList = new List<int>();
            foreach(Student student in studentList)
            {
                idList.Add(student.ID);
            }
            idList = StudentDAO.CheckAll(idList);

            StudentDAO.BATCH = true;
            StudentDAO.OpenConnection();
            try
            {
                foreach (Student student in studentList)
                {
                    if(idList.Contains(student.ID))
                    {
                        StudentDAO.Update(student);
                    } else
                    {
                        StudentDAO.Insert(student);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            StudentDAO.CloseConnection();
        }

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

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

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

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

        static void ShowMenu()
        {
            Console.WriteLine("1. Nhap du lieu vao database");
            Console.WriteLine("2. Hien thi");
            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 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 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);
        }

        public void Parse(MySqlDataReader reader)
        {
            ID = int.Parse(reader["id"].ToString());
            Fullname = reader["fullname"].ToString();
            RollNo = reader["rollno"].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();
        }
    }
}

#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 MySql.Data.MySqlClient;
using System.Collections.Generic;

namespace BT2473.DAO
{
    public class StudentDAO
    {
        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
            conn.Close();
        }

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

            //Query
            string sql = "insert into bt2473_student(fullname, rollno, email, phone, address, status, " +
                "father_name, father_phone, mother_name, mother_phone) values (@fullname, @rollno, @email, @phone, @address, @status, " +
                "@father_name, @father_phone, @mother_name, @mother_phone)";
            MySqlCommand command = new MySqlCommand(sql, conn);

            command.Parameters.AddWithValue("@fullname", student.Fullname);
            command.Parameters.AddWithValue("@rollno", student.RollNo);
            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
            if (!BATCH) CloseConnection();
        }

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

            //Query
            string sql = "update bt2473_student set fullname = @fullname, rollno = @rollno, 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("@fullname", student.Fullname);
            command.Parameters.AddWithValue("@rollno", student.RollNo);
            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
            if (!BATCH) CloseConnection();
        }

        public static List<Student> GetStudentList()
        {
            List<Student> dataList = 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 student = new Student();
                student.Parse(reader);

                dataList.Add(student);
            }
            reader.Close();

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

            return dataList;
        }

        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.Parse(reader);
            }
            reader.Close();

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

            return student;
        }

        public static List<int> CheckAll(List<int> idList)
        {
            List<int> idAvaiableList = new List<int>();
            //Open connection
            if (!BATCH) OpenConnection();

            //Query
            string ids = string.Join(",", idList);
            string sql = "select * from bt2473_student where id in (" + ids + ")";
            //select * from bt2473_student where id in (1, 2, 6, 4)
            MySqlCommand command = new MySqlCommand(sql, conn);

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                idAvaiableList.Add(int.Parse(reader["id"].ToString()));
            }
            reader.Close();

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

            return idAvaiableList;
        }
    }
}