By GokiSoft.com| 14:25 28/10/2021|
C Sharp

Chương trình quản lý sách C# - Book project C# - CSDL BT2474

Thiết kế database đặt tên library gồm các bảng sau

- Book: gồm các column -> id tự tăng, bookName, price, authorName

Viết menu chương trình sau

1. Thêm 1 sách vào CSDL

2. Sửa 1 sách theo id trong CSDL

3. Hiển thị toàn bộ sách trong CSDL

4. Xoá sách trong CSDL

5. Thoát

Liên kết rút gọn:

https://gokisoft.com/2474

Bình luận

avatar
Trần Việt Đức Anh [C2010L]
2021-10-20 15:08:46


#Program.cs
using System;
using System.Collections.Generic;
using Ex2474.DAO;
using Ex2502.Model;

namespace Ex2502
{
    delegate void SwitchCase();
    class Program
    {
        static void Main(string[] args)
        {
            SwitchCase[] options = { Create, Update, Read, Delete, Exit };

            int choose;

            do
            {
                showMenu();
                choose = int.Parse(Console.ReadLine());
                if (choose <= options.Length && choose > 0)
                {
                    options[choose - 1]();
                }
                else
                {
                    Console.WriteLine("Try Again!!!");
                }
            } while (choose != 5);
        }

        private static void Create()
        {
            Book tmp = new Book();
            tmp.Input();

            BookDAO.Create(tmp);
        }

        private static void Update()
        {
            Console.WriteLine("Enter iD: ");
            int id = int.Parse(Console.ReadLine());

            Book bookcheck = BookDAO.Read(id);
            if (bookcheck != null)
            {
                bookcheck.Input();
                BookDAO.Update(bookcheck);
            }
            else
            {
                Console.WriteLine("No Available ID = " + id);
            }
        }

        private static void Read()
        {
            List<Book> books = BookDAO.GetBookList();
            List<Book> dataList = books;

            Console.WriteLine("==== List of Books =====");
            foreach (Book tmp in dataList)
            {
                tmp.Display();
            }
        }

        private static void Delete()
        {
            Console.WriteLine("Enter iD: ");
            int id = int.Parse(Console.ReadLine());

            Book userDelete = BookDAO.Read(id);
            if (userDelete != null)
            {
                BookDAO.Delete(userDelete.ID);
            }
            else
            {
                Console.WriteLine("Khong tim thay nguoi dung vs ID = " + id);
            }
        }

        private static void Exit()
        {
            Console.WriteLine("Exitingg....");
        }

        static void showMenu()
        {
            Console.WriteLine("\n1. Create a Book into database");
            Console.WriteLine("2. Update a Book in database");
            Console.WriteLine("3. Read ALL Book in Database");
            Console.WriteLine("4. Delete Book in Databae");
            Console.WriteLine("5. Exit");
            Console.Write("Choose: ");
        }
    }
}

#BookDAO.cs

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

namespace Ex2474.DAO
{
    class BookDAO
    {
        public static List<Book> GetBookList()
        {
            List<Book> booksList = new List<Book>();
            // Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query
            string sql = "select * from book";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Book tmp = new Book();
                tmp.ReadData(reader);

                booksList.Add(tmp);
            }

            //Close connection
            conn.Close();

            return booksList;
        }

        public static void Create(Book book)
        {
            // Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "insert into book(bookName,price,authorName)values(" +
                "@bookname, @price, @authorname)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@bookname", book.BookName);
            command.Parameters.AddWithValue("@price", book.Price);
            command.Parameters.AddWithValue("@authorname", book.AuthorName);

            command.ExecuteNonQuery();

            //Close 
            conn.Close();
        }

        public static void Update(Book book)
        {
            //Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "update book set bookName = @bookName, price = @price, authorName = @authorName where id = @ID";

            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@bookname", book.BookName);
            command.Parameters.AddWithValue("@price", book.Price);
            command.Parameters.AddWithValue("@authorname", book.AuthorName);
            command.Parameters.AddWithValue("@ID", book.ID);

            command.ExecuteNonQuery();

            //Close
            conn.Close();
        }

        public static Book Read(int ID)
        {
            Book bookRead = null;
            //Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "select * from book where id = @ID";

            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@ID", ID);

            MySqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                bookRead = new Book();
                bookRead.ReadData(reader);
            }

            //Close 
            conn.Close();
            return bookRead;
        }

        public static void Delete(int ID)
        {
            //Open connect 
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "delete from book where id = @ID";
            MySqlCommand command = new MySqlCommand(sql, conn);

            command.Parameters.AddWithValue("@id", ID);
            command.ExecuteNonQuery();
            //Close
            conn.Close();
        }
    }
}
#Config.cs


using System;

namespace Ex2474.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);
        }
    }
}
#Book.cs



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

namespace Ex2502.Model
{
    class Book
    {
        public int ID { get; set; }
        public string BookName { get; set; }
        public int Price { get; set; }
        public string AuthorName { get; set; }

        public Book()
        {

        }

        public Book(int iD, string bookName, int price, string authorName)
        {
            ID = iD;
            BookName = bookName;
            Price = price;
            AuthorName = authorName;
        }

        public void Input()
        {
            Console.Write("Import Book Name:");
            BookName = Console.ReadLine();

            Console.Write("Import Author Name:");
            AuthorName = Console.ReadLine();

            Console.Write("Import Price:");
            Price = int.Parse(Console.ReadLine());
        }

