4. Tạo lớp kết nối và truy vấn dữ liệu: DataLayer

- Tạo thư mục DataLayer

- Tạo lớp hỗ trợ kết nối dữ liệu DataAccessHelper

- Tên file: DataAccessHelper.cs, dạng Class Library.

- Nội dung coding:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace BookShop.DataLayer

{

    class DataAccessHelper

    {

        #region 1. Khai báo các thành viên dữ liệu

        SqlConnection conn;

        SqlCommand cmd;

        #endregion

        #region 2. Các phương thức khởi tạo

        public DataAccessHelper()

        {

            conn = new SqlConnection(@"Server=.\SQLEXPRESS; Database=BookShopDB; Integrated Security = True;");

        }

        #endregion

        #region 3. Các phương thức thao tác với CSDL

        ///

        /// Phương thức mở kết nối tới CSDL

        ///

        private void Open()

        {

            if (conn.State == System.Data.ConnectionState.Closed)

                conn.Open();

        }

        ///

        /// Phương thức ngắt kết nối với CSDL

        ///

        private void Close()

        {

            if (conn.State == System.Data.ConnectionState.Open)

                conn.Close();

        }

      

        ///

        /// Phương thức lấy về một giá trị từ mệnh đề SELECT

        ///

        /// Câu lệnh SELECT

        /// Một đối tượng

        public object GetValue(string query)

        {

            Open();

            cmd = new SqlCommand(query, conn);

            return cmd.ExecuteScalar();

        }

        public DataTable GetDateTable(string select)

        {

            SqlDataAdapter da = new SqlDataAdapter(select, conn);

            DataTable dt = new DataTable();

            da.Fill(dt);

            return dt;

        }

        public int ExecuteNonQuery(string query)

        {

            Open();

            cmd = new SqlCommand(query, conn);

            return cmd.ExecuteNonQuery();

        }

        #endregion

    }

}

- Tạo lớp kết nối dữ liệu sách BookDAL

- Nội dung coding BookDAL.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using BookShop.Entities;

using System.Data;

namespace BookShop.DataLayer

{

    class BookDAL

    {

        DataAccessHelper db = new DataAccessHelper();

        public Book GetBookByID(int id)

        {

            Book bk = new Book();

            List<Book> list = GetList();

            foreach(Book b in list)

                if (b.BookID == id)

                {

                    bk = b;

                    break;

                }

            return bk;

        }

        public static List<Book> GetList()

        {

            DataAccessHelper db = new DataAccessHelper();

            DataTable dt = db.GetDateTable("SELECT * FROM Books");

            List<Book> list = new List<Book>();

            foreach (DataRow row in dt.Rows)

            {

                list.Add(new Book(row));

            }

            return list;

        }

        public static List<Book> GetListByName(string keyword)

        {

            DataAccessHelper db = new DataAccessHelper();

            DataTable dt = db.GetDateTable("SELECT * FROM Books WHERE BookID like '%"+keyword+"%' or BookName LIKE '%" + keyword + "%'");

            List<Book> list = new List<Book>();

            foreach (DataRow row in dt.Rows)

            {

                list.Add(new Book(row));

            }

            return list;

        }

        public bool Insert(Book bk)

        {

            int count = db.ExecuteNonQuery("INSERT INTO Books VALUES(N'" + bk.BookName + "',N'" + bk.Author + "'," + bk.Amount + "," + bk.Price + ")");

            return count > 0;

        }

        public bool Update(Book bk)

        {

            int count = db.ExecuteNonQuery("UPDATE Books SET BookName = N'" + bk.BookName + "',Author = N'" + bk.Author + "',Amount=" + bk.Amount + ",Price=" + bk.Price + " WHERE BookID = "+bk.BookID);

            return count > 0;

        }

        public bool Delete(Book bk)

        {

            int count = db.ExecuteNonQuery("DELETE Books WHERE BookID=" + bk.BookID);

            return count > 0;

        }

    }

}

- Tạo lớp kết nối OrderDAL

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using BookShop.Entities;

using System.Data;

namespace BookShop.DataLayer

{

    class OrderDAL

    {

        DataAccessHelper db = new DataAccessHelper();

        public List<Order> GetListOrders()

        {

                   

            DataTable dt = db.GetDateTable("SELECT * FROM Orders ORDER BY OrderID DESC");

            List<Order> list = new List<Order>();

            foreach (DataRow row in dt.Rows)

            {

                list.Add(new Order(row));

            }

            return list;

      

        }

        public List<Order> GetListOrderByDate(DateTime orderDate)

        {

            DataTable dt = db.GetDateTable("SELECT * FROM Orders WHERE CONVERT(date, OrderDate) ='" + orderDate.ToString("yyyy-MM-dd") + "' ORDER BY OrderID DESC");

            List<Order> list = new List<Order>();

            foreach (DataRow row in dt.Rows)

            {

                list.Add(new Order(row));

            }

            return list;

        }

        public bool Insert(Order od)

        {

            try

            {

                db.ExecuteNonQuery("INSERT INTO Orders VALUES(N'" + od.CustomerName + "','" + od.OrderDate.ToString("yyyy-MM-dd hh:mm:ss tt") + "'," + od.Total + ")");

                int id = (int)db.GetValue("SELECT MAX(OrderID) FROM Orders");

                foreach (OrderDetail d in od.ListOrderDetail)

                {

                    db.ExecuteNonQuery("INSERT INTO OrderDetails VALUES(" + id + "," + d.BookID + "," + d.Quantity + "," + d.Price + "," + d.TotalPrice + ")");

                }

                return true;

            }

            catch

            {

                return false;

            }

        }

    }

}