public void GetDatabaseList() { try { using (SqlConnection con = new SqlConnection(GetStrConnect.GetStrSrc())) { con.Open(); // Set up a command with the given query and associate // this with the current connection. using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandText = "SELECT BatchNo,BatchDesc from GL_tblBatchList "; if (chkDocDate.Checked) { cmd.CommandText += "Where "; var adapter = new SqlDataAdapter(cmd); var ds = new DataSet(); adapter.Fill(ds); gridLookUpEditBatch.Properties.DataSource = ds.Tables[0]; gridLookUpEditBatch.Properties.DisplayMember = "BatchNo"; gridLookUpEditBatch.Properties.ValueMember = "BatchNo"; } } } } catch (Exception ex) { MessageBox.Show("Connect Fail"); } }
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; } }
public string CheckData() { try { string[] BatchCheck = gridLookUpEditBatch.Text.Split(','); DataTable dtLockData = new DataTable(); DataTable dtdata = new DataTable(); using (SqlConnection conn = new SqlConnection(GetStrConnect.GetStrSrc())) { conn.Open(); using (SqlCommand cmd = new SqlCommand("select * from AD_tblLockDataParameter", conn)) { dtLockData.Load(cmd.ExecuteReader()); } string Condition; if (data[0].Condition != null) { Condition = data[0].Condition + " and "; } else { Condition = ""; } using (SqlCommand cmd1 = new SqlCommand()) { cmd1.Connection = conn; cmd1.CommandType = CommandType.Text; if (chkDocDate.Checked && chkBatchNo.Checked) { cmd1.CommandText = $"select * from {data[0].Table} Where {Condition} DocumentDate between '{dtpfromdate.Value.ToShortDateString()}' and '{dtptodate.Value.ToShortDateString()}' and"; cmd1.CommandText += "("; foreach (var value in BatchCheck) { cmd1.CommandText += $" BatchNo = '{value}' or"; } cmd1.CommandText = cmd1.CommandText.Substring(0, cmd1.CommandText.Length - 2); cmd1.CommandText += ")"; dtdata.Load(cmd1.ExecuteReader()); if (dtdata.Rows.Count <= 0) { return("0"); } else { //check ngày khóa chứng từ foreach (DataRow row in dtLockData.Rows) { foreach (var item in data) // duyệt danh sách chọn { if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa { foreach (DataRow rowdata in dtdata.Rows) { if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"]) { return(item.Name); } } } } } } } else if (chkBatchNo.Checked) { cmd1.CommandText = $"select * from {data[0].Table} Where {Condition} "; cmd1.CommandText += "("; foreach (var value in BatchCheck) { cmd1.CommandText += $" BatchNo = '{value}' or"; } cmd1.CommandText = cmd1.CommandText.Substring(0, cmd1.CommandText.Length - 2); cmd1.CommandText += ")"; dtdata.Load(cmd1.ExecuteReader()); if (dtdata.Rows.Count <= 0) { return("0"); } else { //check ngày khóa chứng từ foreach (DataRow row in dtLockData.Rows) { foreach (var item in data) // duyệt danh sách chọn { if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa { foreach (DataRow rowdata in dtdata.Rows) { if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"]) { return(item.Name); } } } } } } } else if (chkDocDate.Checked) { cmd1.CommandText = "select * from " + data[0].Table + " Where " + Condition + " DocumentDate between '" + dtpfromdate.Value.ToShortDateString() + "' and '" + dtptodate.Value.ToShortDateString() + "'"; dtdata.Load(cmd1.ExecuteReader()); if (dtdata.Rows.Count <= 0) { return("0"); } else { //check ngày khóa chứng từ foreach (DataRow row in dtLockData.Rows) { foreach (var item in data) // duyệt danh sách chọn { if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa { foreach (DataRow rowdata in dtdata.Rows) { if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"]) { return(item.Name); } } } } } } } else { cmd1.CommandText = "select * from " + data[0].Table + " Where " + Condition.Replace("and ", ""); dtdata.Load(cmd1.ExecuteReader()); if (dtdata.Rows.Count <= 0) { return("0"); } else { //check ngày khóa chứng từ foreach (DataRow row in dtLockData.Rows) { foreach (var item in data) // duyệt danh sách chọn { if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa { foreach (DataRow rowdata in dtdata.Rows) { if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"]) { return(item.Name); } } } } } } } } } return("1"); } catch (Exception ex) { MessageBox.Show(@"Lỗi: " + ex.Message); return("0"); } }
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]); } } } } }