public System.Data.Linq.Table <Mon> LayMon()
{
DataSet ds = new DataSet();
QLSVDataContext qlSV = new QLSVDataContext();
return(qlSV.Mons);
}
public System.Data.Linq.Table <Login> LayLogin()
{
DataSet ds = new DataSet();
QLSVDataContext qlSV = new QLSVDataContext();
return(qlSV.Logins);
}
public System.Data.Linq.Table <KetQua> LayDiem()
{
DataSet ds = new DataSet();
QLSVDataContext qlSV = new QLSVDataContext();
return(qlSV.KetQuas);
}
private void Loaddgv()
{
using (QLSVDataContext _db = new QLSVDataContext())
{
dgv1.DataSource = Bang_SINHVIEN;
}
}
public void DeleteDiem(string MaSV, string maMon, string dgk, string dck)
{
QLSVDataContext qLSV = new QLSVDataContext();
string sqlString = string.Format("Delete From KetQua Where maSV=N'{0}' and maMon=N'{1}' and diemGiuaKi={2} and diemCuoiKi={3}", MaSV, maMon, dgk, dck);
qLSV.ExecuteQuery <KetQua>(sqlString);
}
public System.Data.Linq.Table <SinhVien> LaySinhVien()
{
DataSet ds = new DataSet();
QLSVDataContext qlSV = new QLSVDataContext();
return(qlSV.SinhViens);
}
public System.Data.Linq.Table <Khoa> LayKhoa()
{
DataSet ds = new DataSet();
QLSVDataContext qlSV = new QLSVDataContext();
return(qlSV.Khoas);
}
public void UpdateDiem(string maSV, string maMon, string dgkc, string dckc, string dgk, string dck, string dtb, string kq)
{
QLSVDataContext qLSV = new QLSVDataContext();
string sqlString =
string.Format("Update KetQua SET diemGiuaKi='{4}',diemCuoiKi='{5}',diemTB='{6}',ketQua='{7}' where maSV=N'{0}' and maMon=N'{1}' and diemGiuaKi='{2}' and diemCuoiKi='{3}'",
maSV, maMon, dgkc, dckc, dgk, dck, dtb, kq);
qLSV.ExecuteQuery <KetQua>(sqlString);
}
public void InsertDiem(string maSV, string maMon, string dgk, string dck, string dtb, string kq)
{
QLSVDataContext qLSV = new QLSVDataContext();
string sqlString =
string.Format("Insert KetQua (maSV,maMon,diemGiuaKi,diemCuoiKi,diemTB,ketQua)" +
"VALUES(N'{0}',N'{1}',{2},{3},{4},N'{5}')", maSV, maMon, dgk, dck, dtb, kq);
qLSV.ExecuteQuery <KetQua>(sqlString);
}
public List <Lop> LayKhoaInLop(string MaKhoa)
{
QLSVDataContext qLSV = new QLSVDataContext();
var tpQuery = (from tp in qLSV.Lops
where tp.maKhoa == MaKhoa
select tp).ToList();
return(tpQuery);
}
public List <Lop> TimTenLop(string TenLop)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.Lops
where tp.tenLop.Contains(TenLop)
select tp).ToList();
return(tpQuery);
}
public List <SinhVien> TimTenSinhVien(string HoTen)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.SinhViens
where tp.hoTen.Contains(HoTen)
select tp).ToList();
return(tpQuery);
}
public List <Khoa> TimTenKhoa(string TenKhoa)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.Khoas
where tp.tenKhoa.Contains(TenKhoa)
select tp).ToList();
return(tpQuery);
}
private void LoadCBOLop()
{
using (QLSVDataContext _db = new QLSVDataContext())
{
cmblop.DataSource = Bang_LOP;
cmblop.DisplayMember = "TenLop";
cmblop.ValueMember = "MaLop";
}
}
public List <Login> LoginMember(string userName, string passWord)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.Logins
where tp.userName == userName && tp.passWord == passWord && tp.Quyen == "Member"
select tp).ToList();
return(tpQuery);
}
public List <Mon> TimTenMon(string TenMon)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.Mons
where tp.tenMon.Contains(TenMon)
select tp).ToList();
return(tpQuery);
}
public List <SinhVien> TimMaSinhVien(string MaSV)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.SinhViens
where tp.maSV.Contains(MaSV)
select tp).ToList();
return(tpQuery);
}
public bool XoaKhoa(ref string err, string MaKhoa)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = from tp in qlSV.Khoas
where tp.maKhoa == MaKhoa
select tp;
qlSV.Khoas.DeleteAllOnSubmit(tpQuery);
qlSV.SubmitChanges();
return(true);
}
public bool XoaSinhVien(ref string err, string MaSV)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = from tp in qlSV.SinhViens
where tp.maSV == MaSV
select tp;
qlSV.SinhViens.DeleteAllOnSubmit(tpQuery);
qlSV.SubmitChanges();
return(true);
}
public bool ThemKhoa(string MaKhoa, string TenKhoa, ref string err)
{
QLSVDataContext qlSV = new QLSVDataContext();
Khoa kh = new Khoa();
kh.maKhoa = MaKhoa;
kh.tenKhoa = TenKhoa;
qlSV.Khoas.InsertOnSubmit(kh);
qlSV.Khoas.Context.SubmitChanges();
return(true);
}
public bool XoaAc(ref string err, string user)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = from tp in qlSV.Logins
where tp.userName == user
select tp;
qlSV.Logins.DeleteAllOnSubmit(tpQuery);
qlSV.SubmitChanges();
return(true);
}
public bool XoaDiem(ref string err, string MaSV, string MaMon)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = from tp in qlSV.KetQuas
where tp.maSV == MaSV && tp.maMon == MaMon
select tp;
qlSV.KetQuas.DeleteAllOnSubmit(tpQuery);
qlSV.SubmitChanges();
return(true);
}
public List <SinhVien> LaySinhVienTrongKhoaLop(string MaKhoa, string MaLop)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.SinhViens
from vp in qlSV.Lops
where vp.maKhoa.Contains(MaKhoa) &&
tp.maLop.Contains(MaLop) && tp.maLop == vp.maLop
select tp).ToList();
return(tpQuery);
}
public List <Mon> LoadMonTrongMaLop(string MaLop)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.Lops
from vp in qlSV.Khoas
from up in qlSV.Mons
where vp.maKhoa == tp.maKhoa &&
tp.maLop == MaLop && vp.maKhoa == up.maKhoa
select up).ToList();
return(tpQuery);
}
public bool ThemLop(string MaLop, string TenLop, string MaKhoa, ref string err)
{
QLSVDataContext qlSV = new QLSVDataContext();
Lop kh = new Lop();
kh.maLop = MaLop;
kh.tenLop = TenLop;
kh.maKhoa = MaKhoa;
qlSV.Lops.InsertOnSubmit(kh);
qlSV.Lops.Context.SubmitChanges();
return(true);
}
public bool ChangePassAccount(string username, string pass, string newpass, string comfimMK, string quyen)
{
if (quyen == "Member")
{
if (LoginMember(username, pass).Count > 0)
{
if (newpass == comfimMK)
{
QLSVDataContext qlSV = new QLSVDataContext();
var query = (from tp in qlSV.Logins
where tp.userName == username
select tp);
foreach (var item in query)
{
item.passWord = newpass;
}
qlSV.SubmitChanges();
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
else //admin
{
if (newpass == comfimMK)
{
QLSVDataContext qlSV = new QLSVDataContext();
var query = (from tp in qlSV.Logins
where tp.userName == username
select tp);
foreach (var item in query)
{
item.passWord = newpass;
}
qlSV.SubmitChanges();
return(true);
}
else
{
return(false);
}
}
}
public bool ThemMon(string MaMon, string TenMon, string MaKhoa, int SoTinChi, string HK, ref string err)
{
QLSVDataContext qlSV = new QLSVDataContext();
Mon kh = new Mon();
kh.maMon = MaMon;
kh.tenMon = TenMon;
kh.maKhoa = MaKhoa;
kh.soTinChi = SoTinChi;
kh.hocKi = HK;
qlSV.Mons.InsertOnSubmit(kh);
qlSV.Mons.Context.SubmitChanges();
return(true);
}
public bool CapNhatKhoa(string MaKhoa, string TenKhoa, ref string err)
{
QLSVDataContext qlSV = new QLSVDataContext();
var tpQuery = (from tp in qlSV.Khoas
where tp.maKhoa == MaKhoa
select tp).SingleOrDefault();
if (tpQuery != null)
{
tpQuery.tenKhoa = TenKhoa;
qlSV.SubmitChanges();
}
return(true);
}
public bool ThemAc(string user, string pass, string Hoten, string GT, string phone, string email, ref string err)
{
QLSVDataContext qlSV = new QLSVDataContext();
Login kh = new Login();
kh.userName = user;
kh.passWord = pass;
kh.hoTen = Hoten;
kh.gioiTinh = GT;
kh.Phone = phone;
kh.Email = email;
qlSV.Logins.InsertOnSubmit(kh);
qlSV.Logins.Context.SubmitChanges();
return(true);
}
public bool ThemSinhVien(string MaSV, string HoTen, DateTime NgaySinh, string GioiTinh, string DiaChi, string MaLop, ref string err)
{
QLSVDataContext qlSV = new QLSVDataContext();
SinhVien kh = new SinhVien();
kh.maSV = MaSV;
kh.hoTen = HoTen;
kh.ngaySinh = NgaySinh;
kh.gioiTinh = GioiTinh;
kh.diaChi = DiaChi;
kh.maLop = MaLop;
qlSV.SinhViens.InsertOnSubmit(kh);
qlSV.SinhViens.Context.SubmitChanges();
return(true);
}
