private void button1_Click(object sender, EventArgs e)
{
bool hopLe = thongTinHopLe();
if (hopLe)
{
string maHS = txtMaHS.Text.Trim();
int maHS_length = maHS.Length;
string ho_ten = txtHoTen.Text.Trim();
int hoten_length = ho_ten.Length;
string gioi_tinh = gioitinh.Checked == true ? "Nam" : "Nu";
string diachi = dia_chi.Text.Trim();
Single diemTB = 0;
diemTB = Single.Parse(diem_tb.Text.Trim());
string lop = cbLop.Text;
string ngay_sinh = ngaysinh.Value.ToShortDateString();
string txtmaLop = maLop(lop);
string procedureName = "sp_InsertHocSinh";
SqlParameter @out = new SqlParameter("@p_Mess", SqlDbType.NVarChar, 100);
#region Input Parameter
object[] inputValue = { maHS, ho_ten, gioi_tinh, ngay_sinh, diachi, diemTB, txtmaLop };
string[] inputKey =
{
"@p_MaHS",
"@p_TenHs",
"@p_GioiTinh",
"@p_NgaySinh",
"@p_DiaChi",
"@p_DTB",
"@p_MaLop"
};
SqlParameter[] InputParametter = new SqlParameter[inputValue.Length];
for (int i = 0; i < inputValue.Length; i++)
{
InputParametter[i] = new SqlParameter(inputKey[i], inputValue[i]);
}
#endregion
string message = SQL_Connect.execProcedure(procedureName, @out, InputParametter);
if (message.Length == 0)
{
MessageBox.Show("Nhap thanh cong");
button2_Click(sender, e);
Form1_Load(sender, e);
}
else
{
MessageBox.Show(message);
}
}
}
private void xoa_hs_trong_csdl_Click(object sender, EventArgs e)
{
string mahs_temp = txtMaHS.Text.Trim();
int index = xoa_hoc_sinh_trong_du_lieu.FocusedItem.Index;
if ((index < 0) && (mahs_temp.Length == 0))
{
MessageBox.Show("Chon hoc sinh can xoa");
}
else
{
txtMaHS.Text = mahs_temp;
button6_Click(sender, e);
string sql_text = "DELETE from HOCSINH WHERE MAHS = '" + mahs_temp + "'";
if (SQL_Connect.exec(sql_text))
{
MessageBox.Show("Xoa thanh cong");
button2_Click(sender, e);
Form1_Load(sender, e);
}
else
{
MessageBox.Show("Loi du lieu");
}
}
}
private void button6_Click(object sender, EventArgs e)
{
string maHS = txtMaHS.Text.Trim();
int n = maHS.Length;
if (n == 0)
{
MessageBox.Show("Vui long nhap ma hoc sinh");
}
else
{
string sql_command = "SELECT MaHS, HoTen, GioiTinh, NgaySinh, " +
"DTB, TenLop, DiaChi " +
"from HOCSINH,LOP "
+ "where HOCSINH.MALOP =LOP.MALOP " + "AND MAHS='" + maHS + "'";
DataTable dataReader = SQL_Connect.getTable(sql_command);
if (dataReader.Rows.Count > 0)
{
txtMaHS.Text = dataReader.Rows[0]["MAHS"].ToString();
txtHoTen.Text = dataReader.Rows[0]["HOTEN"].ToString();
gioitinh.Checked = dataReader.Rows[0]["GIOITINH"].ToString() == "Nam";
dia_chi.Text = dataReader.Rows[0]["DIACHI"].ToString();
diem_tb.Text = dataReader.Rows[0]["DTB"].ToString();
cbLop.Text = dataReader.Rows[0]["TenLop"].ToString();
ngaysinh.Value = (DateTime)dataReader.Rows[0]["NgaySinh"];
}
else
{
MessageBox.Show("khong tim thay");
}
}
}
private void btnTimKiem_Click(object sender, EventArgs e)
{
string sLop = "";
try
{
sLop = cbLop.SelectedItem.ToString();
}
catch { }
string sMaHS = "";
try
{
sMaHS = txtMaHS.Text.ToUpper();
}
catch { }
string sHoTenHS = "";
try
{
sHoTenHS = txtHoTenHS.Text;
}
catch { }
string sGioiTinh = "";
try
{
sGioiTinh = cbGioiTinh.SelectedItem.ToString();
}
catch { }
string sql = @"SELECT HOCSINH.MAHS, HOCSINH.HOTEN, HOCSINH.GIOITINH, HOCSINH.NGAYSINH, HOCSINH.DIACHI, HOCSINH.DTB, LOP.LOP
FROM LOP, HOCSINH
WHERE LOP.MALOP = HOCSINH.MALOP";
if (sLop != "")
{
sql += " and LOP.LOP = '" + sLop + "'";
}
if (sMaHS != "")
{
sql += " and HOCSINH.MAHS like '%" + sMaHS + "%'";
}
if (sHoTenHS != "")
{
sql += " and HOCSINH.HOTEN like '%" + sHoTenHS + "%'";
}
if (sGioiTinh != "")
{
sql += " and HOCSINH.GIOITINH ='" + sGioiTinh + "'";
}
DataTable hocSinh = SQL_Connect.getTable(sql);
dgHocSinh.DataSource = hocSinh;
dgHocSinh.ReadOnly = true;
}
private static string maLop(string txtLop)
{
string sql_docLop = "SELECT * FROM LOP WHERE TenLop ='" + txtLop + "'";
DataTable dt = SQL_Connect.getTable(sql_docLop);
string txtMALop = dt.Rows[0]["MALOP"].ToString();
return(txtMALop);
}
private void cbLop_SelectedIndexChanged(object sender, EventArgs e)
{
string txt_Lop = cbLop.Text;
if (!SQL_Connect.openConnection())
{
MessageBox.Show("Khong ket noi duoc CSDL", "Thong bao loi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
DataTable tbHocSinh;
tbHocSinh = Doc_Danhsachhocsinh(txt_Lop);
Load_DanhSach_HocSinh_ListView(tbHocSinh);
}
private void button4_Click(object sender, EventArgs e)
{
bool hopLe = thongTinHopLe();
if (hopLe)
{
string maHS = txtMaHS.Text.Trim();
int maHS_length = maHS.Length;
string ho_ten = txtHoTen.Text.Trim();
int hoten_length = ho_ten.Length;
string gioi_tinh = gioitinh.Checked == true ? "Nam" : "Nu";
string diachi = dia_chi.Text.Trim();
Single diemTB = 0;
bool dtb_bool = kiem_diem(ref diemTB);
string lop = cbLop.Text;
string ngay_sinh = ngaysinh.Value.ToShortDateString();
string txtMaLop = maLop(lop);
string sql_text = "UPDATE HOCSINH SET " +
"HOTEN = '" + ho_ten + "'," +
"GIOITINH = '" + gioi_tinh + "'," +
"NGAYSINH = '" + ngay_sinh + "'," +
"DIACHI='" + diachi + "'," +
"DTB='" + diemTB + "'," +
"MALOP='" + txtMaLop + "'" +
"WHERE MAHS = '" + maHS + "';";
if (SQL_Connect.exec(sql_text))
{
MessageBox.Show("Nhap thanh cong");
button2_Click(sender, e);
Form1_Load(sender, e);
}
else
{
MessageBox.Show("loi khi nhap du lieu");
}
}
}
DataTable Doc_Danhsachhocsinh(string txtLop)
{
string sql_command = "SELECT MaHS, HoTen, GioiTinh, NgaySinh, " +
"DTB, TenLop, DiaChi " +
"from HOCSINH,LOP "
+ "where HOCSINH.MALOP =LOP.MALOP";
if (txtLop != "")
{
string txtMaLop = maLop(txtLop);
sql_command += " AND HOCSINH.MALOP ='" + txtMaLop + "'";
}
DataTable tb = SQL_Connect.getTable(sql_command);
return(tb);
}
private void danh_sach_Load(object sender, EventArgs e)
{
string sql = @"SELECT distinct LOP
FROM LOP";
DataTable tbLop = SQL_Connect.getTable(sql);
cbLop.Items.Clear();
cbLop.Items.Add("");
for (int i = 0; i < tbLop.Rows.Count; i++)
{
cbLop.Items.Add(tbLop.Rows[i]["LOP"].ToString());
}
sql = @"SELECT HOCSINH.MAHS, HOCSINH.HOTEN, HOCSINH.GIOITINH, HOCSINH.NGAYSINH, HOCSINH.DIACHI, HOCSINH.DTB, LOP.LOP
FROM LOP INNER JOIN HOCSINH ON LOP.MALOP = HOCSINH.MALOP;";
DataTable hocSinh = SQL_Connect.getTable(sql);
dgHocSinh.DataSource = hocSinh;
dgHocSinh.ReadOnly = true;
}
private void Form1_Load(object sender, EventArgs e)
{
if (!SQL_Connect.openConnection())
{
MessageBox.Show("Khong ket noi duoc CSDL", "Thong bao loi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
string sql_docLop = "SELECT TenLop from LOP";
DataTable tb_Lop = SQL_Connect.getTable(sql_docLop);
cbLop.Items.Clear();
cbLop.Items.Add("");
for (int i = 0; i < tb_Lop.Rows.Count; i++)
{
string lop_txt = tb_Lop.Rows[i]["TenLop"].ToString();
cbLop.Items.Add(lop_txt);
}
DataTable tbHocSinh;
tbHocSinh = Doc_Danhsachhocsinh("");
Load_DanhSach_HocSinh_ListView(tbHocSinh);
}