public int ThemTaiKhoan(TaiKhoan tk)
{
byte[] temp = ASCIIEncoding.ASCII.GetBytes(tk.MatKhau);
byte[] hasData = new MD5CryptoServiceProvider().ComputeHash(temp);
string hasPass = "";
foreach (byte item in hasData)
{
hasPass += item;
}
provider.Connect();
string sql = "sp_ThemTK";
int nRow = provider.ExecuteNonQuery(CommandType.StoredProcedure, sql,
new SqlParameter {
ParameterName = "user", Value = tk.TenDangNhap
},
new SqlParameter {
ParameterName = "pass", Value = hasPass
},
new SqlParameter {
ParameterName = "rule", Value = tk.PhanQuyen
}
);
provider.DisConnect();
return(nRow);
}
public int ThemPhieuMuon(PhieuMuonDTO PM)
{
int nRow = 0;
try
{
string strSQL = "INSERT INTO PhieuMuonSach VALUES(@MaPM,@NgayMuon,@MaDG,@NgayHetHan)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@MaPM", Value = PM.MaPM
},
new SqlParameter {
ParameterName = "@NgayMuon", Value = PM.NgayMuon
},
new SqlParameter {
ParameterName = "@MaDG", Value = PM.MaDG
},
new SqlParameter {
ParameterName = "@NgayHetHan", Value = PM.NgayHetHan
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int ThemPhieuThanhLySach(PhieuThanhLySachDTO phieuThanhLySach)
{
try
{
string strSQL = "INSERT INTO THANHLYSACH VALUES(@MaPhieuThanhLy, @MaNV, @NgayThanhLy)";
provider.Connect();
int Row = provider.ExecuteNonQuery(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@MaPhieuThanhLy", Value = phieuThanhLySach.MaPhieuthanhLy
},
new SqlParameter {
ParameterName = "@MaNV", Value = phieuThanhLySach.MaNV
},
new SqlParameter {
ParameterName = "@NgayThanhLy", Value = phieuThanhLySach.NgayThanhLy
}
);
return(Row);
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public int CapNhatPhieuTra(GhiNhanMatSachDTO gnms)
{
int nRow = 0;
try
{
string strSql = "SP_CapNhatTienPhat";
pro.Connect();
nRow = pro.ExecuteNonQuery(CommandType.StoredProcedure, strSql, new SqlParameter {
ParameterName = "@MaPT", Value = gnms.MaPT
},
new SqlParameter {
ParameterName = "@TienNo", Value = gnms.TienNo
},
new SqlParameter {
ParameterName = "@MaPM", Value = gnms.MaPM
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
pro.DisConnect();
}
return(nRow);
}
public int UpDatePhieuTra(PhieuThuTienPhatDTO pttp)
{
int nRow = 0;
try
{
string strSql = "UPDATE PHIEUTRASACH SET TienNo = @TienNo WHERE PHIEUTRASACH.MaPT=@MaPT";
pro.Connect();
nRow = pro.ExecuteNonQuery(CommandType.Text, strSql, new SqlParameter {
ParameterName = "@MaPT", Value = pttp.MaPT
},
new SqlParameter {
ParameterName = "@TienNo", Value = pttp.TienNo
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
pro.DisConnect();
}
return(nRow);
}
public void ThemVeDatLai(int idKhach, int IdVe)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThemVeDatLai";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IDKH", Value = idKhach
},
new SqlParameter {
ParameterName = "@IDVe", Value = IdVe
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void Update_TuyenKhongTram2(DTO.Tuyen tuyen)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_UpdateTuyenNotTram2";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = tuyen.ID
},
new SqlParameter {
ParameterName = "@KhoangCach", Value = tuyen.KhoangCach
},
new SqlParameter {
ParameterName = "@ThoiGianChay", Value = tuyen.ThoiGian
},
new SqlParameter {
ParameterName = "@IdTram1", Value = tuyen.IDTram1
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void UpdateGiaTien(int GiaTien, int MaChuyen)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_UpdateGiaTien";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IDChuyen", Value = MaChuyen
},
new SqlParameter {
ParameterName = "@giaTien", Value = GiaTien
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThemVe(DTO.Ve ve)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThemVe";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = ve.ID
},
new SqlParameter {
ParameterName = "@IDGhe", Value = ve.IDGhe
},
new SqlParameter {
ParameterName = "@IdChuyen", Value = ve.IDChuyen
},
new SqlParameter {
ParameterName = "@GiaTien", Value = ve.GiaTien
},
new SqlParameter {
ParameterName = "@NgayXuat", Value = ve.NgayXuat
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void SuaQuanLyThanhLySach(string tenQuanLyThanhLySach, string maQuanLyThanhLySach)
{
Provider provider = new Provider();
try
{
provider.Connect();
string query = "UPDATE LyDoThanhLy SET MoTa = @MoTa WHERE MaLyDoThanhLy = @MaLyDoThanhLy";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaLyDoThanhLy", Value = maQuanLyThanhLySach
},
new SqlParameter {
ParameterName = "@MoTa", Value = tenQuanLyThanhLySach
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void Update_Tram(DTO.Tram tram)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_SuaTram";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = tram.ID
},
new SqlParameter {
ParameterName = "@TenTram", Value = tram.TenTram
},
new SqlParameter {
ParameterName = "@DiaDanh", Value = tram.DiaDanh
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void UpdateUser(DTO.Users user)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "UpdateUsers";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@UserID", Value = user.Username1
},
new SqlParameter {
ParameterName = "@Pass", Value = user.Password1
},
new SqlParameter {
ParameterName = "@Email", Value = user.Email1
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThemKhachHang(KhachHang kh)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "Add_KhachHang";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@hoten", Value = kh.HoTen
},
new SqlParameter {
ParameterName = "@dienthoai", Value = kh.SoDienThoai
},
new SqlParameter {
ParameterName = "@email", Value = kh.Email
},
new SqlParameter {
ParameterName = "@loai", Value = kh.Loai
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThemKhachHangDatVe(string hoten, string sdt)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThemKhachHang";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@HoTen", Value = hoten
},
new SqlParameter {
ParameterName = "@SDT", Value = sdt
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void InsertRole(string userID, int iD)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "AddRole";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@UserID", Value = userID
},
new SqlParameter {
ParameterName = "@ID", Value = iD
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void InsertXe(DTO.Xe xe)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThemXe";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = xe.ID
},
new SqlParameter {
ParameterName = "@TenXe", Value = xe.TenXe
},
new SqlParameter {
ParameterName = "@SoDangKy", Value = xe.SoDangKy
},
new SqlParameter {
ParameterName = "@IDLoai", Value = xe.IdLoai
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThemQuanLyThanhLySach(string tenQuanLyThanhLySach, string maQuanLyThanhLySach)
{
Provider provider = new Provider();
try
{
provider.Connect();
string query = "INSERT INTO LyDoThanhLy VALUES(@MaQuanLyThanhLySach, @LoaiQuanLyThanhLySach)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaQuanLyThanhLySach", Value = maQuanLyThanhLySach
},
new SqlParameter {
ParameterName = "@LoaiQuanLyThanhLySach", Value = tenQuanLyThanhLySach
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public static int AddANewBook(SachDTO book)
{
int nRow = 0;
try
{
string strSql = "INSERT INTO SACH(MaSach, TenSach, MaTheLoai, MaTacGia, TTS, MaNXB, NgayXuatBan, GiaTien, Ke, NgayNhap, MaNVTiepNhan)" +
" VALUES(@Ma, @Ten, @MaTheLoai, @MaTacGia, @TTS, @MaNXB, @NgayXuatBan, @GiaTien, @Ke, @NgayNhap, @MaNVTN)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@Ma", Value = book.Ma
},
new SqlParameter {
ParameterName = "@Ten", Value = book.Ten
},
new SqlParameter {
ParameterName = "@MaTheLoai", Value = book.MaTheLoai
},
new SqlParameter {
ParameterName = "@MaTacGia", Value = book.MaTacGia
},
new SqlParameter {
ParameterName = "@TTS", Value = "TTS02"
},
new SqlParameter {
ParameterName = "@MaNXB", Value = book.MaMXB
},
new SqlParameter {
ParameterName = "@NgayXuatBan", Value = book.NgayXuatBan
},
new SqlParameter {
ParameterName = "@Giatien", Value = book.GiaTien
},
new SqlParameter {
ParameterName = "@Ke", Value = book.ViTri
},
new SqlParameter {
ParameterName = "@NgayNhap", Value = book.NgayTiepNhan
},
new SqlParameter {
ParameterName = "@MaNVTN", Value = book.MaNVTiepNhan
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int ThemPhieuTra(PhieuTraDTO PT)
{
int nRow = 0;
try
{
string strSQL = "INSERT INTO PhieuTraSach VALUES(@MaPT,@NgayTra,@MaPM,@TienPhatKyNay,@TienNo,@SoNgayTraTre)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@MaPT", Value = PT.MaPT
},
new SqlParameter {
ParameterName = "@MaPM", Value = PT.MaPM
},
new SqlParameter {
ParameterName = "@NgayTra", Value = PT.NgayTra
},
new SqlParameter {
ParameterName = "@TienPhatKyNay", Value = PT.TienPhatKyNay
},
new SqlParameter {
ParameterName = "@TienNo", Value = PT.TienNo
},
new SqlParameter {
ParameterName = "@SoNgayTraTre", Value = PT.SoNgayTraTre
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public void ThemTheLoai(string maTheLoai, string tenTheLoai)
{
try
{
provider.Connect();
string query = "INSERT INTO TheLoaiSach VALUES(@MaTheLoai, @TenTheLoai)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaTheLoai", Value = maTheLoai
},
new SqlParameter {
ParameterName = "@TenTheLoai", Value = tenTheLoai
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void ThemChucVu(string tenChucVu, string maChucVu)
{
try
{
provider.Connect();
string query = "INSERT INTO ChucVu VALUES(@MaChucVu, @TenChucVu)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaChucVu", Value = maChucVu
},
new SqlParameter {
ParameterName = "@TenChucVu", Value = tenChucVu
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void ThemBoPhan(string tenBoPhan, string maBoPhan)
{
try
{
provider.Connect();
string query = "INSERT INTO BoPhan VALUES(@MaBoPhan, @TenBoPhan)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaBoPhan", Value = maBoPhan
},
new SqlParameter {
ParameterName = "@TenBoPhan", Value = tenBoPhan
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void ThemTacGia(string maTacGia, string tenTacGia)
{
try
{
provider.Connect();
string query = "INSERT INTO TACGIA VALUES(@MaTacGia, @TenTacGia)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaTacGia", Value = maTacGia
},
new SqlParameter {
ParameterName = "@TenTacGia", Value = tenTacGia
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void ThemBangCap(string tenBangCap, string maBangCap)
{
try
{
provider.Connect();
string query = "INSERT INTO BangCap VALUES(@MaBangCap, @LoaiBangCap)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaBangCap", Value = maBangCap
},
new SqlParameter {
ParameterName = "@LoaiBangCap", Value = tenBangCap
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void ThemNXB(string maNXB, string tenNXB)
{
try
{
provider.Connect();
string query = "INSERT INTO NHAXUATBAN " +
"VALUES(@MaNXB, @TenNXB)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaNXB", Value = maNXB
},
new SqlParameter {
ParameterName = "@TenNXB", Value = tenNXB
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void ThemNhanVien(NhanVienDTO nhanVien)
{
try
{
provider.Connect();
string query = "INSERT INTO NhanVien(MaNhanVien, HoTenNV, DiaChi, NgaySinh, DienThoai, MaBangCap, MaChucVu, MaBoPhan,MaTTNV)" +
" VALUES(@MaNhanVien, @HoTenNV, @DiaChi, @NgaySinh, @DienThoai, @MaBangCap, @MaChucVu, @MaBoPhan, @MaTTNV)";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaNhanVien", Value = nhanVien.MaNhanVien
},
new SqlParameter {
ParameterName = "@HoTenNV", Value = nhanVien.HoTenNV
},
new SqlParameter {
ParameterName = "@DiaChi", Value = nhanVien.DiaChi
},
new SqlParameter {
ParameterName = "@NgaySinh", Value = nhanVien.NgaySinh
},
new SqlParameter {
ParameterName = "@DienThoai", Value = nhanVien.DienThoai
},
new SqlParameter {
ParameterName = "@MaBangCap", Value = nhanVien.BangCap
},
new SqlParameter {
ParameterName = "@MaChucVu", Value = nhanVien.ChucVu
},
new SqlParameter {
ParameterName = "@MaBoPhan", Value = nhanVien.BoPhan
},
new SqlParameter {
ParameterName = "@MaTTNV", Value = nhanVien.MaTTNV
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void RollBAck()
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "SP_ROOLBACK";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void UpdateChuyen(DTO.Chuyen chuyen)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_UpdateChuyen";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = chuyen.ID
},
new SqlParameter {
ParameterName = "@IdTuyen", Value = chuyen.IDTuyen
},
new SqlParameter {
ParameterName = "@NgayKhoiHanh", Value = chuyen.NgayKhoiHanh
},
new SqlParameter {
ParameterName = "@GioKhoiHanh", Value = chuyen.GioKhoiHanh
},
new SqlParameter {
ParameterName = "@GhiChu", Value = chuyen.GhiChi
},
new SqlParameter {
ParameterName = "@IdXe", Value = chuyen.IdXe
},
new SqlParameter {
ParameterName = "@IDTaiXe", Value = chuyen.IDTaiXe
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThanhToan(int idKhach)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThanhToan";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IdKH", Value = idKhach
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void RollBackVeDat(int IDVe)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "SP_ROOLBACKVeDat";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IDVe", Value = IDVe
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}