public static bool KiemTraTonTai(BENHNHAN bnDTO)
{
string sTruyVan = string.Format("SELECT MaBN from BENHNHAN WHERE TrangThai = '1' and (TenBN like N'{0}') and (NgaySinh = N'{1}' and (DienThoai = '{2}' and GioiTinh = N'{3}'))", bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DienThoai1, bnDTO.GioiTinh1); // MaBN tu dong tang
Con = DataProvider_1.KetNoi();
try
{
// thuc thi truy van
bool KetQua = DataProvider_1.ThucThiTruyVanNonQuery(sTruyVan, Con);
// dong ket noi
DataProvider_1.DongKetNoi(Con);
if (KetQua == true)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(false);
}
}
// Them BenhNhan
public static bool ThemBenhNhan(BENHNHAN bnDTO)
{
// tao cau truy van
// string sTruyVan = @"insert into BENHNHAN(MaBN,TenBN) value"; -- them mot vai thanh phan vao bang
/* string sTruyVan = string.Format("insert into BENHNHAN values ('{0}',N'{1}','{2}',N'{3}',{4},N'{5}')", bnDTO.MaBN1, bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DiaChi1, bnDTO.DienThoai1, bnDTO.GioiTinh1);*/ // them day du thong tin cua bang
string sTruyVan = string.Format("insert into BENHNHAN values (N'{0}',N'{1}',N'{2}','{3}',N'{4}', '1')", bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DiaChi1, bnDTO.DienThoai1, bnDTO.GioiTinh1); // MaBN tu dong tang
Con = DataProvider_1.KetNoi();
try
{
// thuc thi truy van
bool KetQua = DataProvider_1.ThucThiTruyVanNonQuery(sTruyVan, Con);
// dong ket noi
DataProvider_1.DongKetNoi(Con);
if (KetQua == true)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(false);
}
}
public static bool XoaBenhNhanCu(BENHNHAN bnDTO)
{
DataTable dt = TaoBang(bnDTO);
if (dt != null)
{
DuyetBang(dt);
}
String sTruyVan = string.Format("update BENHNHAN set TrangThai = '0' where MaBN ='{0}'", bnDTO.MaBN1);
Con = DataProvider_1.KetNoi();
try
{
// thuc thi truy van
bool KetQua = DataProvider_1.ThucThiTruyVanNonQuery(sTruyVan, Con);
// dong truy van
DataProvider_1.DongKetNoi(Con);
if (KetQua == true)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(false);
}
}
//Load benhnhan
public static List <BENHNHAN> LoadBenhNhan()
{
// khai bao truy van sql
string sTruyVan = "select * from BENHNHAN where TrangThai = '1'";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List BenhNhan_DTO
List <BENHNHAN> listbn = new List <BENHNHAN>();
for (int i = 0; i < dt.Rows.Count; i++)
{
BENHNHAN bn = new BENHNHAN();
// bn.MaBN1 = dt.Rows[i]["MaBN"].ToString(); // MaBN kieu string
bn.MaBN1 = int.Parse(dt.Rows[i]["MaBN"].ToString());
bn.TenBN1 = dt.Rows[i]["TenBN"].ToString();
bn.NgaySinh1 = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
bn.DiaChi1 = dt.Rows[i]["DiaChi"].ToString();
bn.DienThoai1 = dt.Rows[i]["DienThoai"].ToString();
bn.GioiTinh1 = dt.Rows[i]["GioiTinh"].ToString();
bn.TrangThai1 = int.Parse(dt.Rows[i]["TrangThai"].ToString());
listbn.Add(bn);
}
DataProvider_1.DongKetNoi(Con);
return(listbn);
}
// sua benh nhan
public static bool SuaBenhNhan(BENHNHAN bnDTO)
{
string sTruyVan = string.Format("update BENHNHAN set TenBN = N'{0}', NgaySinh=N'{1}', DiaChi=N'{2}', DienThoai='{3}', GioiTinh=N'{4}' where MaBN={5}", bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DiaChi1, bnDTO.DienThoai1, bnDTO.GioiTinh1, bnDTO.MaBN1);
Con = DataProvider_1.KetNoi();
try
{
// thuc thi truy van
bool KetQua = DataProvider_1.ThucThiTruyVanNonQuery(sTruyVan, Con);
// dong truy van
DataProvider_1.DongKetNoi(Con);
if (KetQua == true)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(false);
}
}
public static DataTable BangKiemTraTonTai(BENHNHAN bnDTO)
{
string sTruyVan = string.Format("SELECT MaBN from BENHNHAN WHERE TinhTrangTonTai = '1' and (NgaySinh = N'{1}' and (DienThoai = '{2}' and GioiTinh = N'{3}'))", bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DienThoai1, bnDTO.GioiTinh1); // MaBN tu dong tang
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
return(dt);
}
public List <BENHNHAN> Xem()
{
List <BENHNHAN> benhnhan = new List <BENHNHAN>();
string query = "select * from BENHNHAN";
DataTable data = DataProvider.Instance.ExecuteQuery(query);
foreach (DataGridViewRow item in data.Rows)
{
int MaBN = (int)item.Cells["MaBN"].Value;
string TenBN = item.Cells["TenBN"].Value.ToString();
DateTime NgaySinh = (DateTime)item.Cells["NgaySinh"].Value;
string DiaChi = item.Cells["DiaChi"].Value.ToString();
string DienThoai = item.Cells["DienThoai"].Value.ToString();
string GioiTinh = item.Cells["GioiTinh"].Value.ToString();
int TinhTrangTonTai = (int)item.Cells["TinhTrangTonTai"].Value;
BENHNHAN BnMoi = new BENHNHAN(MaBN, TenBN, NgaySinh, DiaChi, DienThoai, GioiTinh, TinhTrangTonTai);
benhnhan.Add(BnMoi);
}
return(benhnhan);
}
//
public static DataTable TaoBang(BENHNHAN bnDTO)
{
//string x = "BN003";
//string query = "select pk.MaPK from BENHNHAN bn , PHIEUKHAM pk where bn.MaBN = pk.MaBN and bn.MaBN ='BN001'";
//string query = "select pk.MaPK from BENHNHAN bn , PHIEUKHAM pk where bn.MaBN = pk.MaBN and bn.MaBN ='"+bnDTO.MaBN1+"' ";
//string query = string.Format("select pk.MaPK from BENHNHAN bn , PHIEUKHAM pk where bn.MaBN = pk.MaBN and bn.MaBN ='{0}'", x);
// string query = string.Format("select PHIEUKHAM.MaPK from BENHNHAN , PHIEUKHAM where BENHNHAN.MaBN = PHIEUKHAM.MaBN and BENHNHAN.MaBN =N'{0}'", bnDTO.MaBN1);
string query = string.Format("select PHIEUKHAM.MaPK from PHIEUKHAM where PHIEUKHAM.MaBN =N'{0}'", bnDTO.MaBN1);
Con = DataProvider_1.KetNoi();
DataTable dt = DataProvider_1.LayDataTable(query, Con);
DataProvider_1.DongKetNoi(Con);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return(dt);
}
}