        public void ReadData(MySqlDataReader reader)
        {
            ID = int.Parse(reader["ID"].ToString());
            BookName = reader["BookName"].ToString();
            AuthorName = reader["Author Name"].ToString();
            Price = int.Parse(reader["Price"].ToString());
        }

        public void Display()
        {
            Console.WriteLine("ID: {0}, Book Name: {1}, Author Name: {2}, Price: {3}",
                ID, BookName, AuthorName, Price);
        }
    }
}


avatar
Võ Như Việt [C2010L]
2021-10-19 17:44:55


#Book.cs


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

namespace BT2474_QLBook_mySQL_
{
    class Book
    {
        public int ID { get; set; }
        public string BookName { get; set; }
        static int _price;
        /*
        public int Price { 
            get{
                return _price;
                    }
            set {
                if (value < 0)
                {
                    Console.WriteLine("Gia khong duoc < 0 ");
                }
                else { _price = value; }
            }
        }*/
        public int Price { get; set; }
        public string AuthorName { get; set; }

        public Book()
        {
        }

        public Book(int iD, string bookName, int price, string authorName)
        {
            ID = iD;
            BookName = bookName;
            Price = price;
            AuthorName = authorName;
        }


        public void Input()
        {
            Console.WriteLine("Nhap ten cuon sach: ");
            BookName = Console.ReadLine();
            Console.WriteLine("Nhap gia tien: ");
            Price = int.Parse(Console.ReadLine());
            Console.WriteLine("Nhap ten tac gia: ");
            AuthorName = Console.ReadLine();
            
        }

        public void Display()
        {
            Console.WriteLine("ID : {0}, Ten Sach: {1}, Gia : {2}, Tac Gia: {3} ", ID, BookName, Price, AuthorName);
        }

        public void ReadData(MySqlDataReader reader)
        {
            ID = int.Parse(reader["id"].ToString());
            BookName = reader["bookName"].ToString();
            Price = int.Parse(reader["price"].ToString());
            AuthorName = reader["authorName"].ToString();
        }
    }
}


#Program.cs


using System;
using BT2474_QLBook_mySQL_.DAO;
using System.Collections.Generic;

namespace BT2474_QLBook_mySQL_
{
    class Program
    {
        delegate void SwitchCase();
        static void Main(string[] args)
        {
            SwitchCase[] option = { Insert, Update, Display, Delete, ExitProgram };

            int choose;

            do {
                ShowMenu();
                choose = int.Parse(Console.ReadLine());
                if(choose <= option.Length && choose > 0)
                {
                    option[choose - 1]();

                }
                else
                {
                    Console.WriteLine("Nhap lai!!!");
                }
            
            } while (choose != 5);
        }

        private static void Insert()
        {
            Book b = new Book();
            b.Input();

            BookDAO.Insert(b);
        }

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

            Book bookcheck = BookDAO.Find(id);
            if(bookcheck != null)
            {
                bookcheck.Input();
                BookDAO.Update(bookcheck);
            }
            else
            {
                Console.WriteLine("Khong tim thay nguoi dung voi ID = " + id);
            }
        }

        private static void Display()
        {
            List<Book> booksList = BookDAO.GetBookList();

            Console.WriteLine("=== Danh sach cac quyen sach ===");
            foreach(Book b in booksList)
            {
                b.Display();
            }
        }

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

            Book bookFind = BookDAO.Find(id);
            if (bookFind != null)
            {
                BookDAO.Delete(bookFind.ID);
                Console.WriteLine("Da xoa thanh cong!!!");
            }
            else
            {
                Console.WriteLine("Khong tim thay quyen sach voi ID = " + id);
            }
        }

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

        static void ShowMenu()
        {
            Console.WriteLine();
            Console.WriteLine("1. Them 1 sach vao CSDL");
            Console.WriteLine("2. Sua 1 sach theo id trong CSDL");
            Console.WriteLine("3. Hien thi toan bo sach trong CSDL");
            Console.WriteLine("4. Xoa sach trong CSDL");
            Console.WriteLine("5. Thoat!!!");
            Console.WriteLine("Chon: ");
        }
    }
}


#BookDAO.cs


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

namespace BT2474_QLBook_mySQL_.DAO
{
    class BookDAO
    {
        public static List<Book> GetBookList()
        {
            List<Book> booksList = new List<Book>();
            // Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query
            string sql = "select * from book";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Book b = new Book();
                b.ReadData(reader);

                booksList.Add(b);
            }

            //Close connection
            conn.Close();

            return booksList;
        }

        public static void Insert(Book b)
        {
            // Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "insert into book(bookName,price,authorName)values(" +
                "@bookname, @price, @authorname)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@bookname", b.BookName);
            command.Parameters.AddWithValue("@price", b.Price);
            command.Parameters.AddWithValue("@authorname", b.AuthorName);

            command.ExecuteNonQuery();

