public static List <CoQuanDTO> LoadcbCQ()
{
// Khai báo truy vấn.
string sQuery = @"SELECT * FROM CoQuan";
// Mở kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list LopDTO
List <CoQuanDTO> lstCQ = new List <CoQuanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CoQuanDTO cq = new CoQuanDTO();
cq.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
cq.STenCQ = dt.Rows[i]["TenCQ"].ToString();
lstCQ.Add(cq);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstCQ);
}
public static List <VanThuDTO> TimKiemvanThu(string key)
{
string sQuery = string.Format("SELECT * FROM VanThu WHERE TieuDe like N'%{0}%'", key);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <VanThuDTO> lstvt = new List <VanThuDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
VanThuDTO vt = new VanThuDTO();
vt.IMaVT = int.Parse(dt.Rows[i]["MaVT"].ToString());
vt.STieuDe = dt.Rows[i]["TieuDe"].ToString();
vt.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
vt.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
lstvt.Add(vt);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstvt);
}
// Load Cơ Quan
public static List <CoQuanDTO> LoadCoQuan()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM CoQuan";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <CoQuanDTO> lstcq = new List <CoQuanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CoQuanDTO cq = new CoQuanDTO();
cq.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
cq.STenCQ = dt.Rows[i]["TenCQ"].ToString();
cq.SDiaChi = dt.Rows[i]["DiaChi"].ToString();
cq.ISDT = int.Parse(dt.Rows[i]["SDT"].ToString());
cq.SNguoiDungDau = dt.Rows[i]["NguoiDungDau"].ToString();
lstcq.Add(cq);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstcq);
}
public static List <VanThuDTO> LoadVanThu()
{
string sQuery = @"SELECT *
FROM VanThu vt
INNER JOIN CoQuan cq
ON cq.MaCQ = vt.MaCQ
INNER JOIN LoaiCongVan lcv
ON lcv.MaLCV = vt.MaLCV" ;
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <VanThuDTO> lstvt = new List <VanThuDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
VanThuDTO vt = new VanThuDTO();
vt.IMaVT = int.Parse(dt.Rows[i]["MaVT"].ToString());
vt.STieuDe = dt.Rows[i]["TieuDe"].ToString();
vt.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
vt.STenCQ = dt.Rows[i]["TenCQ"].ToString();
vt.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
vt.STenLCV = dt.Rows[i]["TenLCV"].ToString();
lstvt.Add(vt);
}
DataProvider.CloseConnect(con);
return(lstvt);
}
public static List <NienKhoaDTO> LoadcbNienKhoa()
{
string sQuery = @"SELECT * FROM NienKhoa";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list Niên Khóa
List <NienKhoaDTO> lstNienKhoa = new List <NienKhoaDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
NienKhoaDTO nienkhoa = new NienKhoaDTO();
nienkhoa.IMaNienKhoa = int.Parse(dt.Rows[i]["MaNienKhoa"].ToString());
nienkhoa.STenNienKhoa = dt.Rows[i]["TenNienKhoa"].ToString();
//nienkhoa.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lstNienKhoa.Add(nienkhoa);
}
DataProvider.CloseConnect(con);
return(lstNienKhoa);
}
public static List <MonHocDTO> LoadMonHoc()
{
string sQuery = string.Format(
@"SELECT * FROM MonHoc mh
INNER JOIN HocKy hk
ON hk.MaHK = mh.MaHK
INNER JOIN Khoi k
ON k.MaKhoi = mh.MaKhoi"
);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <MonHocDTO> lstmh = new List <MonHocDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
MonHocDTO monhocDTO = new MonHocDTO();
monhocDTO.IMaMH = int.Parse(dt.Rows[i]["MaMH"].ToString());
monhocDTO.STenMH = dt.Rows[i]["TenMH"].ToString();
monhocDTO.ISoTiet = int.Parse(dt.Rows[i]["SoTiet"].ToString());
monhocDTO.IMaHK = int.Parse(dt.Rows[i]["MaHK"].ToString());
monhocDTO.STenHK = dt.Rows[i]["TenHK"].ToString();
monhocDTO.IMaKhoi = int.Parse(dt.Rows[i]["MaKhoi"].ToString());
monhocDTO.STenKhoi = dt.Rows[i]["TenKhoi"].ToString();
lstmh.Add(monhocDTO);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstmh);
}
// Load Sinh Vien
public static List <HocSinhDTO> LoadSinhVien()
{
// Khai báo truy vấn
string sQuery = "SELECT * FROM HocSinh, Lop WHERE HocSinh.MaLop = Lop.MaLop";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <HocSinhDTO> lstsv = new List <HocSinhDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
HocSinhDTO sv = new HocSinhDTO();
sv.IMaHS = int.Parse(dt.Rows[i]["MaHS"].ToString());
sv.SHoTen = dt.Rows[i]["Hoten"].ToString();
sv.SGioiTinh = dt.Rows[i]["GioiTinh"].ToString();
sv.DtNgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
sv.SDiaChi = dt.Rows[i]["DiaChi"].ToString();
sv.IMaLop = int.Parse(dt.Rows[i]["MaLop"].ToString());
sv.STenLop = dt.Rows[i]["TenLop"].ToString();
lstsv.Add(sv);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstsv);
}
public static List <DonViNhanDTO> LoadDonViNhan()
{
string sQuery = @"SELECT * FROM DonViNhan, CoQuan WHERE DonViNhan.MaCQ = CoQuan.MaCQ";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DonViNhanDTO> lstdvn = new List <DonViNhanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DonViNhanDTO dvn = new DonViNhanDTO();
dvn.IMaDVN = int.Parse(dt.Rows[i]["MaDVN"].ToString());
dvn.STenDVN = dt.Rows[i]["TenDVN"].ToString();
dvn.SDiaChi = dt.Rows[i]["DiaChi"].ToString();
dvn.ISDT = int.Parse(dt.Rows[i]["SDT"].ToString());
dvn.DtNgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
dvn.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
dvn.STenCQ = dt.Rows[i]["TenCQ"].ToString();
lstdvn.Add(dvn);
}
DataProvider.CloseConnect(con);
return(lstdvn);
}
/// <summary>
/// Load combobox
/// </summary>
/// <returns> list Lớp</returns>
public static List <LopDTO> LoadcbLop()
{
// Khai báo truy vấn.
string sQuery = string.Format(@"SELECT * FROM Lop");
// Mở kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list LopDTO
List <LopDTO> lstLop = new List <LopDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
LopDTO lop = new LopDTO();
lop.IMaLop = int.Parse(dt.Rows[i]["MaLop"].ToString());
lop.STenLop = dt.Rows[i]["TenLop"].ToString();
//lop.ISiSo = int.Parse(dt.Rows[i]["SiSo"].ToString());
lstLop.Add(lop);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstLop);
}
public static List <NienKhoaDTO> LoadNienKhoa()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM NienKhoa ";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <NienKhoaDTO> lstnienkhoa = new List <NienKhoaDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
NienKhoaDTO nienkhoa = new NienKhoaDTO();
nienkhoa.IMaNienKhoa = int.Parse(dt.Rows[i]["MaNienKhoa"].ToString());
nienkhoa.STenNienKhoa = dt.Rows[i]["TenNienKhoa"].ToString();
nienkhoa.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lstnienkhoa.Add(nienkhoa);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstnienkhoa);
}
public static List <PhieuGuiDTO> LoadPhieuGui()
{
string sQuery = @"SELECT * FROM PhieuGui, CoQuan WHERE PhieuGui.MaCQ = CoQuan.MaCQ";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <PhieuGuiDTO> lstpg = new List <PhieuGuiDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
PhieuGuiDTO pg = new PhieuGuiDTO();
pg.IMaPG = int.Parse(dt.Rows[i]["MaPG"].ToString());
pg.DtNgayGui = DateTime.Parse(dt.Rows[i]["NgayGui"].ToString());
pg.SNguoiGui = dt.Rows[i]["NguoiGui"].ToString();
pg.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
pg.STenCQ = dt.Rows[i]["TenCQ"].ToString();
lstpg.Add(pg);
}
DataProvider.CloseConnect(con);
return(lstpg);
}
public static List <PhieuNhanDTO> LoadPhieuNhan()
{
string sQuery = @"SELECT * FROM PhieuNhan, DonViNhan WHERE PhieuNhan.MaDVN = DonViNhan.MaDVN";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <PhieuNhanDTO> lstpn = new List <PhieuNhanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
PhieuNhanDTO pn = new PhieuNhanDTO();
pn.IMaPN = int.Parse(dt.Rows[i]["MaPN"].ToString());
pn.DtNgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
pn.SNguoiNhan = dt.Rows[i]["NguoiNhan"].ToString();
pn.IMaDVN = int.Parse(dt.Rows[i]["MaDVN"].ToString());
pn.STenDVN = dt.Rows[i]["TenDVN"].ToString();
lstpn.Add(pn);
}
DataProvider.CloseConnect(con);
return(lstpn);
}
public static List <LoaiCongVanDTO> LoadLoaiCongVan()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM LoaiCongVan";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <LoaiCongVanDTO> lstlcv = new List <LoaiCongVanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
LoaiCongVanDTO lcv = new LoaiCongVanDTO();
lcv.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
lcv.STenLCV = dt.Rows[i]["TenLCV"].ToString();
lstlcv.Add(lcv);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstlcv);
}
public static bool XoaKhoi(KhoiDTO khoiDTO)
{
string sQuery = string.Format(@"DELETE Khoi WHERE MaKhoi={0}", khoiDTO.IMaKhoi);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool ThemKhoi(KhoiDTO khoiDTO)
{
string sQuery = string.Format(@"INSERT INTO Khoi VALUES (N'{0}', N'{1}')", khoiDTO.STenKhoi, khoiDTO.SGhiChu);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool SuaKhoi(KhoiDTO khoiDTO)
{
string sQuery = string.Format(@"UPDATE Khoi SET TenKhoi=N'{0}', GhiChu=N'{1}' WHERE MaKhoi={2}", khoiDTO.STenKhoi, khoiDTO.SGhiChu, khoiDTO.IMaKhoi);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static List <KhoiDTO> LoadcbKhoi()
{
string sQuery = string.Format("SELECT * FROM Khoi");
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
List <KhoiDTO> lstk = new List <KhoiDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
KhoiDTO khoiDTO = new KhoiDTO();
khoiDTO.IMaKhoi = int.Parse(dt.Rows[i]["MaKhoi"].ToString());
khoiDTO.STenKhoi = dt.Rows[i]["TenKhoi"].ToString();
//khoiDTO.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lstk.Add(khoiDTO);
}
DataProvider.CloseConnect(con);
return(lstk);
}
public static List <MonHocDTO> LoadcbMonHoc()
{
string sQuery = string.Format(@"SELECT * FROM MonHoc");
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <MonHocDTO> lstmh = new List <MonHocDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
MonHocDTO monhocDTO = new MonHocDTO();
monhocDTO.IMaMH = int.Parse(dt.Rows[i]["MaMH"].ToString());
monhocDTO.STenMH = dt.Rows[i]["TenMH"].ToString();
lstmh.Add(monhocDTO);
}
DataProvider.CloseConnect(con);
return(lstmh);
}
public static List <DonViNhanDTO> LoadcbDVN()
{
string sQuery = @"SELECT * FROM DonViNhan";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DonViNhanDTO> lstDVN = new List <DonViNhanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DonViNhanDTO dvn = new DonViNhanDTO();
dvn.IMaDVN = int.Parse(dt.Rows[i]["MaDVN"].ToString());
dvn.STenDVN = dt.Rows[i]["TenDVN"].ToString();
lstDVN.Add(dvn);
}
DataProvider.CloseConnect(con);
return(lstDVN);
}
// Load combobox LoaiCongVan
public static List <LoaiCongVanDTO> LoadcbLCV()
{
string sQuery = @"SELECT * FROM LoaiCongVan";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <LoaiCongVanDTO> lstCVN = new List <LoaiCongVanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
LoaiCongVanDTO lcv = new LoaiCongVanDTO();
lcv.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
lcv.STenLCV = dt.Rows[i]["TenLCV"].ToString();
lstCVN.Add(lcv);
}
DataProvider.CloseConnect(con);
return(lstCVN);
}
public static List <LopDTO> LoadLop()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM Lop l
INNER JOIN NienKhoa nk
ON nk.MaNienKhoa = l.MaNienKhoa
INNER JOIN Khoi k
ON k.MaKhoi = l.MaKhoi";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <LopDTO> lstlop = new List <LopDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
LopDTO lop = new LopDTO();
lop.IMaLop = int.Parse(dt.Rows[i]["MaLop"].ToString());
lop.STenLop = dt.Rows[i]["TenLop"].ToString();
lop.ISiSo = int.Parse(dt.Rows[i]["SiSo"].ToString());
lop.IMaNienKhoa = int.Parse(dt.Rows[i]["MaNienKhoa"].ToString());
lop.STenNienKhoa = dt.Rows[i]["TenNienKhoa"].ToString();
lop.IMaKhoi = int.Parse(dt.Rows[i]["MaKhoi"].ToString());
lop.STenKhoi = dt.Rows[i]["TenKhoi"].ToString();
lstlop.Add(lop);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstlop);
}
public static List <HocKyDTO> LoadcbHocKy()
{
string sQuery = @"SELECT * FROM HocKy";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <HocKyDTO> lsthk = new List <HocKyDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
HocKyDTO hk = new HocKyDTO();
hk.IMaHK = int.Parse(dt.Rows[i]["MaHK"].ToString());
hk.STenHK = dt.Rows[i]["TenHK"].ToString();
hk.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lsthk.Add(hk);
}
DataProvider.CloseConnect(con);
return(lsthk);
}
public static List <KhoiDTO> LoadKhoi()
{
string sQuery = @"SELECT * FROM Khoi ";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <KhoiDTO> lstk = new List <KhoiDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
KhoiDTO k = new KhoiDTO();
k.IMaKhoi = int.Parse(dt.Rows[i]["MaKhoi"].ToString());
k.STenKhoi = dt.Rows[i]["TenKhoi"].ToString();
k.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lstk.Add(k);
}
DataProvider.CloseConnect(con);
return(lstk);
}
