public DataTable getBangDiem(DTO_BangDiem A)
{
List <int> dsHocSinh = new List <int>();
dsHocSinh = InsertDanhSach(A);
foreach (int i in dsHocSinh)
{
string sql = "insert into BANGDIEM values ( " + A.MaNH + ", " + A.MaLop + " , " + A.MaHK + ", " + A.MaMH + ", " + i + ", " + A.HeSo + ", " + A.LanKiemTra + ", '" + A.HinhThucKiemTra + "', NULL)";
MessageBox.Show(sql);
_conn.Open();
SqlCommand cmdds = new SqlCommand(sql, _conn);
cmdds.ExecuteNonQuery();
_conn.Close();
}
try
{
string sql = "select HOCSINH.mahs, HOCSINH.HOTEN, BANGDIEM.DIEM from BANGDIEM , HOCSINH where HOCSINH.MAHS = BANGDIEM.MAHS and BANGDIEM.MALOP = " + A.MaLop + " and BANGDIEM.MAHK = " + A.MaHK + " and BANGDIEM.MANH= " + A.MaNH + " and BANGDIEM.HESO = " + A.HeSo + " and BANGDIEM.LANKIEMTRA = " + A.LanKiemTra + " and BANGDIEM.MAMH = " + A.MaMH + " and BANGDIEM.HINHTHUCKIEMTRA = '" + A.HinhThucKiemTra + "'";
MessageBox.Show(sql);
da = new SqlDataAdapter(sql, _conn);
da.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show("Không thể lấy cơ sở dữ liệu mã lớp");
}
return(dt);
}
public DataTable getBangDiem(DTO_BangDiem A)
{
dt.Clear();
List <int> dsHocSinh = new List <int>();
dsHocSinh = InsertDanhSach(A);
//foreach (int i in dsHocSinh)
//{
// string sqlInsert = "insert into BANGDIEM values ( " + A.MaNH + ", " + A.MaLop + " , " + A.MaHK + ", " + A.MaMH + ", " + i + ", " + A.HeSo + ", " + A.LanKiemTra + ", '" + A.HinhThucKiemTra + "', NULL)";
// _conn.Open();
// SqlCommand cmdds = new SqlCommand(sqlInsert, _conn);
// try
// {
// cmdds.ExecuteNonQuery();
// }
// catch
// {
// }
// _conn.Close();
//}
try
{
string sqlSelectCoSan = "select HOCSINH.MAHS, HOCSINH.HOTEN, BANGDIEM.DIEM from BANGDIEM , HOCSINH where HOCSINH.MAHS = BANGDIEM.MAHS and BANGDIEM.MALOP = " + A.MaLop + " and BANGDIEM.MAHK = " + A.MaHK + " and BANGDIEM.MANH= " + A.MaNH + " and BANGDIEM.HESO = " + A.HeSo + " and BANGDIEM.LANKIEMTRA = " + A.LanKiemTra + " and BANGDIEM.MAMH = " + A.MaMH + " and BANGDIEM.HINHTHUCKIEMTRA = '" + A.HinhThucKiemTra + "'";
string sqlSelectChuaCo = string.Format("select mahs, hoten from hocsinh where mahs in (select mahs from chitietlop where malop = " + A.MaLop + "and manh = " + A.MaNH + ")", _conn);
//try
//{
da = new SqlDataAdapter(sqlSelectCoSan, _conn);
//}
//catch
//{
// MessageBox.Show("Không thể load!");
// da = new SqlDataAdapter(sqlSelectChuaCo, _conn);
//}
da.Fill(dt);
if (dt.Rows.Count == 0)
{
da = new SqlDataAdapter(sqlSelectChuaCo, _conn);
da.Fill(dt);
}
}
catch (Exception ex)
{
MessageBox.Show("Không thể lấy cơ sở dữ liệu mã lớp");
}
return(dt);
}
List <int> InsertDanhSach(DTO_BangDiem A)
{
_conn.Open();
string sql = "select MAHS from CHITIETLOP where MALOP = " + A.MaLop + " and MANH = " + A.MaNH;
//MessageBox.Show(sql);
SqlDataAdapter d = new SqlDataAdapter(sql, _conn);
DataTable temp = new DataTable();
d.Fill(temp);
_conn.Close();
List <int> lHocSinh = new List <int>();
foreach (DataRow item in temp.Rows)
{
int i = int.Parse(item[0].ToString());
lHocSinh.Add(i);
}
return(lHocSinh);
}
public void CapNhatDiem(DTO_BangDiem A)
{
try
{
string sqlcmd = "UPDATE BANGDIEM SET DIEM = " + A.Diem + " where " + "HESO = " + A.HeSo + " and LANKIEMTRA = " + A.LanKiemTra + " and MAHS = " + A.MaHS + " and MALOP= " + A.MaLop + " and MANH =" + A.MaNH + " and MAHK =" + A.MaHK + " and MAMH =" + A.MaMH;
//string sqlhocsinh = "select mahs from chitietlop where malop = " + A.MaLop + " manh = " + A.MaNH;
MessageBox.Show(sqlcmd);
_conn.Open();
SqlCommand sqlCom = new SqlCommand(sqlcmd, _conn);
int i = sqlCom.ExecuteNonQuery();
//MessageBox.Show(i.ToString());
_conn.Close();
//int i = sqlCom.ExecuteNonQuery();
//if (i<0) MessageBox.Show("Không thể lưu dữ liệu!");
}
catch (Exception ex)
{
MessageBox.Show("Không thể lưu dữ liệu!");
}
}
public void CapNhatDiem(DTO_BangDiem A)
{
try
{
string sqlUpdate = "UPDATE BANGDIEM SET DIEM = " + A.Diem + " where " + "HESO = " + A.HeSo + " and LANKIEMTRA = " + A.LanKiemTra + " and MAHS = " + A.MaHS + " and MALOP= " + A.MaLop + " and MANH =" + A.MaNH + " and MAHK =" + A.MaHK + " and MAMH =" + A.MaMH + " and HINHTHUCKIEMTRA ='" + A.HinhThucKiemTra + "'";
string sqlInsert = string.Format("INSERT INTO BANGDIEM VALUES({0},{1},{2},{3},{4},{5},{6},'{7}',{8})", A.MaNH, A.MaLop, A.MaHK, A.MaMH, A.MaHS, A.HeSo, A.LanKiemTra, A.HinhThucKiemTra, A.Diem, _conn);
_conn.Open();
SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, _conn);
SqlCommand cmdInsert = new SqlCommand(sqlInsert, _conn);
try
{
cmdInsert.ExecuteNonQuery();
}
catch
{
cmdUpdate.ExecuteNonQuery();
}
_conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Không thể lưu dữ liệu!");
}
}
//DataTable dtBangDiem = new DataTable();
public DataTable getBangDiem(DTO_BangDiem A)
{
dt.Clear();
//List<int> dsHocSinh = new List<int>();
//dsHocSinh = InsertDanhSach(A);
try
{
string sqlSelectCoSan = "select HOCSINH.MAHS, HOCSINH.HOTEN, BANGDIEM.DIEM from BANGDIEM , HOCSINH where HOCSINH.MAHS = BANGDIEM.MAHS and BANGDIEM.MALOP = " + A.MaLop + " and BANGDIEM.MAHK = " + A.MaHK + " and BANGDIEM.MANH= " + A.MaNH + " and BANGDIEM.HESO = " + A.HeSo + " and BANGDIEM.LANKIEMTRA = " + A.LanKiemTra + " and BANGDIEM.MAMH = " + A.MaMH + " and BANGDIEM.HINHTHUCKIEMTRA = '" + A.HinhThucKiemTra + "'";
string sqlSelectChuaCo = string.Format("select mahs, hoten from hocsinh where mahs in (select mahs from chitietlop where malop = " + A.MaLop + "and manh = " + A.MaNH + ")", _conn);
da = new SqlDataAdapter(sqlSelectCoSan, _conn);
da.Fill(dt);
if (dt.Rows.Count == 0)
{
da = new SqlDataAdapter(sqlSelectChuaCo, _conn);
da.Fill(dt);
}
A.Dem = dt.Rows.Count;
}
catch (Exception ex)
{
MessageBox.Show("Không thể lấy cơ sở dữ liệu mã lớp");
}
return(dt);
}
public void CapNhatDiem(DTO_BangDiem A)
{
da.CapNhatDiem(A);
}
public DataTable getBangDiem(DTO_BangDiem A)
{
return(da.getBangDiem(A));
}
