public int?Update_HocSinh(HocSinh hocsinh)
{
int?result = null;
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
SqlCommand command = new SqlCommand("UPDATE_HOCSINH", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@MAHOCSINH", SqlDbType.Decimal).Value = hocsinh.MaHocSinh;
command.Parameters.Add("@HOTEN", SqlDbType.NVarChar).Value = hocsinh.HoTen;
command.Parameters.Add("@GIOITINH", SqlDbType.Bit).Value = hocsinh.GioiTinh;
command.Parameters.Add("@NGAYSINH", SqlDbType.SmallDateTime).Value = hocsinh.NgaySinh;
command.Parameters.Add("@DIACHI", SqlDbType.NVarChar).Value = hocsinh.DiaChi;
command.Parameters.Add("@EMAIL", SqlDbType.NVarChar).Value = hocsinh.Email;
try
{
command.Connection.Open();
command.ExecuteNonQuery();
result = 1;
command.Connection.Close();
}
catch
{
command.Connection.Close();
}
}
return(result);
}
//public IList<HocSinh> getAll(int PageNo, int PageSize, string Tenhocsinh, string Quequan, out int RecordCount)
//{
// SqlParameter[] parm = new SqlParameter[]
// {
// new SqlParameter("@PageIndex",SqlDbType.Int),
// new SqlParameter("@PageSize",SqlDbType.Int),
// new SqlParameter("@tenhocsinh",SqlDbType.NVarChar,20),
// new SqlParameter("@quequan",SqlDbType.NVarChar,20),
// };
// parm[0].Value = PageNo;
// parm[1].Value = PageSize;
// parm[2].Value = Tenhocsinh;
// parm[3].Value = Quequan;
// RecordCount = 0;
// SqlDataReader dra = DataAccessHelper.ExecuteReader(DataAccessHelper.ConnectionString, CommandType.StoredProcedure, "tbl_HocSinh_Sell_All_Page", parm);
// IList<HocSinh> list = new List<HocSinh>();
// while (dra.Read())
// {
// RecordCount = int.Parse(dra["RecordCount"].ToString());
// list.Add(new HocSinh(
// int.Parse(dra["mahocsinh"].ToString()),
// int.Parse(dra["malop"].ToString()),
// dra["tenhocsinh"].ToString(),
// DateTime.Parse(dra["ngaysinh"].ToString()),
// dra["gioitinh"].ToString(),
// dra["quequan"].ToString(),
// dra["sodienthoai"].ToString(),
// dra["socmnd"].ToString()));
// }
// dra.Dispose();
// return list;
//}
public HocSinh getmaHocSinh(int mahocsinh)
{
SqlParameter[] parm = new SqlParameter[]
{
new SqlParameter(PARM_MAHOCSINH, SqlDbType.Int)
};
parm[0].Value = mahocsinh;
HocSinh hs = null;
SqlDataReader dra = DataAccessHelper.ExecuteReader(DataAccessHelper.ConnectionString, CommandType.StoredProcedure, "tbl_HocSinh_Sel_ID", parm);
if (dra.Read())
{
hs = new HocSinh(
int.Parse(dra["mahocsinh"].ToString()),
int.Parse(dra["malop"].ToString()),
dra["tenhocsinh"].ToString(),
DateTime.Parse(dra["ngaysinh"].ToString()),
dra["gioitinh"].ToString(),
dra["quequan"].ToString(),
dra["sodienthoai"].ToString(),
dra["socmnd"].ToString());
}
dra.Dispose();
return(hs);
}
public int ThemHocSinh(HocSinh hs)
{
List <SqlParameter> list = new List <SqlParameter>();
list.Add(new SqlParameter("@hoten", hs.HoTen));
list.Add(new SqlParameter("@gioitinh", hs.GioiTinh));
list.Add(new SqlParameter("@ngaysinh", hs.NgaySinh));
list.Add(new SqlParameter("@diachi", hs.DiaChi));
list.Add(new SqlParameter("@sdt", hs.SDT));
list.Add(new SqlParameter("@email", hs.Email));
list.Add(new SqlParameter("@tinhtrang", hs.TinhTrang));
list.Add(new SqlParameter("@lophientai", hs.LopHienTai));
return(ExecProcedure("ThemHocSinh", System.Data.CommandType.StoredProcedure, list));
}
public List <HocSinh> getStudents()
{
string sql = "SELECT hs.MaHS,hs.HoTen,hs.GioiTinh,hs.NgaySinh,hs.DiaChi,hs.SDT,hs.Email,hs.TinhTrang,hs.MaLop, l.TenLop from HocSinh hs, Lop l where hs.MaLop = l.MaLop";
int maHS, maLop;
DateTime ngaySinh;
string hoTen, gioiTinh, diaChi, sdt, email, tenLop;
bool tinhTrang;
List <HocSinh> list = new List <HocSinh>();
dp.Connect();
try
{
SqlDataReader dr = dp.myExecuteReader(sql);
while (dr.Read())
{
maHS = dr.GetInt32(0);
hoTen = dr.GetString(1);
gioiTinh = dr.GetString(2);
ngaySinh = dr.GetDateTime(3);
diaChi = dr.GetString(4);
sdt = dr.GetString(5);
email = dr.GetString(6);
tinhTrang = (bool)dr[7];
maLop = dr.GetInt32(8);
tenLop = dr.GetString(9);
HocSinh hs = new HocSinh(maHS, hoTen, gioiTinh, ngaySinh, diaChi, sdt, email, tinhTrang, maLop, tenLop);
list.Add(hs);
list.ForEach(x =>
{
x.TinhTrangText = x.TinhTrang ? "Đang học" : "Thôi học";
});
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public HocSinh getStudent(int id)
{
string sql = "SELECT * from HocSinh where MaSoHS = " + "'" + id + "'";
int maHS, maLop;
DateTime ngaySinh;
string hoTen, gioiTinh, diaChi, sdt, email, tenLop;
bool tinhTrang;
HocSinh hocsinh = new HocSinh();
dp.Connect();
try
{
SqlDataReader dr = dp.myExecuteReader(sql);
while (dr.Read())
{
maHS = dr.GetInt32(0);
hoTen = dr.GetString(1);
gioiTinh = dr.GetString(2);
ngaySinh = dr.GetDateTime(3);
diaChi = dr.GetString(4);
sdt = dr.GetString(5);
email = dr.GetString(6);
tinhTrang = (bool)dr[7];
maLop = dr.GetInt32(8);
tenLop = dr.GetString(9);
HocSinh hs = new HocSinh(maHS, hoTen, gioiTinh, ngaySinh, diaChi, sdt, email, tinhTrang, maLop, tenLop);
}
dr.Close();
return(hocsinh);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public int Insert(HocSinh hs)
{
SqlParameter[] parm = new SqlParameter[]
{
new SqlParameter(PARM_MALOP, SqlDbType.Int),
new SqlParameter(PARM_TENHOCSINH, SqlDbType.NVarChar, 20),
new SqlParameter(PARM_NGAYSINH, SqlDbType.DateTime),
new SqlParameter(PARM_GIOITINH, SqlDbType.NVarChar, 3),
new SqlParameter(PARM_QUEQUAN, SqlDbType.NVarChar, 20),
new SqlParameter(PARM_SODT, SqlDbType.NVarChar, 11),
new SqlParameter(PARM_SOCMND, SqlDbType.NVarChar, 11),
};
parm[0].Value = hs.Malop;
parm[1].Value = hs.Tenhocsinh;
parm[2].Value = hs.Ngaysinh;
parm[3].Value = hs.Gioitinh;
parm[4].Value = hs.Quequan;
parm[5].Value = hs.Sodienthoai;
parm[6].Value = hs.Socmnd;
return(DataAccessHelper.ExecuteNonQuery(DataAccessHelper.ConnectionString, CommandType.StoredProcedure, "tbl_HocSinh_Insert", parm));
}