private void btnSua_Click(object sender, EventArgs e)
{
string maDieuKien = this.tbMaDieuKien.Text;
string tenDieuKien = this.tbTenDieuKien.Text;
string dieuKien = this.tbDieuKien.Text;
maDieuKien = maDieuKien.Replace("'", "''");
maDieuKien = maDieuKien.Replace("\n", " ");
tenDieuKien = tenDieuKien.Replace("'", "''");
dieuKien = dieuKien.Replace("'", "''");
if (maDieuKien != null && tenDieuKien != null && dieuKien != null)
{
string query = "UPDATE DanhMucDieuKienXetNghiemNangCao SET TenDieuKien = N'" + tenDieuKien + "', DieuKien = N'" + dieuKien + "' WHERE MaDieuKien ='" + maDieuKien + "'";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) == 1)
{
MessageBox.Show("Đã Sửa Điều Kiện " + maDieuKien);
}
else
{
MessageBox.Show("Mã Điều Kiện Không Được Thay Đổi");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
khoiTaoDVKTCoDieuKien();
}
public void taoTable_2(string maCSKCB)
{
string tenTable = "xml123_" + maCSKCB;
string query = "";
query = "CREATE TABLE " + tenTable + "(ID numeric(18,0),XML1_ID numeric(18,0),Ky_QT numeric(10,0),CoSoKCB_ID numeric(18,0),Ma_CSKCB nvarchar(10),"
+ "Ma_LK nvarchar(100),MA_BN nvarchar(100),Ho_Ten nvarchar(1000),Ngay_Sinh nvarchar(15),Gioi_Tinh nvarchar(2),Ma_The nvarchar(20),Ma_DKBD nvarchar(10),"
+ "GT_The_Tu nvarchar(25),GT_The_Den nvarchar(25),Mien_Cung_CT numeric(18,2),Ngay_Vao nvarchar(25),Ngay_Ra nvarchar(25),So_Ngay_DTri numeric(10,0),Ma_LyDo_VVien nvarchar(2),Ma_Benh nvarchar(100),Ma_BenhKhac nvarchar(1000),Muc_Huong_XML1 numeric(18,2),T_TongChi numeric(18,0),T_BNTT numeric(18,0),T_BHTT numeric(18,0),T_BNCCT numeric(18,0),"
+ "T_XN numeric(18,0),T_CDHA numeric(18,0), T_Thuoc numeric(18,0),T_Mau numeric(18,0),T_TTPT numeric(18,0),T_VTYT numeric(18,0),T_DVKT_TyLe numeric(18,0),T_Thuoc_TyLe numeric(18,0),T_VTYT_TyLe numeric(18,0),"
+ "T_Kham numeric(18,0),T_Giuong numeric(18,0),T_VChuyen numeric(18,0),T_NgoaiDS numeric(18,0),T_NguonKhac numeric(18,0),Ma_Loai_KCB nvarchar(2),ID_CP numeric(18,0),Loai_CP nvarchar(10),Ma_CP nvarchar(40),"
+ "Ma_Vat_Tu nvarchar(40),Ma_Nhom nvarchar(3),Ten_CP nvarchar(300),DVT nvarchar(100),So_Dang_Ky nvarchar(100),Ham_Luong nvarchar(100),Duong_Dung nvarchar(100),So_Luong numeric(18,2),"
+ "So_Luong_BV numeric(18,2),Don_Gia numeric(18,2),Don_Gia_BV numeric(18,2),Thanh_Tien numeric(18,2),TyLe_TT numeric(18,2),Ngay_YL nvarchar(25),"
+ "Ngay_KQ nvarchar(25),T_NguonKhac_DTL numeric(18,0),T_BNTT_DTL numeric(18,0),"
+ "T_BHTT_DTL numeric(18,0),T_BNCCT_DTL numeric(18,0),T_NgoaiDS_DTL numeric(18,0),Muc_Huong_DTL numeric(10,0),TT_Thau nvarchar(100),Pham_Vi nvarchar(100),Ma_Giuong nvarchar(50),"
+ "T_TranTT numeric(18,2),Goi_VTYT nvarchar(100),Ten_Vat_Tu nvarchar(300),"
+ "Ten_Khoa nvarchar(100),Ma_Khoa nvarchar(20),Ma_Khoa_XML1 nvarchar(20),Ten_Khoa_XML1 nvarchar(300),Ten_Benh nvarchar(300),Ma_Bac_Si nvarchar(20),Ma_Tinh nvarchar(5),Ma_Tinh_The nvarchar(5))";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) > 0)
{
MessageBox.Show("Tạo Table Thành Công");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void btnXoa_Click(object sender, EventArgs e)
{
if (this.lbMaCSKCB.Text != null && this.lbTenCSKCB.Text != null)
{
string maCSKCB = this.lbMaCSKCB.Text;
string tenCSKCB = this.lbTenCSKCB.Text;
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
//Xóa dòng trong bảng dmxml123
//DBUtils.ExecuteNonQuery(query, conn);
//Xóa luôn table xml123_maCSKCB
string tenBangCanXoa = "xml123_" + maCSKCB;
string query = "DROP TABLE " + tenBangCanXoa;
DBUtils.ExecuteNonQuery(query, conn);
MessageBox.Show("Xóa CSKCB " + maCSKCB + " Thành Công");
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
else
{
MessageBox.Show("Chưa Chọn Dữ Liệu Cần Xóa!");
}
khoiTaodmCSKCB();
this.lbSTT.Text = "";
this.lbMaCSKCB.Text = "";
this.lbTenCSKCB.Text = "";
}
private void btnSua_Click(object sender, EventArgs e)
{
string MaCSKCB = this.tbMaCSKCB.Text;
string TenCSKCB = this.tbTenCSKCB.Text;
MaCSKCB = MaCSKCB.Replace("'", "''");
TenCSKCB = TenCSKCB.Replace("'", "''");
if (MaCSKCB != null && TenCSKCB != null)
{
string query = "UPDATE DanhMucCoSoKCB SET MaCSKCB = N'" + MaCSKCB + "', TenCSKCB = N'" + TenCSKCB + "' WHERE MaCSKCB ='" + MaCSKCB + "'";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) == 1)
{
MessageBox.Show("Đã Sửa Điều Kiện " + MaCSKCB);
}
else
{
MessageBox.Show("Mã Điều Kiện Không Được Thay Đổi");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
khoiTaoDanhMuc();
}
public void taoProcedure(string maCSKCB)
{
string tenProcedure = "Insert_xml123_" + maCSKCB;
string tenTable = "xml123_" + maCSKCB;
string tenType = "udt_xml123_" + maCSKCB;
string query = "CREATE PROCEDURE " + tenProcedure + " "
+ "@xml123 " + tenType + " READONLY "
+ "AS "
+ "BEGIN "
+ "INSERT INTO " + tenTable + " "
+ "(ID, XML1_ID, Ky_QT, CoSoKCB_ID, Ma_CSKCB, Ma_LK, MA_BN, Ho_Ten, Ngay_Sinh, Gioi_Tinh, Ma_The, Ma_DKBD, GT_The_Tu, GT_The_Den, Mien_Cung_CT, Ngay_Vao, Ngay_Ra, So_Ngay_DTri, Ma_LyDo_VVien, Ma_Benh, Ma_BenhKhac, Muc_Huong_XML1, T_TongChi, T_BNTT, T_BHTT, T_BNCCT, T_XN, T_CDHA, T_Thuoc, T_Mau, T_TTPT, T_VTYT, T_DVKT_TyLe, T_Thuoc_TyLe, T_VTYT_TyLe, T_Kham, T_Giuong, T_VChuyen, T_NgoaiDS, T_NguonKhac, Ma_Loai_KCB, ID_CP, Loai_CP, Ma_CP, Ma_Vat_Tu, Ma_Nhom, Ten_CP, DVT, So_Dang_Ky, Ham_Luong, Duong_Dung, So_Luong, So_Luong_BV, Don_Gia, Don_Gia_BV, Thanh_Tien, TyLe_TT, Ngay_YL, Ngay_KQ, T_NguonKhac_DTL, T_BNTT_DTL, T_BHTT_DTL, T_BNCCT_DTL, T_NgoaiDS_DTL, Muc_Huong_DTL, TT_Thau, Pham_Vi, Ma_Giuong, T_TranTT, Goi_VTYT, Ten_Vat_Tu, Ten_Khoa, Ma_Khoa, Ma_Khoa_XML1, Ten_Khoa_XML1, Ten_Benh, Ma_Bac_Si, Ma_Tinh, Ma_Tinh_The) "
+ "SELECT ID, XML1_ID, Ky_QT, CoSoKCB_ID, Ma_CSKCB, Ma_LK, MA_BN, Ho_Ten, Ngay_Sinh, Gioi_Tinh, Ma_The, Ma_DKBD, GT_The_Tu, GT_The_Den, Mien_Cung_CT, Ngay_Vao, Ngay_Ra, So_Ngay_DTri, Ma_LyDo_VVien, Ma_Benh, Ma_BenhKhac, Muc_Huong_XML1, T_TongChi, T_BNTT, T_BHTT, T_BNCCT, T_XN, T_CDHA, T_Thuoc, T_Mau, T_TTPT, T_VTYT, T_DVKT_TyLe, T_Thuoc_TyLe, T_VTYT_TyLe, T_Kham, T_Giuong, T_VChuyen, T_NgoaiDS, T_NguonKhac, Ma_Loai_KCB, ID_CP, Loai_CP, Ma_CP, Ma_Vat_Tu, Ma_Nhom, Ten_CP, DVT, So_Dang_Ky, Ham_Luong, Duong_Dung, So_Luong, So_Luong_BV, Don_Gia, Don_Gia_BV, Thanh_Tien, TyLe_TT, Ngay_YL, Ngay_KQ, T_NguonKhac_DTL, T_BNTT_DTL, T_BHTT_DTL, T_BNCCT_DTL, T_NgoaiDS_DTL, Muc_Huong_DTL, TT_Thau, Pham_Vi, Ma_Giuong, T_TranTT, Goi_VTYT, Ten_Vat_Tu, Ten_Khoa, Ma_Khoa, Ma_Khoa_XML1, Ten_Khoa_XML1, Ten_Benh, Ma_Bac_Si, Ma_Tinh, Ma_Tinh_The "
+ "FROM @xml123 "
+ "END";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) > 0)
{
///MessageBox.Show("Tạo Procedure Thành Công");
}
}
catch (SqlException ex)
{
//MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void themVaoCSDLCoSoKCB_BULKCOPY()
{
string _FileName = _FileNames[0];
string tenTable = "DanhMucCoSoKCB";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
string query = "DBCC DROPCLEANBUFFERS";
DBUtils.ExecuteNonQuery(query, conn);
query = "DBCC FREEPROCCACHE";
DBUtils.ExecuteNonQuery(query, conn);
//Xóa table nếu tồn tại
query = "begin try drop table " + tenTable + " end try begin catch end catch";
DBUtils.ExecuteNonQuery(query, conn);
//Tạo table
taoTable_dmCSKCB(tenTable);
try
{
string constr = "";
if (System.IO.Path.GetExtension(_FileName).ToUpper() == ".XLS")
{
constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""", _FileName);
}
else
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""", _FileName);
}
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""", _FileName);
OleDbConnection Econ = new OleDbConnection(constr);
string Query = string.Format("Select [MACSKCB], [TENCSKCB] FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
Exceldt.AcceptChanges();
SqlBulkCopy objbulk = new SqlBulkCopy(conn);
objbulk.DestinationTableName = tenTable;
//Mapping Table column
objbulk.ColumnMappings.Add("[MACSKCB]", "MACSKCB");
objbulk.ColumnMappings.Add("[TENCSKCB]", "TENCSKCB");
objbulk.WriteToServer(Exceldt);
MessageBox.Show("Ghi thành công danh mục Cơ sở Khám chữa bệnh!", "Thành Công", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show(string.Format("Dữ liệu không đúng định dạng!"), "Không Thành Công", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
