public static void Insert(KhachHangDTO KhachHang)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Insert into KHACHHANG( HoTen, DiaChi, DienThoai, Email, Facebook, TKNganHang, NgayThem, NguoiThem) values (?,?,?,?,?,?,?,?)";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@HoTen", OleDbType.VarWChar);
command.Parameters.Add("@DiaChi", OleDbType.VarWChar);
command.Parameters.Add("@DienThoai", OleDbType.VarWChar);
command.Parameters.Add("@Email", OleDbType.VarWChar);
command.Parameters.Add("@Facebook", OleDbType.VarWChar);
command.Parameters.Add("@TKNganHang", OleDbType.VarWChar);
command.Parameters.Add("@NgayThem", OleDbType.VarWChar);
command.Parameters.Add("@NguoiThem", OleDbType.VarWChar);
command.Parameters["@HoTen"].Value = KhachHang.HoTen;
command.Parameters["@DiaChi"].Value = KhachHang.DiaChi;
command.Parameters["@DienThoai"].Value = KhachHang.DienThoai;
command.Parameters["@Email"].Value = KhachHang.Email;
command.Parameters["@Facebook"].Value = KhachHang.Facebook;
command.Parameters["@TKNganHang"].Value = KhachHang.TKNganHang;
command.Parameters["@NgayThem"].Value = KhachHang.NgayThem;
command.Parameters["@NguoiThem"].Value = KhachHang.NguoiThem;
command.ExecuteNonQuery();
command.CommandText = "Select @@IDENTITY";
KhachHang.MaKhachHang = (int)command.ExecuteScalar();
connection.Close();
}
public void GetKhachHang(KhachHangDTO KhachHangDTO)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from KHACHHANG where MaKhachHang =" + KhachHangDTO.MaKhachHang;
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
KhachHangDTO.HoTen = (string)reader["HoTen"];
KhachHangDTO.DiaChi = (string)reader["DiaChi"];
KhachHangDTO.DienThoai = (string)reader["DienThoai"];
KhachHangDTO.Email = (string)reader["Email"];
KhachHangDTO.Facebook = (string)reader["Facebook"];
KhachHangDTO.TKNganHang = (string)reader["TKNganHang"];
if (reader["DiemTichLuy"].ToString() == "")
{
KhachHangDTO.DiemTichLuy = 0;
}
else
{
KhachHangDTO.DiemTichLuy = (int)reader["DiemTichLuy"];
}
}
reader.Close();
connection.Close();
}
public TheLoaiDTO LayTenTheLoai(int maTL)
{
TheLoaiDTO tl = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from THELOAI where MaLoai = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaLoai", OleDbType.Integer);
command.Parameters["@MaLoai"].Value = maTL;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
tl = new TheLoaiDTO();
tl.MaLoai = (int)reader["MaLoai"];
tl.TenTheLoai = (string)reader["TenTheLoai"];
}
reader.Close();
connection.Close();
return(tl);
}
public void Insert(FbGroupDTO fbGroup)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Insert into FBGROUP(GroupId, GroupName, NumberOfMember, NgayNhap, NguoiNhap) values(?,?,?,?,?)";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@GroupId", OleDbType.VarWChar);
command.Parameters["@GroupId"].Value = fbGroup.GroupId;
command.Parameters.Add("@GroupName", OleDbType.VarWChar);
command.Parameters["@GroupName"].Value = fbGroup.GroupName;
command.Parameters.Add("@NumberOfMember", OleDbType.Integer);
command.Parameters["@NumberOfMember"].Value = fbGroup.NumberOfMember;
command.Parameters.Add("@NgayNhap", OleDbType.Date);
command.Parameters["@NgayNhap"].Value = fbGroup.NgayNhap;
command.Parameters.Add("@NguoiNhap", OleDbType.WChar);
command.Parameters["@NguoiNhap"].Value = fbGroup.NguoiNhap;
command.ExecuteNonQuery();
connection.Close();
}
public static IList GetList()
{
ArrayList arrList = new ArrayList();
GiangVienDTO giangVienDTO = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from GIANGVIEN";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
giangVienDTO = new GiangVienDTO();
giangVienDTO.MaGiangVien = (int)reader["MaGiangVien"];
giangVienDTO.TenGiangVien = (string)reader["TenGiangVien"];
giangVienDTO.DiaChi = (string)reader["DiaChi"];
giangVienDTO.SoDienThoai = (string)reader["SoDienThoai"];
giangVienDTO.Email = (string)reader["Email"];
arrList.Add(giangVienDTO);
}
reader.Close();
connection.Close();
return(arrList);
}
public NgonNguDTO LayTenNgonNgu(int maNN)
{
NgonNguDTO nn = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from NGONNGU where MaNgonNgu = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaNgonNgu", OleDbType.Integer);
command.Parameters["@MaNgonNgu"].Value = maNN;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
nn = new NgonNguDTO();
nn.MaNgonNgu = (int)reader["MaNgonNgu"];
nn.TenNgonNgu = (string)reader["TenNgonNgu"];
}
reader.Close();
connection.Close();
return(nn);
}
public NhanVienDTO Search(string Username)
{
NhanVienDTO NhanVien = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from NHANVIEN where Username = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@Username", OleDbType.WChar);
command.Parameters["@Username"].Value = Username;
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
NhanVien = new NhanVienDTO();
NhanVien.Username = (string)reader["Username"];
NhanVien.Password = (string)reader["Password"];
NhanVien.HoTen = (string)reader["HoTen"];
NhanVien.DiaChi = (string)reader["DiaChi"];
NhanVien.DienThoai = (string)reader["DienThoai"];
NhanVien.Email = (string)reader["Email"];
NhanVien.Loai = (int)reader["Loai"];
}
reader.Close();
connection.Close();
return(NhanVien);
}
public NhaXuatBanDTO LayTenNhaXuatBan(int maNXB)
{
NhaXuatBanDTO nxb = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from NHAXUATBAN where MaNXB = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaNXB", OleDbType.Integer);
command.Parameters["@MaNXB"].Value = maNXB;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
nxb = new NhaXuatBanDTO();
nxb.MaNXB = (int)reader["MaNXB"];
nxb.TenNXB = (string)reader["TenNXB"];
}
reader.Close();
connection.Close();
return(nxb);
}
public void Update(SachDTO sachDTO)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Update SACH Set [TenSach] = ? , [MaTG] = ? , [MaNXB] = ? , [MaLoai] = ? , [SoTrang] = ? , [TrangThai] = ? , [MaNN] = ? Where [MaSach] = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@TenSach", OleDbType.WChar);
command.Parameters.Add("@MaTG", OleDbType.Integer);
command.Parameters.Add("@MaNXB", OleDbType.Integer);
command.Parameters.Add("@MaLoai", OleDbType.Integer);
command.Parameters.Add("@SoTrang", OleDbType.Integer);
command.Parameters.Add("@TrangThai", OleDbType.Integer);
command.Parameters.Add("@MaNN", OleDbType.Integer);
command.Parameters.Add("@MaSach", OleDbType.Integer);
command.Parameters["@TenSach"].Value = sachDTO.TenSach;
command.Parameters["@MaTG"].Value = sachDTO.MaTacGia;
command.Parameters["@MaNXB"].Value = sachDTO.MaNXB;
command.Parameters["@MaLoai"].Value = sachDTO.MaTheLoai;
command.Parameters["@SoTrang"].Value = sachDTO.SoTrang;
command.Parameters["@TrangThai"].Value = sachDTO.TrangThai;
command.Parameters["@MaNN"].Value = sachDTO.MaNgonNgu;
command.Parameters["@MaSach"].Value = sachDTO.MaSach;
command.ExecuteNonQuery();
connection.Close();
}
public SachDTO Search_MaSach(int maSach)
{
SachDTO sachDTO = null;
DataTable dataTable = new DataTable();
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from SACH where MaSach = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaSach", OleDbType.Integer);
command.Parameters["@MaSach"].Value = maSach;
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
sachDTO = new SachDTO();
sachDTO.TenSach = (string)reader["TenSach"];
sachDTO.MaNXB = (int)reader["MaNXB"];
sachDTO.MaTacGia = (int)reader["MaTG"];
sachDTO.MaTheLoai = (int)reader["MaLoai"];
sachDTO.MaNgonNgu = (int)reader["MaNN"];
sachDTO.SoTrang = (int)reader["SoTrang"];
sachDTO.TrangThai = (int)reader["TrangThai"];
}
reader.Close();
connection.Close();
return(sachDTO);
}
public static GiangVienDTO TimTheoMaGiangVien(int maGiangVien)
{
ArrayList arrList = new ArrayList();
GiangVienDTO giangVienDTO = new GiangVienDTO();
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from GIANGVIEN where MaGiangVien = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaGiangVien", OleDbType.Integer);
command.Parameters["@MaGiangVien"].Value = maGiangVien;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
giangVienDTO.MaGiangVien = (int)reader["MaGiangVien"];
giangVienDTO.TenGiangVien = (string)reader["TenGiangVien"];
giangVienDTO.DiaChi = (string)reader["DiaChi"];
giangVienDTO.SoDienThoai = (string)reader["SoDienThoai"];
giangVienDTO.Email = (string)reader["Email"];
arrList.Add(giangVienDTO);
}
reader.Close();
connection.Close();
return(giangVienDTO);
}
public static IList GetList()
{
ArrayList arrList = new ArrayList();
DocGiaDTO DocGia;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from DOCGIA";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocGia = new DocGiaDTO();
DocGia.MaDocGia = (int)reader["MaDG"];
DocGia.HoTen = (string)reader["HoTen"];
DocGia.DiaChi = (string)reader["DiaChi"];
DocGia.DienThoai = (string)reader["DienThoai"];
arrList.Add(DocGia);
}
reader.Close();
connection.Close();
return(arrList);
}
public void UpdateKhachHang(KhachHangDTO khachhangDTO)
{
OleDbConnection cn = DataProvider.CreateConnection();
string strSql = "Update KHACHHANG set HoTen = ?, DiemTichLuy = ?, DiaChi = ?, DienThoai = ?, Email = ?, Facebook = ?, TKNganHang = ?, NgayCapNhat = ?, NguoiCapNhat = ? where MaKhachHang=" + khachhangDTO.MaKhachHang;
OleDbCommand cmd = new OleDbCommand(strSql, cn);
cmd.Parameters.Add("@HoTen", OleDbType.VarWChar);
cmd.Parameters.Add("@DiemTichLuy", OleDbType.Integer);
cmd.Parameters.Add("@DiaChi", OleDbType.VarWChar);
cmd.Parameters.Add("@DienThoai", OleDbType.VarWChar);
cmd.Parameters.Add("@Email", OleDbType.VarWChar);
cmd.Parameters.Add("@Facebook", OleDbType.VarWChar);
cmd.Parameters.Add("@TKNganHang", OleDbType.VarWChar);
cmd.Parameters.Add("@NgayCapNhat", OleDbType.Date);
cmd.Parameters.Add("@NguoiCapNhat", OleDbType.WChar);
cmd.Parameters["@HoTen"].Value = khachhangDTO.HoTen;
cmd.Parameters["@DiemTichLuy"].Value = khachhangDTO.DiemTichLuy;
cmd.Parameters["@DiaChi"].Value = khachhangDTO.DiaChi;
cmd.Parameters["@DienThoai"].Value = khachhangDTO.DienThoai;
cmd.Parameters["@Email"].Value = khachhangDTO.Email;
cmd.Parameters["@Facebook"].Value = khachhangDTO.Facebook;
cmd.Parameters["@TKNganHang"].Value = khachhangDTO.TKNganHang;
cmd.Parameters["@NgayCapNhat"].Value = khachhangDTO.NgayCapNhat;
cmd.Parameters["@NguoiCapNhat"].Value = khachhangDTO.NguoiCapNhat;
cmd.ExecuteNonQuery();
cn.Close();
}
public static LopDTO TimLopTheoMaLop(int maLop)
{
ArrayList arrList = new ArrayList();
LopDTO lop = new LopDTO();
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from LOP where MaLop = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaLop", OleDbType.Integer);
command.Parameters["@MaLop"].Value = maLop;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
lop.MaLop = (int)reader["MaLop"];
lop.TenLop = (string)reader["TenLop"];
lop.SoLuongSinhVien = (int)reader["SoLuongSinhVien"];
lop.SoLuongTrongNganSach = (int)reader["SoLuongTrongNganSach"];
lop.SoLuongNgoaiNganSach = (int)reader["SoLuongNgoaiNganSach"];
lop.MaNamHoc = (int)reader["MaNamHoc"];
}
reader.Close();
connection.Close();
return(lop);
}
//public static IList GetList()
//{
// ArrayList arrList = new ArrayList();
// DonHangDTO DonHang = null;
// OleDbConnection connection = DataProvider.CreateConnection();
// string cmdText = "Select * from DONHANG";
// OleDbCommand command = new OleDbCommand(cmdText, connection);
// OleDbDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// DonHang = new DonHangDTO();
// DonHang.MaDocGia = (int)reader["MaDocGia"];
// DonHang.MaPhieuMuon = (int)reader["MaPhieuMuon"];
// DonHang.NgayMuon = (DateTime)reader["NgayMuon"];
// arrList.Add(DonHang);
// }
// reader.Close();
// connection.Close();
// return arrList;
//}
//public static void UpdateTable(DataTable dataTable)
//{
// OleDbConnection connection = DataProvider.CreateConnection();
// string cmdText = "Select * from DONHANG";
// OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, connection);
// OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);
// adapter.Update(dataTable);
// connection.Close();
//}
public static void Insert(DonHangDTO DonHang)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Insert into DONHANG(MaKhachHang, NgayDatHang, TrangThai, NguoiNhap, PhiVanChuyen, TongTien, SoLuongSanPham, HinhThucMua) values(?,?,?,?,?,?,?,?)";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaKhachHang", OleDbType.Integer);
command.Parameters.Add("@NgayDatHang", OleDbType.Date);
command.Parameters.Add("@TrangThai", OleDbType.Integer);
command.Parameters.Add("@NguoiNhap", OleDbType.WChar);
command.Parameters.Add("@PhiVanChuyen", OleDbType.Integer);
command.Parameters.Add("@TongTien", OleDbType.Integer);
command.Parameters.Add("@SoLuongSanPham", OleDbType.Integer);
command.Parameters.Add("@HinhThucMua", OleDbType.Integer);
command.Parameters["@MaKhachHang"].Value = DonHang.MaKhachHang;
command.Parameters["@NgayDatHang"].Value = DonHang.NgayDatHang;
command.Parameters["@TrangThai"].Value = DonHang.TrangThai;
command.Parameters["@NguoiNhap"].Value = DonHang.NguoiNhap;
command.Parameters["@PhiVanChuyen"].Value = DonHang.PhiVanChuyen;
command.Parameters["@TongTien"].Value = DonHang.TongTien;
command.Parameters["@SoLuongSanPham"].Value = DonHang.SoLuongSanPham;
command.Parameters["@HinhThucMua"].Value = DonHang.HinhThucMua;
command.ExecuteNonQuery();
command.CommandText = "select @@IDENTITY";
DonHang.MaDonHang = (int)command.ExecuteScalar();
connection.Close();
}
public static NamHocDTO GetRecord(int maNamHoc)
{
ArrayList arrList = new ArrayList();
NamHocDTO namHocDTO = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from NAMHOC where MaNamHoc = ? order by TenNamHoc DESC";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaNamHoc", OleDbType.Integer);
command.Parameters["@MaNamHoc"].Value = maNamHoc;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
namHocDTO = new NamHocDTO();
namHocDTO.MaNamHoc = (int)reader["MaNamHoc"];
namHocDTO.TenNamHoc = (string)reader["TenNamHoc"];
arrList.Add(namHocDTO);
}
reader.Close();
connection.Close();
return(namHocDTO);
}
public static IList GetList()
{
ArrayList arrList = new ArrayList();
LopDTO lop = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from LOP";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
lop = new LopDTO();
lop.MaLop = (int)reader["MaLop"];
lop.TenLop = (string)reader["TenLop"];
lop.SoLuongSinhVien = (long)reader["SoLuongSinhVien"];
lop.SoLuongTrongNganSach = (long)reader["SoLuongTrongNganSach"];
lop.SoLuongNgoaiNganSach = (long)reader["SoLuongNgoaiNganSach"];
lop.MaNamHoc = (int)reader["MaNamHoc"];
arrList.Add(lop);
}
reader.Close();
connection.Close();
return(arrList);
}
public DanhMucDTO LayMaDanhMuc(string tenDM)
{
DanhMucDTO DANHMUC = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from DANHMUC where TenDanhMuc like ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@TenDanhMuc", OleDbType.VarWChar);
command.Parameters["@TenDanhMuc"].Value = tenDM;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DANHMUC = new DanhMucDTO();
DANHMUC.MaDanhMuc = (int)reader["MaDanhMuc"];
DANHMUC.TenDanhMuc = (string)reader["TenDanhMuc"];
}
reader.Close();
connection.Close();
return(DANHMUC);
}
public static ChiTietMuonDTO Search(int MaPhieuMuon)
{
ChiTietMuonDTO CTMuon = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from CHITIETMUON where MaPhieuMuon = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaPhieuMuon", OleDbType.Integer);
command.Parameters["@MaPhieuMuon"].Value = MaPhieuMuon;
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
CTMuon = new ChiTietMuonDTO();
CTMuon.MaPhieuMuon = (int)reader["MaPhieuMuon"];
CTMuon.MaSach = (int)reader["MaSach"];
CTMuon.NgayTra = (DateTime)reader["NgayTra"];
}
reader.Close();
connection.Close();
return(CTMuon);
}
public QuanThuDTO Search(string Username)
{
QuanThuDTO QuanThu = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from QUANTHU where Username = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@Username", OleDbType.WChar);
command.Parameters["@Username"].Value = Username;
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
QuanThu = new QuanThuDTO();
QuanThu.Username = (string)reader["Username"];
QuanThu.Password = (string)reader["Password"];
QuanThu.HoTen = (string)reader["HoTen"];
QuanThu.DiaChi = (string)reader["DiaChi"];
QuanThu.DienThoai = (string)reader["DienThoai"];
QuanThu.Loai = (int)reader["Loai"];
}
reader.Close();
connection.Close();
return(QuanThu);
}
public void Insert(QuanThuDTO QuanThu)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Insert into QUANTHU([Username], [Password], [HoTen], [DiaChi], [DienThoai], [Loai]) values (?, ?, ?, ?, ?, ?)";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@Username", OleDbType.VarWChar);
command.Parameters.Add("@Password", OleDbType.VarWChar);
command.Parameters.Add("@HoTen", OleDbType.VarWChar);
command.Parameters.Add("@DiaChi", OleDbType.VarWChar);
command.Parameters.Add("@DienThoai", OleDbType.VarWChar);
command.Parameters.Add("@Loai", OleDbType.Integer);
command.Parameters["@Username"].Value = QuanThu.Username;
command.Parameters["@Password"].Value = QuanThu.Password;
command.Parameters["@HoTen"].Value = QuanThu.HoTen;
command.Parameters["@DiaChi"].Value = QuanThu.DiaChi;
command.Parameters["@DienThoai"].Value = QuanThu.DienThoai;
command.Parameters["@Loai"].Value = QuanThu.Loai;
command.ExecuteNonQuery();
/*command.CommandText = "Select @@IDENTITY";
* QuanThu.MaDocGia = (int)command.ExecuteScalar();*/
connection.Close();
}
public static IList GetList()
{
ArrayList arrList = new ArrayList();
SachDTO Sach = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from SACH";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Sach = new SachDTO();
Sach.MaSach = (int)reader["MaSach"];
Sach.TenSach = (string)reader["TenSach"];
Sach.MaTacGia = (int)reader["MaTG"];
Sach.MaNXB = (int)reader["MaNXB"];
Sach.MaTheLoai = (int)reader["MaLoai"];
Sach.NgayNhap = (DateTime)reader["NgayNhap"];
Sach.SoTrang = (int)reader["SoTrang"];
Sach.TrangThai = (int)reader["TrangThai"];
Sach.MaNgonNgu = (int)reader["MaNN"];
arrList.Add(Sach);
}
reader.Close();
connection.Close();
return(arrList);
}
public static DocGiaDTO Search(int MaDG)
{
DocGiaDTO DocGia = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select * from DOCGIA where MaDG = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaDG", OleDbType.Integer);
command.Parameters["@MaDG"].Value = MaDG;
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
DocGia = new DocGiaDTO();
DocGia.MaDocGia = (int)reader["MaDG"];
DocGia.HoTen = (string)reader["HoTen"];
DocGia.DiaChi = (string)reader["DiaChi"];
DocGia.DienThoai = (string)reader["DienThoai"];
}
reader.Close();
connection.Close();
return(DocGia);
}
public static IList GetList(string data)
{
ArrayList arrList = new ArrayList();
KhachHangDTO KhachHang;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select MaKhachHang, HoTen from KHACHHANG where HoTen like '%" + data + "%' " + "or DiaChi like '%" + data + "%' " + "or DienThoai like '%" + data + "%' " + "or Email like '%" + data + "%' " + "or Facebook like '%" + data + "%' " + "or TKNganHang like '%" + data + "%' ";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
KhachHang = new KhachHangDTO();
KhachHang.MaKhachHang = (int)reader["MaKhachHang"];
KhachHang.HoTen = (string)reader["HoTen"];
//KhachHang.DiaChi = (string)reader["DiaChi"];
//KhachHang.DienThoai = (string)reader["DienThoai"];
//KhachHang.Email = (string)reader["Email"];
//KhachHang.Facebook = (string)reader["Facebook"];
//KhachHang.TKNganHang = (string)reader["TKNganHang"];
arrList.Add(KhachHang);
}
reader.Close();
connection.Close();
return(arrList);
}
//public static bool Delete(int MaPhieuMuon)
//{
// bool result = false;
// OleDbConnection connection = DataProvider.CreateConnection();
// string cmdText = "delete from DONHANG where MaPhieuMuon = ?";
// OleDbCommand command = new OleDbCommand(cmdText, connection);
// command.Parameters.Add("@MaPhieuMuon", OleDbType.Integer);
// command.Parameters["@MaPhieuMuon"].Value = MaPhieuMuon;
// int row = command.ExecuteNonQuery();
// if (row > 0)
// {
// result = true;
// }
// connection.Close();
// return result;
//}
public static void Update(DonHangDTO dhDTO)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Update DONHANG Set DiemTichLuyCongThem = ?, DiemTichLuySuDung = ?, TrangThai = ?, NguoiCapNhat = ?, NgayCapNhat = ?, PhiVanChuyen = ?, TongTien = ?, SoLuongSanPham = ?, HinhThucMua = ? Where MaDonHang = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@DiemTichLuyCongThem", OleDbType.Integer);
command.Parameters.Add("@DiemTichLuySuDung", OleDbType.Integer);
command.Parameters.Add("@TrangThai", OleDbType.Integer);
command.Parameters.Add("@NguoiCapNhat", OleDbType.WChar);
command.Parameters.Add("@NgayCapNhat", OleDbType.Date);
command.Parameters.Add("@PhiVanChuyen", OleDbType.Integer);
command.Parameters.Add("@TongTien", OleDbType.Integer);
command.Parameters.Add("@SoLuongSanPham", OleDbType.Integer);
command.Parameters.Add("@HinhThucMua", OleDbType.Integer);
command.Parameters.Add("@MaDonHang", OleDbType.Integer);
command.Parameters["@DiemTichLuyCongThem"].Value = dhDTO.DiemTichLuyCongThem;
command.Parameters["@DiemTichLuySuDung"].Value = dhDTO.DiemTichLuySuDung;
command.Parameters["@TrangThai"].Value = dhDTO.TrangThai;
command.Parameters["@NguoiCapNhat"].Value = dhDTO.NguoiCapNhat;
command.Parameters["@NgayCapNhat"].Value = dhDTO.NgayCapNhat;
command.Parameters["@PhiVanChuyen"].Value = dhDTO.PhiVanChuyen;
command.Parameters["@TongTien"].Value = dhDTO.TongTien;
command.Parameters["@SoLuongSanPham"].Value = dhDTO.SoLuongSanPham;
command.Parameters["@HinhThucMua"].Value = dhDTO.HinhThucMua;
command.Parameters["@MaDonHang"].Value = dhDTO.MaDonHang;
command.ExecuteNonQuery();
connection.Close();
}
public TacGiaDTO LayTenTacGia(int maTG)
{
TacGiaDTO TACGIA = null;
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from TACGIA where MaTG = ?";
OleDbCommand command = new OleDbCommand(cmdText, connection);
command.Parameters.Add("@MaTG", OleDbType.Integer);
command.Parameters["@MaTG"].Value = maTG;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TACGIA = new TacGiaDTO();
TACGIA.MaTG = (int)reader["MaTG"];
TACGIA.TenTG = (string)reader["TenTG"];
}
reader.Close();
connection.Close();
return(TACGIA);
}
public static DataTable Search_DTO(SachDTO sach, bool NgonNguChk, bool TheLoaiChk)
{
DataTable dataTable = new DataTable();
OleDbConnection connection = DataProvider.CreateConnection();
OleDbCommand command = BuildQuery(sach, NgonNguChk, TheLoaiChk, connection);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dataTable);
return(dataTable);
}
public static void UpdateTable(DataTable datatable)
{
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "Select * from CHITIETMUON";
OleDbDataAdapter adpter = new OleDbDataAdapter(cmdText, connection);
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adpter);
adpter.Update(datatable);
connection.Close();
}
public static DataTable TimKiemTheoMaSanPham(string maSp)
{
DataTable dataTable = new DataTable();
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select dh.MaDonHang, kh.HoTen, dh.SoLuongSanPham, dh.PhiVanChuyen, dh.TongTien, dh.TrangThai, dh.NgayDatHang, dh.NguoiNhap, dh.NgayCapNhat, dh.NguoiCapNhat from DONHANG dh, KHACHHANG kh, CHITIETDONHANG ctdh where dh.MaKhachHang = kh.MaKhachHang and dh.MaDonHang = ctdh.MaDonHang and ctdh.MaSanPham like '%" + maSp + "%' order by dh.NgayDatHang DESC";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dataTable);
return(dataTable);
}
public static DataTable TimKiemTheoThongTinKhachHang(string data)
{
DataTable dataTable = new DataTable();
OleDbConnection connection = DataProvider.CreateConnection();
string cmdText = "select dh.MaDonHang, kh.HoTen, dh.SoLuongSanPham, dh.PhiVanChuyen, dh.TongTien, dh.TrangThai, dh.NgayDatHang, dh.NguoiNhap, dh.NgayCapNhat, dh.NguoiCapNhat from DONHANG dh inner join KHACHHANG kh on dh.MaKhachHang = kh.MaKhachHang where kh.HoTen like '%" + data + "%' " + "or kh.DiaChi like '%" + data + "%' " + "or kh.DienThoai like '%" + data + "%' " + "or kh.Email like '%" + data + "%' " + "or kh.Facebook like '%" + data + "%' " + "or kh.TKNganHang like '%" + data + "%' order by dh.NgayDatHang DESC";
OleDbCommand command = new OleDbCommand(cmdText, connection);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dataTable);
return(dataTable);
}
