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); } }