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
///
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;
}
}
}
}