            //Close 
            conn.Close();
        }

        public static void Update(Book b)
        {

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

            //Query

            string sql = "update book set bookName = @bookname, price = @price,authorName = @authorname where id = @id";
            
            MySqlCommand command = new MySqlCommand(sql, conn);
            
            command.Parameters.AddWithValue("@bookname", b.BookName);
            command.Parameters.AddWithValue("@price", b.Price);
            command.Parameters.AddWithValue("@authorname", b.AuthorName);
            command.Parameters.AddWithValue("@id", b.ID);

            command.ExecuteNonQuery();

            //Close 
            conn.Close();
        }

        public static void Delete(int id)
        {
            // Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "delete from book where id = @id";

            MySqlCommand command = new MySqlCommand(sql, conn);
          
            command.Parameters.AddWithValue("@id", id);

            command.ExecuteNonQuery();

            //Close 
            conn.Close();
        }

        public static Book Find(int id)
        {
            Book bookFind = null;
            // Open connect
            MySqlConnection conn = new MySqlConnection(Config.GetConnectionString());
            conn.Open();

            //Query

            string sql = "select * from book where id = @id";

            MySqlCommand command = new MySqlCommand(sql, conn);

            command.Parameters.AddWithValue("@id", id);

            MySqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                bookFind = new Book();
                bookFind.ReadData(reader);
            }

            //Close 
            conn.Close();

            return bookFind;
        }
    }
}


#Config.cs


using System;

namespace BT2474_QLBook_mySQL_.Connect
{
    class Config
    {
        static string HOST = "localhost";
        static string PORT = "3306";
        static string DATABASE = "Library";
        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);
        }
    }
}


avatar
Đào Mạnh Dũng [C2010L]
2021-10-19 15:19:32


#Book.cs


using MySql.Data.MySqlClient;

namespace _2474.Models
{
    internal class Book
    {
        public int id { get; set; }
        public string bookName { get; set; }
        public float price { get; set; }
        public string authorName { get; set; }

        public Book()
        {
        }

        public Book(int id, string bookName, float price, string authorName)
        {
            this.id = id;
            this.bookName = bookName;
            this.price = price;
            this.authorName = authorName;
        }

        public Book(MySqlDataReader reader)
        {
            this.id = int.Parse(reader["id"].ToString());
            this.bookName = reader["bookName"].ToString();
            this.price = float.Parse(reader["price"].ToString());
            this.authorName = reader["authorName"].ToString();
        }
        public void Input()
        {
            System.Console.WriteLine("nhap bookName : ");
            this.bookName = System.Console.ReadLine();
            System.Console.WriteLine("nhap price : ");
            this.price = lib.Utility.ReadFloat();
            System.Console.WriteLine("nhap authorName : ");
            this.authorName = System.Console.ReadLine();
        }
        public void Display()
        {
            System.Console.WriteLine(this);
        }
        public override string ToString()
        {
            return "Book{" + "id=" + id + ", bookName=" + bookName + ", price=" + price + ", authorName=" + authorName + '}';
        }
    }
}


#Book.cs


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

namespace _2474.DAO
{
    internal class Book
    {
        public static List<Models.Book> SELECT()
        {
            List<Models.Book> dataList = new List<Models.Book>();

            MySqlConnection conn = new MySqlConnection(Config.Get);
            conn.Open();

            string sql = "SELECT * FROM book";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
                dataList.Add(new Models.Book(reader));

            conn.Close();

            return dataList;
        }

        public static void INSERT(Models.Book book)
        {
            MySqlConnection conn = new MySqlConnection(Config.Get);
            conn.Open();

            string sql = "INSERT INTO `book` (`bookName`, `price`, `authorName`) " +
                "VALUES (@bookName, @price, @authorName)";

            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@bookName", book.bookName);
            command.Parameters.AddWithValue("@price", book.price);
            command.Parameters.AddWithValue("@authorName", book.authorName);

            command.ExecuteNonQuery();

            conn.Close();
        }

        internal static void UPDATE(Models.Book book)
        {
            MySqlConnection conn = new MySqlConnection(Config.Get);
            conn.Open();

            string sql = "UPDATE `book` SET `bookName` = @bookName, `price` = @price, `authorName` = @authorName WHERE `book`.`id` = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@bookName", book.bookName);
            command.Parameters.AddWithValue("@price", book.price);
            command.Parameters.AddWithValue("@authorName", book.authorName);
            command.Parameters.AddWithValue("@id", book.id);

            command.ExecuteNonQuery();

            conn.Close();
        }

        public static void DELETE(int id)
        {
            MySqlConnection conn = new MySqlConnection(Config.Get);
            conn.Open();

            string sql = "DELETE FROM `book` WHERE `book`.`id` = @id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@id", id);

            command.ExecuteNonQuery();

            conn.Close();
        }
    }
}


#Config.cs


namespace _2474.DAO
{
    internal class Config
    {
        private string HOST, PORT, DATABASE, USERNAME, PASSWORD;

        public static string Get
        {
            get
            {
                return Config.GetInstance().ToString();
            }
        }

        public Config()
        {
            HOST = "localhost";
            PORT = "3306";
            DATABASE = "library";
            USERNAME = "root";
            PASSWORD = "";
        }

        private static Config _instance;

        public static Config GetInstance()
        {
            if (_instance == null)
            {
                _instance = new Config();
            }
            return _instance;
        }

        public override string ToString()
        {
            return string.Format("SERVER={0};PORT={1};DATABASE={2};UID={3};PASSWORD={4};SSL Mode=None", HOST, PORT, DATABASE, USERNAME, PASSWORD);
        }
    }
}


#Program.cs


using _2474.Models;
using lib;
using System;
using System.Collections.Generic;

