public DocGia layDocGiaTheoMa(string ma)
{
try
{
DocGia dg = new DocGia();
SqlCommand command = truyVan1("SELECT * FROM DocGia5 WHERE MaDGia = @ma");
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = ma;
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
dg.MaDG = reader.GetString(0);
dg.TenDG = reader.GetString(1);
dg.GT = reader.GetString(2);
dg.NgaySinh = reader.GetDateTime(3).ToString("dd/MM/yyyy");
dg.MaDT = reader.GetString(4);
dg.NgayCap = reader.GetDateTime(5).ToString("dd/MM/yyyy");
dg.NgayGiaHan = reader.GetDateTime(6).ToString("dd/MM/yyyy");
dg.Anh = reader.GetString(7);
}
reader.Close();
return(dg);
}
catch { }
return(null);
}
public string themDocGia(DocGia docGia)
{
try
{
SqlCommand command = truyVan1("insert into DocGia5 values(@maDG, @tenDG, @gt, @ngaySinh, @maDT, @ngayCap, @ngayHetHan, @anh)");
command.Parameters.Add("@maDG", SqlDbType.NVarChar).Value = docGia.MaDG;
command.Parameters.Add("@tenDG", SqlDbType.NVarChar).Value = docGia.TenDG;
command.Parameters.Add("@gt", SqlDbType.NVarChar).Value = docGia.GT;
string[] s = docGia.NgaySinh.Split('/');
DateTime d1 = DateTime.Parse(s[1] + "/" + s[0] + "/" + s[2]);
command.Parameters.Add("@ngaySinh", SqlDbType.DateTime).Value = d1;
command.Parameters.Add("@maDT", SqlDbType.NVarChar).Value = docGia.MaDT;
string[] s2 = docGia.NgayCap.Split('/');
DateTime d2 = DateTime.Parse(s2[1] + "/" + s2[0] + "/" + s2[2]);
command.Parameters.Add("@ngayCap", SqlDbType.DateTime).Value = d2;
string[] s3 = docGia.NgayGiaHan.Split('/');
DateTime d3 = DateTime.Parse(s3[1] + "/" + s3[0] + "/" + s3[2]);
command.Parameters.Add("@ngayHetHan", SqlDbType.DateTime).Value = d3;
command.Parameters.Add("@anh", SqlDbType.NVarChar).Value = docGia.Anh;
command.ExecuteNonQuery();
return("Thêm độc giả thành công !");
}
catch (Exception ex)
{
return("ERR: " + ex.Message);
}
}
public string suaDocGia(DocGia docGia)
{
try
{
SqlCommand command = truyVan1("update DocGia5 set Hten=@ten, GTinh = @gt, NSinh= @ngSinh, MaDTuong= @maDT, NgayCap= @ngayCap, NgayGHan= @ngayGiaHan, Anh = @anh Where MaDGia= @ma");
command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = docGia.TenDG;
command.Parameters.Add("@gt", SqlDbType.NVarChar).Value = docGia.GT;
string[] s = docGia.NgaySinh.Split('/');
DateTime d1 = DateTime.Parse(s[1] + "/" + s[0] + "/" + s[2]);
command.Parameters.Add("@ngSinh", SqlDbType.DateTime).Value = d1;
command.Parameters.Add("@maDT", SqlDbType.NVarChar).Value = docGia.MaDT;
string[] s2 = docGia.NgayCap.Split('/');
DateTime d2 = DateTime.Parse(s2[1] + "/" + s2[0] + "/" + s2[2]);
command.Parameters.Add("@ngayCap", SqlDbType.DateTime).Value = d2;
string[] s3 = docGia.NgayGiaHan.Split('/');
DateTime d3 = DateTime.Parse(s3[1] + "/" + s3[0] + "/" + s3[2]);
command.Parameters.Add("@ngayGiaHan", SqlDbType.DateTime).Value = d3;
command.Parameters.Add("@anh", SqlDbType.NVarChar).Value = docGia.Anh;
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = docGia.MaDG;
command.ExecuteNonQuery();
return("Sửa độc giả thành công !");
}
catch (Exception ex)
{
return("ERR: " + ex.Message);
}
}
public bool themDocGia(DocGiaDTO docGiaDTO)
{
try
{
DocGia docGia = new DocGia {
MaDocGia = docGiaDTO.MaDocGia,
TenDocGia = docGiaDTO.TenDocGia,
CMND = docGiaDTO.CMND,
DiaChi = docGiaDTO.DiaChi,
SoThe = docGiaDTO.SoThe,
TrangThai = true
};
data.DocGias.Add(docGia);
data.SaveChanges();
return true;
}
catch(Exception)
{
return false;
}
}
public int XoaDocGia(DocGia docGia)
{
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC xoadocgia @maDocGia";
command.Connection = conn;
command.Parameters.Add("@maDocGia", SqlDbType.Int).Value = docGia.maDocGia;
return(command.ExecuteNonQuery());
}
public bool xoaDocGia(string MaDG)
{
try
{
DocGia docGia = new DocGia();
docGia = data.DocGias.SingleOrDefault(u => u.MaDocGia == MaDG && u.TrangThai == true);
docGia.TrangThai = false;
data.SaveChanges();
return true;
}
catch(Exception)
{
return false;
}
}
public DocGiaDTO timDocGia(string MaDG)
{
DocGiaDTO docGiaDTO = new DocGiaDTO();
DocGia docgia = data.DocGias.SingleOrDefault(u => u.MaDocGia == MaDG && u.TrangThai == true);
docGiaDTO.MaDocGia = docgia.MaDocGia;
docGiaDTO.TenDocGia = docgia.TenDocGia;
docGiaDTO.DiaChi = docgia.DiaChi;
docGiaDTO.CMND = docgia.CMND;
docGiaDTO.SoThe = docgia.SoThe;
docgia.TrangThai = docgia.TrangThai;
return docGiaDTO;
}
public bool Sua(DocGia entity)
{
try
{
string query = @"UPDATE SINHVIEN SET TENSV=N'" + entity.Hoten + "', NGAYSINH=N'" + entity.Ngaysinh + "', GIOITINH=N'" + entity.Gioitinh + "',DIACHI=N'" + entity.Diachi + "',LOP=N'" + entity.Lop + "' WHERE MASV='" + entity.Masv + "'";
OpenConection();
ExecuteQueries(query);
CloseConnection();
return(true);
}
catch (Exception ex)
{
SetEx(ex);
return(false);
}
}
public bool Them(DocGia dg)
{
try
{
string query = @"INSERT INTO SINHVIEN( MASV ,TENSV ,NGAYSINH ,GIOITINH ,DIACHI ,LOP)
VALUES ( '" + dg.Masv + "',N'" + dg.Hoten + "','" + dg.Ngaysinh + "', N'" + dg.Gioitinh + "',N'" + dg.Diachi + "',N'" + dg.Lop + "')";
OpenConection();
ExecuteQueries(query);
CloseConnection();
return(true);
}
catch (Exception ex)
{
SetEx(ex);
return(false);
}
}
public bool ThemDocGia(DocGia docGia)
{
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC themDocGia @maDocGia, @tenDocGia, @donVi, @ngaySinh, @sdt, @queQuan";
command.Connection = conn;
command.Parameters.Add("@maDocGia", SqlDbType.Int).Value = docGia.maDocGia;
command.Parameters.Add("@tenDocGia", SqlDbType.NVarChar).Value = docGia.tenDocGia;
command.Parameters.Add("@donVi", SqlDbType.NVarChar).Value = docGia.donVi;
command.Parameters.Add("@ngaySinh", SqlDbType.DateTime).Value = docGia.ngaySinh;
command.Parameters.Add("@sdt", SqlDbType.Int).Value = docGia.sdt;
command.Parameters.Add("@queQuan", SqlDbType.NVarChar).Value = docGia.queQuan;
int kq = command.ExecuteNonQuery();
return(kq > 0);
}
public bool suaDocGia(DocGiaDTO docGiaDTO)
{
try
{
DocGia docGia = new DocGia();
docGia = data.DocGias.SingleOrDefault(u => u.MaDocGia == docGiaDTO.MaDocGia && u.TrangThai == true);
docGia.TenDocGia = docGiaDTO.TenDocGia;
docGia.CMND = docGiaDTO.CMND;
docGia.DiaChi = docGiaDTO.DiaChi;
data.SaveChanges();
return true;
}
catch(Exception)
{
return false;
}
}
public bool Them(DocGia pDocGia)
{
try
{
string sql = string.Format("INSERT INTO DocGia "
+ "VALUES (N'{0}','{1}',N'{2}','{3}','{4}','{5}','{6}')",
pDocGia.HoTenDocGia, pDocGia.NgaySinh,
pDocGia.DiaChi, pDocGia.Email, pDocGia.NgayLapThe, pDocGia.NgayHetHan, pDocGia.TienNo);
SqlCommand cmd = new SqlCommand(sql, _sqlConn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
catch
{
return(false);
}
}
public bool Sua(DocGia pDocGia, string idx)
{
try
{
string sql = string.Format("UPDATE DocGia " +
"SET HoTenDocGia = '{0}', NgaySinh = '{1}', DiaChi = '{2}', Email = '{3}', NgayLapThe = '{4}', NgayHetHan = '{5}', TienNo = '{6}' " +
"WHERE MaDocGia = " + idx,
pDocGia.HoTenDocGia, pDocGia.NgaySinh,
pDocGia.DiaChi, pDocGia.Email, pDocGia.NgayLapThe, pDocGia.NgayHetHan, pDocGia.TienNo);
SqlCommand cmd = new SqlCommand(sql, _sqlConn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
catch
{
return(false);
}
}
public List <DocGia> layDanhSachDocGia()
{
List <DocGia> dsDocGia = new List <DocGia>();
SqlCommand command = truyVan1("select * from DocGia5");
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocGia dg = new DocGia();
dg.MaDG = reader.GetString(0);
dg.TenDG = reader.GetString(1);
dg.GT = reader.GetString(2);
dg.NgaySinh = reader.GetDateTime(3).ToString("dd/MM/yyyy");
dg.MaDT = reader.GetString(4);
dg.NgayCap = reader.GetDateTime(5).ToString("dd/MM/yyyy");
dg.NgayGiaHan = reader.GetDateTime(6).ToString("dd/MM/yyyy");
dg.Anh = reader.GetString(7);
dsDocGia.Add(dg);
}
reader.Close();
return(dsDocGia);
}
public List <DocGia> layDanhSachDocGia(string ten)
{
List <DocGia> dsDocGia = new List <DocGia>();
SqlCommand command = truyVan1("select * from DocGia5 Where Hten like @ten");
command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = ten;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocGia dg = new DocGia();
dg.MaDG = reader.GetString(0);
dg.TenDG = reader.GetString(1);
dg.GT = reader.GetString(2);
dg.NgaySinh = reader.GetDateTime(3).ToString("dd/MM/yyyy");
dg.MaDT = reader.GetString(4);
dg.NgayCap = reader.GetDateTime(5).ToString("dd/MM/yyyy");
dg.NgayGiaHan = reader.GetDateTime(6).ToString("dd/MM/yyyy");
dg.Anh = reader.GetString(7);
dsDocGia.Add(dg);
}
reader.Close();
return(dsDocGia);
}
public List <DocGia> TimKiemDocGia(string textSearch)
{
List <DocGia> listDocGia = new List <DocGia>();
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM dbo.timKiemDocGia(@textSearch)";
command.Connection = conn;
command.Parameters.Add("@textSearch", SqlDbType.NVarChar).Value = textSearch;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocGia docGia = new DocGia();
docGia.maDocGia = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
docGia.tenDocGia = reader.GetString(1);
}
else
{
docGia.tenDocGia = "Chưa có thông tin";
}
if (!reader.IsDBNull(2))
{
docGia.donVi = reader.GetString(2);
}
else
{
docGia.donVi = "Chưa có thông tin";
}
if (!reader.IsDBNull(3))
{
docGia.ngaySinh = reader.GetDateTime(3);
}
else
{
docGia.ngaySinh = DateTime.Now;
}
if (!reader.IsDBNull(4))
{
docGia.sdt = reader.GetInt32(4);
}
else
{
docGia.sdt = 0;
}
if (!reader.IsDBNull(5))
{
docGia.queQuan = reader.GetString(5);
}
else
{
docGia.queQuan = "Chưa có thông tin";
}
listDocGia.Add(docGia);
}
reader.Close();
return(listDocGia);
}
public DocGia ThongTinChiTietDocGia(int maDocGia)
{
DocGia docGia = new DocGia();
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM dbo.timKiemDocGia(@maDocGia)";
command.Connection = conn;
command.Parameters.Add("@maDocGia", SqlDbType.NVarChar).Value = maDocGia.ToString();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
docGia.maDocGia = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
docGia.tenDocGia = reader.GetString(1);
}
else
{
docGia.tenDocGia = "Chưa có thông tin";
}
if (!reader.IsDBNull(2))
{
docGia.donVi = reader.GetString(2);
}
else
{
docGia.donVi = "Chưa có thông tin";
}
if (!reader.IsDBNull(3))
{
docGia.ngaySinh = reader.GetDateTime(3);
}
else
{
docGia.ngaySinh = DateTime.Now;
}
if (!reader.IsDBNull(4))
{
docGia.sdt = reader.GetInt32(4);
}
else
{
docGia.sdt = 0;
}
if (!reader.IsDBNull(5))
{
docGia.queQuan = reader.GetString(5);
}
else
{
docGia.queQuan = "Chưa có thông tin";
}
}
reader.Close();
return(docGia);
}
public List <DocGia> HienThiGiaoDienDocGia()
{
List <DocGia> listDocGia = new List <DocGia>();
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM dbo.hienthitoanbodocgia";
command.Connection = conn;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocGia docGia = new DocGia();
docGia.maDocGia = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
docGia.tenDocGia = reader.GetString(1);
}
else
{
docGia.tenDocGia = "Chưa có thông tin";
}
if (!reader.IsDBNull(2))
{
docGia.donVi = reader.GetString(2);
}
else
{
docGia.donVi = "Chưa có thông tin";
}
if (!reader.IsDBNull(3))
{
docGia.ngaySinh = reader.GetDateTime(3);
}
else
{
docGia.ngaySinh = DateTime.Now;
}
if (!reader.IsDBNull(4))
{
docGia.sdt = reader.GetInt32(4);
}
else
{
docGia.sdt = 0;
}
if (!reader.IsDBNull(5))
{
docGia.queQuan = reader.GetString(5);
}
else
{
docGia.queQuan = "Chưa có thông tin";
}
listDocGia.Add(docGia);
}
reader.Close();
return(listDocGia);
}