private void DeleteTableTemp() { using (SqlConnection connect = new SqlConnection(GetStrConnect.GetStrDsc())) { connect.Open(); SqlDataAdapter sqlDAdetail; string query = "SELECT name FROM sys.tables WHERE name LIKE 'zzzlvimport%'"; sqlDAdetail = new SqlDataAdapter(query, connect); var dsdetail = new DataSet(); sqlDAdetail.Fill(dsdetail); var ListItem = dsdetail.Tables[0]; foreach (DataRow item in ListItem.Rows) { SqlCommand command = new SqlCommand("DROP TABLE " + item["name"], connect); command.ExecuteNonQuery(); } } }
private void ProcessImport(DoWorkEventArgs e) { try { // Đọc File XML Data XmlDocument docProcess = new XmlDocument(); docProcess.Load(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) + "\\dataSrcExportCheck.xml"); //lấy Table từ table sau khi check chọn var Table = docProcess.GetElementsByTagName("Function"); foreach (XmlNode item in Table) { table.Add(new DataDetail { ID = item.SelectNodes("ID")?.Item(0)?.InnerText, Table = item.SelectNodes("Table")?.Item(0)?.InnerText, // Name = item.SelectNodes("Name").Item(0).InnerText, Condition = item.SelectNodes("Condition")?.Item(0)?.InnerText }); foreach (XmlNode itemdetail in item.SelectNodes("Detail")) { detail.Add(new Detail { ID = item.SelectNodes("ID")?.Item(0)?.InnerText, DetailName = itemdetail.InnerXml, ConditionDetail = itemdetail.Attributes[0].Value }); count.Add(itemdetail.Attributes[0].Value); } count.Add(item.SelectNodes("Table")?.Item(0)?.InnerText); } var Posted = docProcess.GetElementsByTagName("Posted")?.Item(0)?.InnerText; //var Overwrite = docProcess.GetElementsByTagName("Overwrite").Item(0).InnerText; // kết nối data lấy dữ liệu bảng muốn chuyển using (SqlConnection sourceConnection = new SqlConnection(GetStrConnect.GetStrSrc())) { sourceConnection.Open(); //Lọc từng Table từ danh sách trong file XML int i = 0; //Import Master foreach (var tableName in table) { //Lấy danh sách Data string queryMaster = $"Select {tableName.Table}.* from {tableName.Table} Where {tableName.Condition}"; SqlDataAdapter sqlDA; sqlDA = new SqlDataAdapter(queryMaster, sourceConnection); var ds = new DataSet(); sqlDA.Fill(ds); //Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp List <string> column = new List <string>(); Dictionary <string, string> openWith = new Dictionary <string, string>(); DataTable dataTable = new DataTable(); //check Posted if (Posted.ToString() == "True" && i == 0) { foreach (DataRow row in ds.Tables[0].Rows) { row.SetField("Posted", 0); } } dataTable = ds.Tables[0]; //Lấy danh sách Column trong Danh sách //Lấy Khóa chính trong danh sách Column foreach (var item in dataTable.Columns) { openWith.Add(item.ToString(), item.ToString()); column.Add(item.ToString()); } using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())) { descConnection.Open(); CopyData(descConnection, tableName.Table, ds.Tables[0], true, openWith, tableName.Table, null, column[0]); this.Invoke(OnUpdateLog, "Import " + tableName.Table + ", Rows: " + dataTable.Rows.Count); } //===========// //Import Detail// foreach (var item in detail) { if (item.ID == tableName.ID) { SqlDataAdapter sqlDAdetail; string query = $"Select {item.DetailName}.* from {tableName.Table},{item.DetailName} Where {tableName.Table}.{item.ConditionDetail} = {item.DetailName}.{item.ConditionDetail} and {tableName.Condition} "; sqlDAdetail = new SqlDataAdapter(query, sourceConnection); var dsdetail = new DataSet(); sqlDAdetail.Fill(dsdetail); //Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp List <string> columnDetail = new List <string>(); Dictionary <string, string> openWithDetail = new Dictionary <string, string>(); //DataTable dataTableDetail = new DataTable(); //DataTable results = new DataTable(); //dataTableDetail = dsdetail.Tables[0]; //var resultsDetail = (from tableMaster in dataTable.AsEnumerable() // join tableDetail in dataTableDetail.AsEnumerable() on tableMaster[0] equals tableDetail[item.ConditionDetail] // select tableDetail).ToList(); //if (!resultsDetail.Any()){ // continue; //} //results = resultsDetail.CopyToDataTable(); //Lấy danh sách Column trong Danh sách //Lấy Khóa chính trong danh sách Column foreach (var itemDetail in dsdetail.Tables[0].Columns) { openWithDetail.Add(itemDetail.ToString(), itemDetail.ToString()); columnDetail.Add(itemDetail.ToString()); } using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())){ descConnection.Open(); CopyData(descConnection, item.DetailName, dsdetail.Tables[0], true, openWithDetail, item.DetailName, null, columnDetail[0]); this.Invoke(OnUpdateLog, "Import " + item.DetailName + ", Rows: " + dsdetail.Tables[0].Rows.Count); } } } bw.ReportProgress((100 * (i + 1)) / table.Count); i++; } } e.Result = ""; } catch (Exception ex) { e.Result = ex.Message; } }
private void btnchitiet_Click(object sender, EventArgs e) { string textname = ""; int textcount = 0; List <TableOld> TableOld = new List <TableOld>(); List <TableNew> TableNew = new List <TableNew>(); int CountSrc = 0, CountDsc = 0; DataSet dsdetailSrc, dsSrc, dsDsc, dsdetailDsc; using (SqlConnection conn = new SqlConnection(GetStrConnect.GetStrDsc())) { conn.Open(); foreach (var tableName in table) { //Lấy danh sách Data SqlDataAdapter sqlDA; sqlDA = new SqlDataAdapter("Select zzzlvimport" + tableName.Table + ".* from zzzlvimport" + tableName.Table + " Where " + tableName.Condition, conn); dsSrc = new DataSet(); sqlDA.Fill(dsSrc); TableOld.Add(new TableOld { tableNameOld = tableName.Table, RowsCountOld = (int)dsSrc.Tables[0].Rows.Count, }); foreach (var item in detail) { if (item.ID == tableName.ID) { SqlDataAdapter sqlDAdetail; string query = "Select * From zzzlvimport" + item.DetailName; sqlDAdetail = new SqlDataAdapter(query, conn); dsdetailSrc = new DataSet(); sqlDAdetail.Fill(dsdetailSrc); TableOld.Add(new TableOld { tableNameOld = item.DetailName, RowsCountOld = dsdetailSrc.Tables[0].Rows.Count, }); } } } foreach (var tableName in table) { //Lấy danh sách Data SqlDataAdapter sqlDA; sqlDA = new SqlDataAdapter("Select " + tableName.Table + ".* from " + tableName.Table + " Where " + tableName.Condition, conn); dsDsc = new DataSet(); sqlDA.Fill(dsDsc); TableNew.Add(new TableNew { tableNameNew = tableName.Table, RowsCountNew = (int)dsDsc.Tables[0].Rows.Count, }); foreach (var item in detail) { if (item.ID == tableName.ID) { SqlDataAdapter sqlDAdetail; string query = "Select * From " + item.DetailName; sqlDAdetail = new SqlDataAdapter(query, conn); dsdetailDsc = new DataSet(); sqlDAdetail.Fill(dsdetailDsc); TableNew.Add(new TableNew { tableNameNew = item.DetailName, RowsCountNew = (int)dsdetailDsc.Tables[0].Rows.Count, }); } } } foreach (var itemold in TableOld) { foreach (var itemnew in TableNew) { if (itemold.tableNameOld == itemnew.tableNameNew) { textname += itemold.tableNameOld; textname += "\r\n"; textcount += itemnew.RowsCountNew - itemold.RowsCountOld; //textcount += ; } } } FrmShow frmShow = new FrmShow(textname + " " + textcount); frmShow.Show(); //MessageBox.Show(textname + " " + textcount, "", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void ExistTable() { using (SqlConnection sourceConnection = new SqlConnection(GetStrConnect.GetStrSrc())) { sourceConnection.Open(); foreach (var tablenameTrans in table) { List <DataTransfer> tabletrans = new List <DataTransfer>(); var tran = sourceConnection.BeginTransaction(); using (SqlCommand cmd = new SqlCommand("AD_spImportTable", sourceConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Transaction = tran; cmd.Parameters.Add("@TableToInsert", SqlDbType.VarChar, 50).Value = tablenameTrans.Table; using (SqlDataReader rdr = cmd.ExecuteReader()) { // iterate through results, printing each to console while (rdr.Read()) { if (rdr["TableName"].ToString() == tablenameTrans.Table) { continue; } tabletrans.Add(new DataTransfer() { Table = rdr["TableName"].ToString(), }); //Console.WriteLine("Product: {0,-35} Total: {1,2}", rdr["TableName"], rdr["Level"]); } } tran.Commit(); } foreach (var tableName in tabletrans) { SqlDataAdapter sqlDA; sqlDA = new SqlDataAdapter("select * from " + tableName.Table, sourceConnection); var ds = new DataSet(); sqlDA.Fill(ds); //Khởi tạo danh sách Column, dataTable, Khóa chính cột ---- Khi chạy từng vòng lặp List <string> column = new List <string>(); Dictionary <string, string> openWith = new Dictionary <string, string>(); DataTable dataTable = new DataTable(); dataTable = ds.Tables[0]; //Lấy danh sách Column trong Danh sách //Lấy Khóa chính trong danh sách Column foreach (var item in dataTable.Columns) { openWith.Add(item.ToString(), item.ToString()); column.Add(item.ToString()); } using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())) { descConnection.Open(); CopyDataExsit(descConnection, tableName.Table, ds.Tables[0], bool.Parse(Overwrite), openWith, tableName.Table, null, column[0]); } } } foreach (var tablenameTrans in detail) { List <DataTransfer> tabletrans = new List <DataTransfer>(); var tran = sourceConnection.BeginTransaction(); using (SqlCommand cmd = new SqlCommand("AD_spImportTable", sourceConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Transaction = tran; cmd.Parameters.Add("@TableToInsert", SqlDbType.VarChar, 50).Value = tablenameTrans.DetailName; using (SqlDataReader rdr = cmd.ExecuteReader()) { // iterate through results, printing each to console while (rdr.Read()) { if (rdr["TableName"].ToString() == tablenameTrans.DetailName) { continue; } tabletrans.Add(new DataTransfer() { Table = rdr["TableName"].ToString(), }); } } tran.Commit(); } foreach (var tableName in tabletrans) { SqlDataAdapter sqlDA; sqlDA = new SqlDataAdapter("select * from " + tableName.Table, sourceConnection); var ds = new DataSet(); sqlDA.Fill(ds); //Khởi tạo danh sách Column, dataTable, Khóa chính cột ---- Khi chạy từng vòng lặp List <string> column = new List <string>(); Dictionary <string, string> openWith = new Dictionary <string, string>(); DataTable dataTable = new DataTable(); dataTable = ds.Tables[0]; //Lấy danh sách Column trong Danh sách //Lấy Khóa chính trong danh sách Column foreach (var item in dataTable.Columns) { openWith.Add(item.ToString(), item.ToString()); column.Add(item.ToString()); } using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())) { descConnection.Open(); CopyDataExsit(descConnection, tableName.Table, ds.Tables[0], bool.Parse(Overwrite), openWith, tableName.Table, null, column[0]); } } } } }
private void ImportData() { // kết nối data lấy dữ liệu bảng muốn chuyển using (SqlConnection sourceConnection = new SqlConnection(GetStrConnect.GetStrDsc())) { sourceConnection.Open(); //Lọc từng Table từ danh sách trong file XML int i = 1; //Import Master foreach (var tableName in table) { string queryImport = "Select zzzlvimport" + tableName.Table + ".* from zzzlvimport" + tableName.Table + " Where " + tableName.Condition; //Lấy danh sách Data SqlDataAdapter sqlDA; sqlDA = new SqlDataAdapter(queryImport, sourceConnection); var ds = new DataSet(); sqlDA.Fill(ds); //Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp List <string> column = new List <string>(); Dictionary <string, string> openWith = new Dictionary <string, string>(); DataTable dataTable = new DataTable(); //check Posted if (Posted.ToString() == "True" && i == 1) { foreach (DataRow row in ds.Tables[0].Rows) { row.SetField("Posted", 0); } } dataTable = ds.Tables[0]; //Lấy danh sách Column trong Danh sách //Lấy Khóa chính trong danh sách Column foreach (var item in dataTable.Columns) { openWith.Add(item.ToString(), item.ToString()); column.Add(item.ToString()); } using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())) { descConnection.Open(); CopyData(descConnection, tableName.Table, ds.Tables[0], bool.Parse(Overwrite), openWith, tableName.Table, null, column[0]); this.Invoke(OnUpdateLog, "Import " + tableName.Table + ", Rows: " + dataTable.Rows.Count); } //===========// //Import Detail// foreach (var item in detail) { if (item.ID == tableName.ID) { SqlDataAdapter sqlDAdetail; string query = $" SELECT zzzlvimport{item.DetailName}.* FROM zzzlvimport{item.DetailName} ,zzzlvimport{tableName.Table} Where {tableName.Condition} and zzzlvimport{tableName.Table}.{item.ConditionDetail}=zzzlvimport{item.DetailName}.{item.ConditionDetail}"; sqlDAdetail = new SqlDataAdapter(query, sourceConnection); var dsdetail = new DataSet(); sqlDAdetail.Fill(dsdetail); //Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp List <string> columnDetail = new List <string>(); Dictionary <string, string> openWithDetail = new Dictionary <string, string>(); //DataTable dataTableDetail = new DataTable(); //DataTable results = new DataTable(); //dataTableDetail = dsdetail.Tables[0]; //var resultsDetail = from tableMaster in dataTable.AsEnumerable() // join tableDetail in dataTableDetail.AsEnumerable() on tableMaster[0] equals tableDetail[item.ConditionDetail] // select tableDetail; //if (!resultsDetail.Any()) //{ // continue; //} //results = resultsDetail.CopyToDataTable(); //Lấy danh sách Column trong Danh sách //Lấy Khóa chính trong danh sách Column foreach (var itemDetail in dsdetail.Tables[0].Columns) { openWithDetail.Add(itemDetail.ToString(), itemDetail.ToString()); columnDetail.Add(itemDetail.ToString()); } var a = dsdetail.Tables[0].Rows.Count; using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())) { descConnection.Open(); CopyData(descConnection, item.DetailName, dsdetail.Tables[0], bool.Parse(Overwrite), openWithDetail, item.DetailName, null, columnDetail[0]); this.Invoke(OnUpdateLog, "Import " + item.DetailName + ", Rows: " + dsdetail.Tables[0].Rows.Count); } } } bw.ReportProgress((100 * i) / table.Count); i++; } } }