namespace _2474
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            SwitchCase[] menu = { ShowMenu, SELECT, INSERT, UPDATE, DELETE, Exit };
            Utility.Menu(menu);
        }

        private static void Exit()
        {
            Environment.Exit(0);
        }

        private static void DELETE()
        {
            Console.WriteLine("nhap id DELETE : ");
            DAO.Book.DELETE(Utility.ReadInt());
        }

        private static void UPDATE()
        {
            Console.WriteLine("nhap id UPDATE : ");
            Book book = new Book();
            book.id = Utility.ReadInt();
            book.Input();
            DAO.Book.UPDATE(book);
        }

        private static void INSERT()
        {
            Book book = new Book();
            book.Input();
            DAO.Book.INSERT(book);
        }

        private static void SELECT()
        {
            List<Book> books = DAO.Book.SELECT();
            foreach (var item in books)
            {
                item.Display();
            }
        }

        private static void ShowMenu()
        {
            Console.WriteLine("1. Hiển thị toàn bộ sách trong CSDL\n" +
            "\n" +
            "2. Thêm 1 sách vào CSDL\n" +
            "\n" +
            "3. Sửa 1 sách theo id trong CSDL\n" +
            "\n" +
            "4. Xoá sách trong CSDL\n" +
            "\n" +
            "5. Thoát");
        }
    }
}


#Utility.cs


using Newtonsoft.Json;
using System;
using System.IO;

namespace lib
{
    internal delegate void SwitchCase();

    internal class Utility
    {
        public static void Menu(SwitchCase[] menu)
        {
            int choose;
            while (true)
            {
                menu[0]();
                choose = Utility.ReadInt();
                if (choose <= menu.Length)
                {
                    menu[choose]();
                }
                else Console.WriteLine("Dau vao khong hop le! ");
            }
        }

        public static int ReadInt()
        {
            int integer;
            while (true)
            {
                try
                {
                    integer = int.Parse(Console.ReadLine());
                    break;
                }
                catch (Exception)
                {
                    Console.Write("du lieu dau vao khong hop le, nhap lai : ");
                }
            }
            return integer;
        }

        public static float ReadFloat()
        {
            float _float;
            while (true)
            {
                try
                {
                    _float = float.Parse(System.Console.ReadLine());
                    break;
                }
                catch (Exception)
                {
                    Console.Write("du lieu dau vao khong hop le, nhap lai : ");
                }
            }
            return _float;
        }

        public static DateTime ReadDate()
        {
            DateTime date;
            while (true)
            {
                try
                {
                    date = ConvertDateTime(Console.ReadLine());
                    break;
                }
                catch (Exception)
                {
                    Console.Write("du lieu dau vao khong hop le, nhap lai : ");
                }
            }
            return date;
        }

        public static string ConvertDateTime(DateTime myDate)
        {
            return myDate.ToString("dd-MM-yyyy");
        }

        public static DateTime ConvertDateTime(string str)
        {
            return DateTime.ParseExact(str, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
        }

        public static T ReadfileJson<T>(string path)
        {
            return JsonConvert.DeserializeObject<T>(System.IO.File.ReadAllText(path));
        }

        public static void SavefileJson(object obj, string path)
        {
            System.IO.File.WriteAllText(path, JsonConvert.SerializeObject(obj));
            Console.WriteLine("save file json is success!");
        }

        public static void SaveObject(object serialize, string path)
        {
            using (Stream stream = File.Open(path, FileMode.Create))
            {
                new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter().Serialize(stream, serialize);
                Console.WriteLine("save file object is success!");
            }
        }

        public static object ReadObject(string path)
        {
            using (Stream stream = File.Open(path, FileMode.Open))
            {
                return new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter().Deserialize(stream);
            }
        }
    }
}


avatar
Hoàng Thiện Thanh [community,AAHN-C2009G]
2021-10-11 10:31:15


#Program.cs


using System;
using BookManageSQL.Utils;
using BookManageSQL.Models;
using BookManageSQL.Database;
using System.Collections.Generic;

namespace BookManageSQL
{
    public class Program
    {
        public static void ShowMenu()
        {
            Console.WriteLine("1. Add Book\n" +
                "2. Edit Book\n" +
                "3. Display Books\n" +
                "4. Delete Book\n" +
                "5. Exit\n");
        }
        private static void UpdateBook()
        {
            Console.WriteLine("Input ID of book to edit: ");
            int id = Utility.ReadInt();

            Book bFind = BookDAO.FindByID(id);

            if (bFind != null)
            {
                BookDAO.Update(bFind);
            }
            else
            {
                Console.WriteLine("Cannot find any suitable books");
            }
        }
        private static void DeleteBook()
        {
            Console.WriteLine("Input the ID of Book to delete:");
            int d = Utility.ReadInt();

            Book bFind = BookDAO.FindByID(d);

            if (bFind != null)
            {
                BookDAO.DeleteBook(d);
            }
            else
            {
                Console.WriteLine("Cannot find Books");
            }
        }
        private static void InsertBook()
        {
            Console.WriteLine("Input book info:");
            Book b = new Book();
            BookDAO.Insert(b);
            Console.WriteLine("Book Added!");
        }
        private static void DisplayBooks()
        {
            List<Book> BookList = BookDAO.GetBookList();

            Console.WriteLine("====== Danh sach sinh vien");
            foreach (Book b in BookList)
            {
                b.Display();
            }
        }
        static void Main(string[] args)
        {
            int choose;
            do
            {
                ShowMenu();
                choose = Utility.ReadInt();
                switch (choose)
                {
                    case 1:
                        InsertBook();
                        break;
                    case 2:
                        UpdateBook();
                        break;
                    case 3:
                        DisplayBooks();
                        break;
                    case 4:
                        DeleteBook();
                        break;
                    case 5:
                        Console.WriteLine("Exited!");
                        break;
                    default:
                        Console.WriteLine("Wrong Input!");
                        break;
                }
            } while (choose != 5);
        }
    }
}


#BookDAO.cs


using BookManageSQL.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Text;

namespace BookManageSQL.Database
{
    public class BookDAO
    {
        public static List<Book> GetBookList()
        {
            List<Book> BookList = new List<Book>();
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            string sql = "SELECT * FROM `Library`";
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                int id = int.Parse(reader["Id"].ToString());
                string bookname = reader["BookName"].ToString();
                int price = int.Parse(reader["Price"].ToString());
                string authorname = reader["AuthorName"].ToString();

                Book b = new Book(id, bookname, price, authorname);
                BookList.Add(b);
            }
            conn.Close();

