public static The_DTO layTTThe(string maThe)
{
SqlConnection conn = SQLConn.connect();
conn.Open();
The_DTO the = new The_DTO();
the.MaThe = maThe;
SqlCommand cmd = new SqlCommand("Select NgayCapThe from The where MaThe = '" + maThe + "'", conn);
string chuoiNgayCap = cmd.ExecuteScalar().ToString();
DateTime ngayCap = new DateTime();
DateTime.TryParse(chuoiNgayCap, out ngayCap);
the.NgayCapThe = ngayCap;
SqlCommand cmd1 = new SqlCommand("Select NgayHetHan from The where MaThe = '" + maThe + "'", conn);
string chuoiNgayHetHan = cmd1.ExecuteScalar().ToString();
DateTime ngayHetHan = new DateTime();
DateTime.TryParse(chuoiNgayHetHan, out ngayHetHan);
the.NgayHetHan = ngayHetHan;
SqlCommand cmd2 = new SqlCommand("Select SoSachDuocMuon from The where MaThe = '" + maThe + "'", conn);
the.SoSachDuocMuon = Convert.ToInt32(cmd2.ExecuteScalar().ToString());
SqlCommand cmd3 = new SqlCommand("Select SoSachDangMuon from The where MaThe = '" + maThe + "'", conn);
the.SoSachDangMuon = Convert.ToInt32(cmd3.ExecuteScalar().ToString());
conn.Close();
return(the);
}
public static DocGia_DTO layTTDocGia(string maDocGia)
{
SqlConnection conn = SQLConn.connect();
DocGia_DTO docGia = new DocGia_DTO();
conn.Open();
docGia.MaDocGia = maDocGia;
SqlCommand cmd = new SqlCommand("Select TenDocGia from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.TenDocGia = cmd.ExecuteScalar().ToString();
SqlCommand cmd1 = new SqlCommand("Select GioiTinh from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.GioiTinh = cmd1.ExecuteScalar().ToString();
SqlCommand cmd2 = new SqlCommand("Select NgaySinh from DocGia where MaDocGia = '" + maDocGia + "'", conn);
string chuoiNgaySinh = cmd2.ExecuteScalar().ToString();
DateTime ngaySinh = new DateTime();
DateTime.TryParse(chuoiNgaySinh, out ngaySinh);
docGia.NgaySinh = ngaySinh;
SqlCommand cmd3 = new SqlCommand("Select DiaChi from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.DiaChi = cmd3.ExecuteScalar().ToString();
SqlCommand cmd4 = new SqlCommand("Select SoCMT from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.SoCMT = cmd4.ExecuteScalar().ToString();
SqlCommand cmd5 = new SqlCommand("Select MaLoaiDocGia from DocGia where MaDocGia = '" + maDocGia + "'", conn);
docGia.MaLoaiDocGia = cmd5.ExecuteScalar().ToString();
conn.Close();
return(docGia);
}
public static DataTable loadPhieuMuonChoDuyetTra()
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_loadPhieuMuonChoDuyetTra", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static void yeuCauTraSach(int soPhieu)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_yeucautrasach", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sophieu", SqlDbType.Int).Value = soPhieu;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static void xoaUser(string maDG)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_xoauser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@uid", SqlDbType.NVarChar).Value = maDG;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static void xoaThe(string maThe)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_xoathe1", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mathe", SqlDbType.VarChar).Value = maThe;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static void tuChoiPhieuMuon(int soPhieu)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_tuchoiphieumuon", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sophieu", SqlDbType.Int).Value = soPhieu;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static void doiMK(string taiKhoan, string mkMoi)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_doimk", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tendangnhap", SqlDbType.NVarChar).Value = taiKhoan;
cmd.Parameters.Add("@matkhau", SqlDbType.NVarChar).Value = mkMoi;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
//tao moi tai khoan
public static void taoMoiTK(string tenDN, string mk)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_taomoitk", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tendangnhap", SqlDbType.NVarChar).Value = tenDN;
cmd.Parameters.Add("@matkhau", SqlDbType.NVarChar).Value = mk;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static DataTable loadDSThe()
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_loadTatCaThe", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static int demSoPhieuMuonTreHan()
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_demSoPhieuMuonTreHan", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
int result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return(result);
}
public static string layTenLoaiDocGia(string maDocGia)
{
SqlConnection conn = SQLConn.connect();
string tenLoaiDocGia = "";
conn.Open();
SqlCommand cmd5 = new SqlCommand("Select TenLoaiDocGia from LoaiDocGia where MaLoaiDocGia = (Select MaLoaiDocGia from DocGia where MaDocGia = '" + maDocGia + "')", conn);
tenLoaiDocGia = cmd5.ExecuteScalar().ToString();
conn.Close();
return(tenLoaiDocGia);
}
public static void suaTL(TheLoai_DTO theLoai)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_suatheloai", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@matheloai", SqlDbType.VarChar).Value = theLoai.MaTheLoai;
cmd.Parameters.Add("@tentheloai", SqlDbType.NVarChar).Value = theLoai.TenTheLoai;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
// lay ten sach
public static string layTenSach(string maSach)
{
string tenSach = "";
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_layTenSach", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@masach", SqlDbType.VarChar).Value = maSach;
conn.Open();
tenSach = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
return(tenSach);
}
public static DataTable timKiemPhieuMuonDaTraTheoSoPhieu(int soPhieu)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_timKiemPhieuMuonDaTraTheoSoPhieu", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sophieu", SqlDbType.Int).Value = soPhieu;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static DataTable timKiemPhieuMuonTheoTinhTrang(string tinhTrang)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_timKiemPhieuMuonTheoTinhTrang", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tinhtrang", SqlDbType.NVarChar).Value = tinhTrang;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static DataTable timKiemPhieuMuonDaTraTheoMaSach(string maSach)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_timKiemPhieuMuonDaTraTheoMaSach", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@masach", SqlDbType.VarChar).Value = maSach;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static void suaNXB(NXB_DTO nXB)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_suanxb", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@manxb", SqlDbType.VarChar).Value = nXB.MaNXB;
cmd.Parameters.Add("@tennxb", SqlDbType.NVarChar).Value = nXB.TenNXB;
cmd.Parameters.Add("@ghichu", SqlDbType.NVarChar).Value = nXB.GhiChu;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static DataTable timKiemTheoNXB(string tenNXB)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_timKiemTheoNXB", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tennxb", SqlDbType.NVarChar).Value = tenNXB;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
// dem so phieu muon theo ma the (phieu dang cho phe duyet)
public static int demSoPhieuMuonTheoMaThe(string maThe)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_demSoPhieuMuonTheoMaThe", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mathe", SqlDbType.VarChar).Value = maThe;
conn.Open();
int soPhieuMuonChoDuyet = Convert.ToInt16(cmd.ExecuteScalar());
conn.Close();
return(soPhieuMuonChoDuyet);
}
public static string layMaTheTheoMaDocGia(string maDocGia)
{
string maThe = "";
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_layMaTheTheoMaDG", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@madocgia", SqlDbType.VarChar).Value = maDocGia;
conn.Open();
maThe = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
return(maThe);
}
// dem so the theo ten
public static int demSoTheTheoTen(string maDocGia)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_demSoTheTheoTen", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@madocgia", SqlDbType.VarChar).Value = maDocGia;
conn.Open();
int soTheTheoTen = Convert.ToInt16(cmd.ExecuteScalar());
conn.Close();
return(soTheTheoTen);
}
public static void suaUser(string tenDN, string mK, int type)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_suauser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@uid", SqlDbType.NVarChar).Value = tenDN;
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = mK;
cmd.Parameters.Add("@type", SqlDbType.Int).Value = type;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static void duyetPhieuMuon(PhieuMuon_DTO phieuMuon)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_duyetphieumuon", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sophieu", SqlDbType.Int).Value = phieuMuon.SoPhieu;
cmd.Parameters.Add("@mathe", SqlDbType.VarChar).Value = phieuMuon.MaThe;
cmd.Parameters.Add("@masach", SqlDbType.VarChar).Value = phieuMuon.MaSach;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static DataTable timKiemTheoNamXB(int namXB)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_timKiemTheoNamXB", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@namxb", SqlDbType.Int).Value = namXB;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static DataTable loadDSSachDangMuonTheoMaThe(string maThe)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_loadDSSachDangMuonTheoMaThe", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mathe", SqlDbType.VarChar).Value = maThe;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static void suaTG(TacGia_DTO tacGia)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_suaTG", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@matacgia", SqlDbType.VarChar).Value = tacGia.MaTacGia;
cmd.Parameters.Add("@tentacgia", SqlDbType.NVarChar).Value = tacGia.TenTacGia;
cmd.Parameters.Add("@ghichu", SqlDbType.NVarChar).Value = tacGia.GhiChu;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public static int laySoSachDangMuon(string maThe)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_laySoSachDangMuon", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mathe", SqlDbType.VarChar).Value = maThe;
conn.Open();
int soSachDangMuon = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return(soSachDangMuon);
}
public static void duyetYeuCauTraSach(int soPhieu, string maThe, string maSach)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd3 = new SqlCommand("sp_duyetyeucautrasach", conn);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.Add("@sophieu", SqlDbType.Int).Value = soPhieu;
cmd3.Parameters.Add("@mathe", SqlDbType.VarChar).Value = maThe;
cmd3.Parameters.Add("@masach", SqlDbType.VarChar).Value = maSach;
conn.Open();
cmd3.ExecuteNonQuery();
conn.Close();
}
public static void suaPhieuMuonDangCho(PhieuMuon_DTO phieuMuon)
{
SqlConnection conn = SQLConn.connect();
SqlCommand cmd = new SqlCommand("sp_suaphieumuondangcho", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sophieu", SqlDbType.Int).Value = phieuMuon.SoPhieu;
cmd.Parameters.Add("@ngaymuon", SqlDbType.Date).Value = phieuMuon.NgayMuon;
cmd.Parameters.Add("@ngaytra", SqlDbType.Date).Value = phieuMuon.NgayTra;
cmd.Parameters.Add("@ghichu", SqlDbType.NVarChar).Value = phieuMuon.GhiChu;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}