            return BookList;
        }

        public static void Insert(Book b)
        {
            b.Input();
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            string sql = "INSERT INTO `Book`(BookName, Price, AuthorName)" +
                "VALUES(@BookName, @Price, @AuthorName)";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@BookName", b.BookName);
            command.Parameters.AddWithValue("@Price", b.Price);
            command.Parameters.AddWithValue("@AuthorName", b.AuthorName);

            command.ExecuteNonQuery();
            conn.Close();
        }

        public static void Update(Book b)
        {
            b.Input();
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            string sql = "UPDATE `Book` SET " +
                "BookName = @BookName, Price = @Price, AuthorName = @AuthorName " +
                "WHERE Id = @Id;";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@BookName", b.BookName);
            command.Parameters.AddWithValue("@Price", b.Price);
            command.Parameters.AddWithValue("@AuthorName", b.AuthorName);
            command.Parameters.AddWithValue("@Id", b.ID);

            command.ExecuteNonQuery();
            conn.Close();
        }

        public static Book FindByID(int id)
        {
            Book b = null;
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            string sql = "SELECT * FROM `Book` WHERE Id = @Id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@Id", id);

            MySqlDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                string BookName = reader["BookName"].ToString();
                float Price = float.Parse(reader["Price"].ToString());
                string AuthorName = reader["AuthorName"].ToString();

                b = new Book(id, BookName, Price, AuthorName);
            }
            conn.Close();

            return b;
        }

        public static void DeleteBook(int id)
        {
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            string sql = "DELETE FROM `Book` WHERE Id = @Id";
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();

            conn.Close();
        }
    }
}


#Config.cs


using System;
using System.Collections.Generic;
using System.Text;

namespace BookManageSQL.Database
{
    public class Config
    {
        static string HOST = "localhost";
        static string PORT = "3306";
        static string DATABASE = "library";
        static string USERNAME = "root";
        static string PASSWORD = "";

        public static string getConnectionString()
        {
            string conn = String.Format("SERVER={0};PORT={1};DATABASE={2};UID={3};PASSWORD={4};SSL Mode=None",
                HOST, PORT, DATABASE, USERNAME, PASSWORD);
            return conn;
        }
    }
}


#Library.sql


CREATE DATABASE IF NOT EXISTS `Library`;
USE `Library`;

CREATE TABLE  `Book`(
`Id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
`BookName` VARCHAR(255) NOT NULL,
`Price` FLOAT NOT NULL,
`AuthorName` VARCHAR(255) NOT NULL
);
INSERT INTO `Book`
VALUES(1,'King Author',12000,'King');


#Book.cs


using BookManageSQL.Utils;
using System;
using System.Collections.Generic;
using System.Text;

namespace BookManageSQL.Models
{
    public class Book
    {
        public int ID { get; set; }
        public string BookName { get; set; }
        public float Price { get; set; }
        public string AuthorName { get; set; }
        public Book()
        {

        }
        public Book(int ID, string BookName, float Price, string AuthorName)
        {
            this.ID = ID;
            this.BookName = BookName;
            this.Price = Price;
            this.AuthorName = AuthorName;
        }
        public void Input()
        {
            Console.WriteLine("Input Book Name:");
            BookName = Console.ReadLine();
            Console.WriteLine("Input Price:");
            Price = Utility.ReadFloat();
            Console.WriteLine("Input Author Name:");
            AuthorName = Console.ReadLine();
        }
        public void Display()
        {
            Console.WriteLine("Book Name: {0}\nPrice: {1}\nAuthorName: {2}", BookName, Price, AuthorName);

        }
    }
}


#Utility.cs


using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace BookManageSQL.Utils
{
    public class Utility
    {
        public static string ReadNum()
        {
            string str = Console.ReadLine();
            while (!Regex.Match(str, "^[0-9]*$").Success)
            {
                Console.WriteLine("Only accept numbers as text input.");
                str = Console.ReadLine();
            }
            return str;
        }
        public static string ReadAlp()
        {
            string str = Console.ReadLine();
            while (!Regex.Match(str, "^([a-zA-Z]+|[a-zA-Z]+\\s[a-zA-Z]+)*$").Success)
            {
                Console.WriteLine("Only accept alphabet letters as text input.");
                str = Console.ReadLine();
            }
            return str;
        }
        public static int ReadInt()
        {
            string str = Console.ReadLine();
            bool check()
            {
                try { int.Parse(str); } catch (Exception) { return false; }
                return true;
            }
            while (!check())
            {
                Console.WriteLine("Cannot read the string as non float numeric input");
                str = Console.ReadLine();
            }
            return int.Parse(str);
        }
        public static float ReadFloat()
        {
            string str = Console.ReadLine();
            bool check()
            {
                try { float.Parse(str); } catch (Exception) { return false; }
                return true;
            }
            while (!check())
            {
                Console.WriteLine("Cannot read the string as numeric input");
                str = Console.ReadLine();
            }
            return float.Parse(str);
        }
    }
}


avatar
Nam20021608 [community,AAHN-C2009G]
2021-10-10 13:55:34


#Program.cs


using System;
using lap9.Model;
using lap9.DataBase;
using lap9.Utily;
using System.Collections.Generic;
using lap9.View;
namespace lap9
{
    class Program
    {
        static void Main(string[] args)
        {
           BookMenu menu = new BookMenu();
           menu.Menu();
        }
    }
}


#BookMenu.cs


using System;
using lap9.Model;
using lap9.DataBase;
using lap9.Utily;
using System.Collections.Generic;
namespace lap9.View
{
    class BookMenu
    {
        public  void Menu()
        {
           int choose;
           do
           {
               Console.WriteLine("1. Them sach");
               Console.WriteLine("2. Chinh sua thông tin sach");
               Console.WriteLine("3. Hien thi sach ");
               Console.WriteLine("4. Xoa sach");
               Console.WriteLine("5. Thoat");
               choose = Utility.ReadInt();
               switch (choose)
               {
                   case 1: 
                        Insert();
                        break;
                    case 2:
                        Update();
                        break;
                    case 3: 
                        View();
                        break;
                    case 4:
                        Delete();
                        break;
                    case 5:
                        break;
                   default:
                        Console.WriteLine("Nhap lai !");
                        break;
               }
           } while (choose !=5);
        }
        static void View(){
            List<Books> list = BookDAO.GetBooks();

            Console.WriteLine("====== Book list =====");
            foreach(Books std in list)
            {
                std.Display();
            }
        }
        static void Insert(){
            Console.WriteLine("Nhap so sach muon them: ");
            int c = Utility.ReadInt();
            for(int i= 0 ; i< c ; i++){
                Console.WriteLine("=====Them thong tin sach ["+i+1+"]=====");
                Books books = new Books();
                books.Input();
                BookDAO.Insert(books);
            }
        }
         static void Update()
        {
            Console.WriteLine("Nhap ma sach can tin :");
            int id = Utility.ReadInt();
            Books find = BookDAO.FindId(id);
            if(find != null)
            {
                Console.WriteLine("====Sach can tim====");
                find.Display();
                Console.WriteLine("====Nhap thong tin can sua====");
                find.Input();
                BookDAO.Update(find);
                Console.WriteLine("Sua thanh cong !");
            }else
            {
                Console.WriteLine("Khong tim thay sach co id: " + id);
            }
        }
        static void Delete()
        {
            List<Books> bookList = BookDAO.GetBooks();
            Console.WriteLine("Nhap ma sach can xoa :");
            int id = Utility.ReadInt();
            Books find = BookDAO.FindId(id);
            if(find != null)
            {
                foreach(Books b in BookDAO.GetBooks())
                {
                    if (b.id.Equals(id))
                    {
                        Console.WriteLine("Thong tin sach muon xoa :");
                        b.Display();
                        Console.WriteLine("1. Xoa sach");
                        Console.WriteLine("2. Huy xoa sach");
                        int waning = Utility.ReadInt();

                        switch (waning)
                        {
                            case 2: 
                                Console.WriteLine("Huy xoa sach thanh cong ");
                                break;
                            case 1:
                                BookDAO.Delete(id);
                                Console.WriteLine("Xoa thanh cong");
                                break;
                            default:
                                Console.WriteLine("Nhap lai !");
                                break;
                        }
                       
                    }
                }
               
            }else
            {
                Console.WriteLine("No Find Book");
            }
        }
    }
}


#Utility.cs


using System;
namespace lap9.Utily
{
    class Utility{
        public static float ReadFloat()
        {
            float value;
            while(true)
            {
                try
                {
                    value = float.Parse(Console.ReadLine());
                    return value;
                } catch(Exception)
                {
                    Console.WriteLine("Nhap lai!!!");
                }
            }
        }
        public static int ReadInt()
        {
            int value;
            while(true)
            {
                try
                {
                    value = int.Parse(Console.ReadLine());
                    return value;
                } catch(Exception)
                {
                    Console.WriteLine("Nhap lai!!!");
                }
            }
        }
    }
}


#Books.cs


using System;
using lap9.Utily;
namespace lap9.Model
{
    class Books{
        public int id{get; set;}
        public String bookName{get; set;}
        public float price{get; set;}
        public String authorName{get; set;}
        public Books(){}
        public Books(int id , String name , float price , String author){
            this.id = id;
            this.bookName = name;
            this.price = price;
            this.authorName = author;
        }
        public void Input(){
            Console.WriteLine("Nhap Ten sach: ");
            bookName = Console.ReadLine();
            Console.WriteLine("Nhap gia sach: ");
            price = Utility.ReadFloat();
            Console.WriteLine("Nhap tac gia: ");
            authorName = Console.ReadLine();
        }

        public void Display(){
            Console.WriteLine("ID : {0} , BookName: {1} , Gia sach: {2} , Tac gia: {3}",id,bookName,price,authorName);
        }
    }
}


#BookDAO.cs


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

namespace lap9.DataBase
{
    class BookDAO
    {
        public static List<Books> GetBooks(){
            List<Books> list = new List<Books>();
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();

            String sql = " Select * from books";
            MySqlCommand command = new MySqlCommand(sql ,conn);
            MySqlDataReader reader = command.ExecuteReader();
            while(reader.Read()){
                int id = int.Parse(reader["bookid"].ToString());
                String bookname = reader["bookname"].ToString();
                float price = float.Parse(reader["price"].ToString());
                String author = reader["author"].ToString();

                Books b = new Books(id,bookname,price,author);
                list.Add(b);    
            }
            conn.Close();
            return list;
        }

        public static void Insert(Books books){
            MySqlConnection connection = new MySqlConnection(Config.getConnectionString());
            connection.Open();
            String sql = "INSERT INTO books(bookname, price, author) VALUES ( @bookname , @price , @author )";
            MySqlCommand command = new MySqlCommand(sql,connection);
            command.Parameters.AddWithValue("@bookname", books.bookName);
            command.Parameters.AddWithValue("@price",books.price);
            command.Parameters.AddWithValue("@author",books.authorName);
            command.ExecuteNonQuery();
            connection.Close();
        }
        public static Books FindId(int id){
            Books books = null;
            MySqlConnection connection = new MySqlConnection(Config.getConnectionString());
            connection.Open();
            String sql = "SELECT * from books WHERE bookid = @id";
            MySqlCommand command = new MySqlCommand(sql,connection);
            command.Parameters.AddWithValue("@id", id);
            MySqlDataReader reader = command.ExecuteReader();
            while(reader.Read()){
                String bookname = reader["bookname"].ToString();
                float price = float.Parse(reader["price"].ToString());
                String author = reader["author"].ToString();
                books = new Books(id,bookname,price,author);
            }
            connection.Close();
            return books;
           
        }
        public static void Update(Books books){
            MySqlConnection connection = new MySqlConnection(Config.getConnectionString());
            connection.Open();
            String sql = "UPDATE books SET bookname = @bookname , price = @price , author = @author WHERE bookid = @id";
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@bookname", books.bookName);
            command.Parameters.AddWithValue("@price",books.price);
            command.Parameters.AddWithValue("@author",books.authorName);
            command.Parameters.AddWithValue("@id", books.id);
            command.ExecuteNonQuery();
            connection.Close();
        }
          public static void Delete(int id)
        {
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();
            string sql = "DELETE FROM books ";
            sql += "WHERE bookid = @Id";
            MySqlCommand cm = new MySqlCommand(sql, conn);
            cm.Parameters.AddWithValue("@Id", id);
            cm.ExecuteNonQuery();
            conn.Close();
        }

    }
}


#Config.cs


using System;
namespace lap9.DataBase
{
    public class Config
    {
        static string HOST = "localhost";
        static string PORT = "3306";
        static string DATABASE = "book";
        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;
        }
    }
}


#Book.sql


CREATE DATABASE book;
USE book;

CREATE TABLE books(
	bookid INT PRIMARY KEY AUTO_INCREMENT,
	bookname VARCHAR(50) ,
	price FLOAT,
	author VARCHAR(50)
);

INSERT INTO books(bookname, price, author)
VALUES ('Titanic', 100, 'Join wick');


avatar
Nguyễn Việt Hoàng [community,AAHN-C2009G]
2021-10-09 17:38:54


#BookDAO.cs


using Ss9.B2.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace Ss9.B2.Database
{
    class BookDAO
    {
        public static List<Book> GetBookList()
        {
            List<Book> bookList = new List<Book>();
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();
            string sql = "SELECT * FROM Book";
            MySqlCommand cm = new MySqlCommand(sql,conn);
            MySqlDataReader rd = cm.ExecuteReader();
            while (rd.Read())
            {
                int id = int.Parse(rd["Id"].ToString());
                string bookname = rd["BookName"].ToString();
                int price = int.Parse(rd["Price"].ToString());
                string authorname = rd["AuthorName"].ToString();
                Book b = new Book(id,bookname,price,authorname);
                bookList.Add(b);
            }
            conn.Close();
            return bookList;
        }
        public static Book FindById(int id)
        {
            Book b = null;
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();
            string sql = "SELECT * FROM Book ";
                sql += "WHERE Id = @Id";
            MySqlCommand cm = new MySqlCommand(sql, conn);
            cm.Parameters.AddWithValue("@Id", id);
            MySqlDataReader rd = cm.ExecuteReader();
            while (rd.Read())
            {
                string bookname = rd["BookName"].ToString();
                int price = int.Parse(rd["Price"].ToString());
                string authorname = rd["AuthorName"].ToString();
                 b = new Book(id, bookname, price, authorname);
            }
            conn.Close();
            return b;
        }
        public static void Insert(Book b)
        {
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();
            string sql = "INSERT INTO Book(";
            sql += "BookName, ";
            sql += "Price, ";
            sql += "AuthorName)";
            sql += "VALUES(";
            sql += "@BookName, ";
            sql += "@Price, ";
            sql += "@AuthorName";
            sql += ")";
            MySqlCommand cm = new MySqlCommand(sql, conn);
            cm.Parameters.AddWithValue("@BookName", b.BookName);
            cm.Parameters.AddWithValue("@Price", b.Price);
            cm.Parameters.AddWithValue("AuthorName", b.AuthorName);
            cm.ExecuteNonQuery();
            conn.Close();

        }
        public static void Update(Book b)
        {
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();
            string sql = "UPDATE Book SET ";
            sql += "BookName = @BookName, ";
            sql += "Price = @Price, ";
            sql += "AuthorName = @AuthorName ";
            sql += "WHERE Id = @Id";
            
            MySqlCommand cm = new MySqlCommand(sql, conn);
            cm.Parameters.AddWithValue("@BookName", b.BookName);
            cm.Parameters.AddWithValue("@Price", b.Price);
            cm.Parameters.AddWithValue("AuthorName", b.AuthorName);
            cm.Parameters.AddWithValue("@Id", b.Id);
            cm.ExecuteNonQuery();
            conn.Close();

        }
        public static void Delete(int id)
        {
            MySqlConnection conn = new MySqlConnection(Config.getConnectionString());
            conn.Open();
            string sql = "DELETE FROM Book ";
            sql += "WHERE Id = @Id";
            MySqlCommand cm = new MySqlCommand(sql, conn);
            cm.Parameters.AddWithValue("@Id", id);
            cm.ExecuteNonQuery();
            conn.Close();
        }

    }
}



#Config.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ss9.B2.Database
{
    class Config
    {
        static string HOST = "localhost";
        static string PORT = "3306";
        static string DATABASE = "Library";
        static string USERNAME = "root";
        static string PASSWORD = "";
        public static string getConnectionString()
        {
            string conn = String.Format("SERVER={0};PORT={1};DATABASE={2};UID={3};PASSWORD={4};SSL Mode=None",
                                        HOST, PORT, DATABASE, USERNAME, PASSWORD);

            return conn;
        }
    }
}


#Library.sql


CREATE DATABASE IF NOT EXISTS `Library`;
USE `Library`;

CREATE TABLE  `Book`(
`Id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
`BookName` VARCHAR(255) NOT NULL,
`Price` INT NOT NULL,
`AuthorName` VARCHAR(255) NOT NULL
);
INSERT INTO `Book`
VALUES(1,'King Author',12000,'King');


#Book.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ss9.B2.Models
{
    class Book
    {
        public int Id { get; set; }
        public string BookName { get; set; }
        public int Price { get; set; }
        public string AuthorName { get; set; }
        public Book()
        {

        }
        public Book(int id, string bookname, int price, string authorname)
        {
            this.Id = id;
            this.BookName = bookname;
            this.Price = price;
            this.AuthorName = authorname;
        }
        public void Input()
        {
            Console.WriteLine("Enter BookName :");
            BookName = Console.ReadLine();
            Console.WriteLine("Enter Price :");
            Price = int.Parse(Console.ReadLine());
            Console.WriteLine("Enter AuthorName :");
            AuthorName = Console.ReadLine();
        }
        public void Display()
        {
            Console.WriteLine("BookName :{0}, Price :{1}, AuthorName :{2}", BookName, Price, AuthorName);

        }
    }
}


#Mains.cs


using Ss9.B2.Database;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ss9.B2.Models
{
    class Mains
    {
        static void ShowMenu()
        {
            Console.WriteLine("1.Add Books");
            Console.WriteLine("2.Update Books By Id ");
            Console.WriteLine("3.Display Books");
            Console.WriteLine("4.Delete Books");
            Console.WriteLine("5.Exits");
        }
        static void Add()
        {
            Console.WriteLine("Enter Sum Of Books :");
            int sum = int.Parse(Console.ReadLine());
            for(int i = 0; i < sum; i++)
            {
                Console.WriteLine("Books[" + (i + 1) + "]");
                Book b = new Book();
                b.Input();
                BookDAO.Insert(b);
            }
            Console.WriteLine("Add Success");
        }
        static void Update()
        {
            Console.WriteLine("Enter Id Book You Need Fix :");
            int id = int.Parse(Console.ReadLine());
            Book find = BookDAO.FindById(id);
            if(find != null)
            {
                find.Input();
                BookDAO.Update(find);
                Console.WriteLine("Update Success");
            }else
            {
                Console.WriteLine("No Find Book");
            }
        }
        static void Display()
        {
            List<Book> bookList = BookDAO.GetBookList();
            Console.WriteLine("Display Book");
            int i = 0;
            foreach(Book b in bookList)
            {
                i++;
                Console.WriteLine("Book[" + i + "]");
                b.Display();
            }
        }
        static void Delete()
        {
            List<Book> bookList = BookDAO.GetBookList();
            Console.WriteLine("Enter Id You Need Delete :");
            int id = int.Parse(Console.ReadLine());
            Book find = BookDAO.FindById(id);
            if(find != null)
            {
                foreach(Book b in BookDAO.GetBookList())
                {
                    if (b.Id.Equals(id))
                    {
                        Console.WriteLine("Information Books old :");
                        b.Display();
                        BookDAO.Delete(id);
                        Console.WriteLine("Delete Success");
                    }
                }
               
            }else
            {
                Console.WriteLine("No Find Book");
            }
        }

        static void ListMenu()
        {
            int choose;
            do
            {
                ShowMenu();
                Console.WriteLine("Enter choose value :");
                choose = int.Parse(Console.ReadLine());
                switch (choose)
                {
                    case 1:
                        Add();
                        break;
                    case 2:
                        Update();
                        break;
                    case 3:
                        Display();
                        break;
                    case 4:
                        Delete();
                        break;
                    case 5:
                        Console.WriteLine("Exits");
                        return;
                    default:
                        Console.WriteLine("Value must be 1 -> 5");
                        break;
                }
            } while (choose != 5);
        }
        public static void Main(string[] args)
        {
            ListMenu();
        }
    }
}