public void inputbasepricelistdetail(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); datainportF inf = (datainportF)obj; string filename = inf.filename; // string filename = theDialog.FileName.ToString(); // string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); dc.ExecuteCommand("DELETE FROM tbl_KAbaseprice"); // dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod); dc.CommandTimeout = 0; dc.SubmitChanges(); ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); DataTable batable = new DataTable(); batable.Columns.Add("PriceList", typeof(string)); batable.Columns.Add("Material", typeof(string)); batable.Columns.Add("MaterialNAme", typeof(string)); batable.Columns.Add("Amount", typeof(double)); batable.Columns.Add("Unit", typeof(string)); batable.Columns.Add("UoM", typeof(string)); batable.Columns.Add("Valid_From", typeof(DateTime)); batable.Columns.Add("Valid_to", typeof(DateTime)); string Pricelist = ""; int columpricelist = 0; int columpmaterial = 0; int columname = 0; int columpamount = 0; int columunit = 0; int columUoM = 0; int columValid_From = 0; int columValid_to = 0; int headindex = 0; for (int rowid = 0; rowid < sourceData.Rows.Count; rowid++) { headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); if (value != null) { #region setcolum if (value.Trim() == "CnTy") { columpricelist = columid; headindex = 0; } if (value.Trim() == "Material") { if (columname == 0) { columpmaterial = columid; headindex = 0; } } if (value.Trim() == "Material") { if (columpmaterial != 0) { columname = columid; headindex = 0; } } if (value.Trim() == "Amount") { columpamount = columid; headindex = 0; } if (value.Trim() == "Unit") { columunit = columid; headindex = 0; } if (value.Trim() == "UoM") { columUoM = columid; headindex = 0; } if (value.Trim() == "Valid From") { columValid_From = columid; headindex = 0; } if (value.Trim() == "Valid to") { columValid_to = columid; headindex = 0; } #endregion // view basetable } //------------ } // colum #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string valuepricelist = sourceData.Rows[rowid][columpricelist].ToString(); if (headindex != 0 && valuepricelist != "" && valuepricelist != "YPR0") { Pricelist = valuepricelist; } if (headindex != 0 && valuepricelist == "YPR0") { DataRow dr = batable.NewRow(); dr["PriceList"] = Pricelist.Trim(); // dr["Material"] = Utils.GetValueOfCellInExcel(worksheet, rowid, columpmaterial); dr["Material"] = sourceData.Rows[rowid][columpmaterial].ToString().Trim(); dr["MaterialNAme"] = sourceData.Rows[rowid][columname].ToString().Trim(); //Utils.GetValueOfCellInExcel(worksheet, rowid, columname); dr["Amount"] = double.Parse(sourceData.Rows[rowid][columpamount].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount); dr["Unit"] = sourceData.Rows[rowid][columunit].ToString().Trim(); // Utils.GetValueOfCellInExcel(worksheet, rowid, columunit); dr["UoM"] = sourceData.Rows[rowid][columUoM].ToString().Trim(); // Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM); dr["Valid_From"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_From].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_From); dr["Valid_to"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_to].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); batable.Rows.Add(dr); } #endregion }// row //conpy to server string destConnString = Utils.getConnectionstr(); //adapter.FillSchema(sourceData, SchemaType.Source); //sourceData.Columns["Posting Date"].DataType = typeof(DateTime); //sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float); //sourceData.Columns["Billed Qty"].DataType = typeof(float); //sourceData.Columns["Cond Value"].DataType = typeof(float); //sourceData.Columns["Sales Org"].DataType = typeof(string); //sourceData.Columns["Cust Name"].DataType = typeof(string); //sourceData.Columns["Outbound Delivery"].DataType = typeof(string); //sourceData.Columns["Mat Group"].DataType = typeof(string); //sourceData.Columns["Mat Group Text"].DataType = typeof(string); //sourceData.Columns["UoM"].DataType = typeof(string); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.DestinationTableName = "tbl_KAbaseprice"; // Write from the source to the destination. bulkCopy.BulkCopyTimeout = 0; bulkCopy.ColumnMappings.Add("PriceList", "PriceList"); bulkCopy.ColumnMappings.Add("Material", "Material"); bulkCopy.ColumnMappings.Add("MaterialNAme", "MaterialNAme"); bulkCopy.ColumnMappings.Add("Amount", "Amount"); bulkCopy.ColumnMappings.Add("Unit", "Unit"); bulkCopy.ColumnMappings.Add("UoM", "UoM"); bulkCopy.ColumnMappings.Add("Valid_From", "[Valid From]"); bulkCopy.ColumnMappings.Add("Valid_to", "[Valid to]"); //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to"); //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to"); //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to"); //bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } //copy to server // string connection_string = Utils.getConnectionstr(); // LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); // var typeffmain = typeof(tbl_KAbaseprice); // var typeffsub = typeof(tbl_KAbaseprice); // VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", ""); // inputcdata1.ShowDialog(); // View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA"); // TB.ShowDialog(); // } }
private void importsexcel(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); Product md = new Product(); bool kq = md.Deleteprodctlist(); datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //------ //---------------fill data System.Data.DataTable sourceData = new System.Data.DataTable(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { try { conn.Open(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Open conext !", MessageBoxButtons.OK, MessageBoxIcon.Error); } // Get the data from the source table as a SqlDataReader. OleDbCommand command = new OleDbCommand( @"SELECT MatNumber, MatText ,UoM,Pcrate,Litter,UnitSize,PackUnit, Ucrate FROM [Sheet1$] WHERE ( MatNumber is not null ) ", conn); // AND ISNUMERIC (MatNumber) OleDbDataAdapter adapter = new OleDbDataAdapter(command); // adapter.FillSchema(sourceData, SchemaType.Source); // sourceData.Columns["Posting Date"].DataType = typeof(DateTime); //sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float); //sourceData.Columns["Billed Qty"].DataType = typeof(float); //sourceData.Columns["Cond Value"].DataType = typeof(float); //sourceData.Columns["Sales Org"].DataType = typeof(string); //sourceData.Columns["Cust Name"].DataType = typeof(string); //sourceData.Columns["Outbound Delivery"].DataType = typeof(string); //sourceData.Columns["Mat Group"].DataType = typeof(string); //sourceData.Columns["Mat Group Text"].DataType = typeof(string); //sourceData.Columns["UoM"].DataType = typeof(string); try { adapter.Fill(sourceData); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill !", MessageBoxButtons.OK, MessageBoxIcon.Error); } conn.Close(); } // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { //@"SELECT MatNumber, MatText ,UoM,Pcrate, // Ucrate FROM [Sheet1$] // WHERE ( MatNumber is not null ) AND ISNUMERIC (MatNumber)", conn); // bulkCopy.DestinationTableName = "tbl_kaProductlist"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("MatNumber", "MatNumber"); bulkCopy.ColumnMappings.Add("MatText", "MatText"); bulkCopy.ColumnMappings.Add("UoM", "UoM"); bulkCopy.ColumnMappings.Add("Pcrate", "Pcrate"); bulkCopy.ColumnMappings.Add("Ucrate", "Ucrate"); bulkCopy.ColumnMappings.Add("PackUnit", "PackUnit"); bulkCopy.ColumnMappings.Add("Litter", "Litter"); bulkCopy.ColumnMappings.Add("UnitSize", "UnitSize"); // , #region tìm id // "Account" // "Assignment" // "Posting Date" // "Document Type" // "Document Number" // "Business Area" // "Amount in local currency" #endregion try { bulkCopy.WriteToServer(sourceData); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexcel2(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); Salesinput_ctrl md = new Salesinput_ctrl(); bool kq = md.deleteedlp(); //if (!kq) //{ // MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information); //} datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //------ //---------------fill data ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); // Sales Group Sales Group desc Sales Off Sales Office Desc System.Data.DataTable batable = new System.Data.DataTable(); // batable.Columns.Add("Soldto", typeof(double)); batable.Columns.Add("ContractNo", typeof(string)); batable.Columns.Add("BatchNo", typeof(double)); batable.Columns.Add("PayType", typeof(string)); batable.Columns.Add("PaidRequestAmt", typeof(double)); batable.Columns.Add("PaidNote", typeof(string)); batable.Columns.Add("PaymentDoc", typeof(string)); // PaymentDoc string username = Utils.getusername(); batable.Columns.Add("Username", typeof(string)); batable.Columns["Username"].DefaultValue = username; batable.Columns.Add("status", typeof(Boolean)); batable.Columns["status"].DefaultValue = false; #region setcolum int PaymentDocid = -1; int ContractNoid = -1; int BatchNoid = -1; int PayTypeid = -1; int PaidRequestAmtid = -1; int PaidNoteid = -1; // View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test"); // vi1.ShowDialog(); // int headindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim() == "ContractNo") { ContractNoid = columid; // headindex = rowid; } if (value.Trim() == ("BatchNo")) { BatchNoid = columid; // headindex = rowid; } if (value.Trim() == ("PayType")) { PayTypeid = columid; // headindex = rowid; } if (value.Trim() == ("PaidRequestAmt")) { PaidRequestAmtid = columid; // headindex = rowid; } if (value.Trim() == "PaidNote") { PaidNoteid = columid; // headindex = rowid; } if (value.Trim() == "PaymentDoc") { PaymentDocid = columid; // headindex = rowid; } #endregion } } // colum } // roww off heatder #endregion if (ContractNoid == -1) { MessageBox.Show("Please check ContractNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (BatchNoid == -1) { MessageBox.Show("Please check BatchNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PayTypeid == -1) { MessageBox.Show("Please check PayType colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PaidRequestAmtid == -1) { MessageBox.Show("Please check PaidRequestAmt colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PaidNoteid == -1) { MessageBox.Show("Please check PaidNote colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PaymentDocid == -1) { MessageBox.Show("Please check PaymentDoc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of massconfirm // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string PaidRequestAmt = sourceData.Rows[rowixd][PaidRequestAmtid].ToString(); if (PaidRequestAmt != "" && Utils.IsValidnumber(PaidRequestAmt)) { DataRow dr = batable.NewRow(); dr["ContractNo"] = sourceData.Rows[rowixd][ContractNoid].ToString().Trim(); dr["BatchNo"] = double.Parse(sourceData.Rows[rowixd][BatchNoid].ToString().Trim()); dr["PayType"] = sourceData.Rows[rowixd][PayTypeid].ToString().Trim(); dr["PaidRequestAmt"] = double.Parse(sourceData.Rows[rowixd][PaidRequestAmtid].ToString().Trim()); dr["PaidNote"] = sourceData.Rows[rowixd][PaidNoteid].ToString().Trim(); dr["PaymentDoc"] = sourceData.Rows[rowixd][PaymentDocid].ToString().Trim(); batable.Rows.Add(dr); } #endregion }// row string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tbl_MassConfirmTemp"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("ContractNo", "ContractNo"); bulkCopy.ColumnMappings.Add("BatchNo", "BatchNo"); bulkCopy.ColumnMappings.Add("PayType", "PayType"); bulkCopy.ColumnMappings.Add("PaidRequestAmt", "PaidRequestAmt"); bulkCopy.ColumnMappings.Add("PaidNote", "PaidNote"); bulkCopy.ColumnMappings.Add("PaymentDoc", "PaymentDoc"); bulkCopy.ColumnMappings.Add("Username", "Username"); bulkCopy.ColumnMappings.Add("status", "status"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexcel2(object obj) { // List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>(); edlpinput_ctrl md = new edlpinput_ctrl(); bool kq = md.deleteedlp(); datainportF inf = (datainportF)obj; string filename = inf.filename; // ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("Soldto", typeof(double)); batable.Columns.Add("SalesOrg", typeof(string)); batable.Columns.Add("CustName", typeof(string)); batable.Columns.Add("InvoiceDocNr", typeof(double)); batable.Columns.Add("OutboundDelivery", typeof(string)); batable.Columns.Add("MatNumber", typeof(string)); batable.Columns.Add("MatText", typeof(string)); batable.Columns.Add("BilledQty", typeof(double)); batable.Columns.Add("CondValue", typeof(double)); batable.Columns.Add("MatGroup", typeof(string)); batable.Columns.Add("MatGroupText", typeof(string)); batable.Columns.Add("UoM", typeof(string)); int Soldto = -1; int SalesOrg = -1; int CustName = -1; int InvoiceDocNr = -1; int OutboundDelivery = -1; int MatNumber = -1; int MatText = -1; int BilledQty = -1; int CondValue = -1; int MatGroup = -1; int MatGroupText = -1; int UoM = -1; int rowseet = sourceData.Rows.Count; if (rowseet > 5) { rowseet = 5; } for (int rowid = 0; rowid < rowseet; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region string value = sourceData.Rows[rowid][columid].ToString(); //OleDbCommand command = new OleDbCommand( // @"SELECT [], [],[],[], // [], [], [],[], // [], [], [], [] FROM [Sheet1$] // WHERE (([Invoice Doc Nr] is not null ) AND ([Sales Org]<> ''))", conn); if (value != null && value != "") { // #region setcolum if (value.Trim() == ("Sold-to")) { Soldto = columid; // headindex = rowid; } if (value.Trim() == ("Sales Org")) { SalesOrg = columid; // headindex = 0; } if (value.Trim() == ("Cust Name")) { CustName = columid; // headindex = 0; } if (value.Trim() == ("Invoice Doc Nr")) { InvoiceDocNr = columid; // headindex = 0; } if (value.Trim() == ("Outbound Delivery")) { OutboundDelivery = columid; } if (value.Trim() == ("Mat Number")) { MatNumber = columid; } if (value.Trim() == ("Mat Text")) { MatText = columid; } if (value.Trim() == ("Billed Qty")) { BilledQty = columid; } if (value.Trim() == ("Cond Value")) { CondValue = columid; } if (value.Trim() == ("Mat Group")) { MatGroup = columid; } if (value.Trim() == ("Mat Group Text")) { MatGroupText = columid; } if (value.Trim() == ("UoM")) { UoM = columid; } } #endregion } // colum } // roww off heatder #region if (Soldto == -1) { MessageBox.Show("Dữ liệu thiếu cột Sold to ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesOrg == -1) { MessageBox.Show("Dữ liệu thiếu cột Sales Org", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CustName == -1) { MessageBox.Show("Dữ liệu thiếu cột Cust Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (InvoiceDocNr == -1) { MessageBox.Show("Dữ liệu thiếu cột Invoice Doc Nr ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (OutboundDelivery == -1) { MessageBox.Show("Dữ liệu thiếu cột Outbound Delivery", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatNumber == -1) { MessageBox.Show("Dữ liệu thiếu cột Mat Number ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatText == -1) { MessageBox.Show("Dữ liệu thiếu cột Mat Text", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (BilledQty == -1) { MessageBox.Show("Dữ liệu thiếu cột Billed Qty ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CondValue == -1) { MessageBox.Show("Dữ liệu thiếu cột CondValue ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatGroup == -1) { MessageBox.Show("Dữ liệu thiếu cột MatGroup", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatGroupText == -1) { MessageBox.Show("Dữ liệu thiếu cột MatGroupText", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (UoM == -1) { MessageBox.Show("Dữ liệu thiếu cột UoM", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } #endregion for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string InvoiceDocNrv = sourceData.Rows[rowixd][InvoiceDocNr].ToString(); if (InvoiceDocNrv != "" && Utils.IsValidnumber(InvoiceDocNrv)) { if (double.Parse(InvoiceDocNrv) > 0) { DataRow dr = batable.NewRow(); dr["Soldto"] = double.Parse(sourceData.Rows[rowixd][Soldto].ToString()); dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrg].ToString().Trim(); dr["CustName"] = sourceData.Rows[rowixd][CustName].ToString().Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][InvoiceDocNr].ToString())) { dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString()); } dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString()); dr["OutboundDelivery"] = sourceData.Rows[rowixd][OutboundDelivery].ToString().Trim(); dr["MatNumber"] = sourceData.Rows[rowixd][MatNumber].ToString().Trim(); dr["MatText"] = sourceData.Rows[rowixd][MatText].ToString().Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][BilledQty].ToString())) { dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQty].ToString()); } if (Utils.IsValidnumber(sourceData.Rows[rowixd][CondValue].ToString())) { dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValue].ToString()); } dr["MatGroup"] = sourceData.Rows[rowixd][MatGroup].ToString().Trim(); dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupText].ToString().Trim(); dr["UoM"] = sourceData.Rows[rowixd][UoM].ToString().Trim(); batable.Rows.Add(dr); } } #endregion } // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tblEDLP"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("[Soldto]", "[Sold-to]"); bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]"); bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]"); bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]"); bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]"); bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]"); bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]"); bulkCopy.ColumnMappings.Add("[BilledQty]", "[Billed Qty]"); bulkCopy.ColumnMappings.Add("[CondValue]", "[Cond Value]"); bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]"); bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]"); bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexcel2(object obj) { string connection_string = Utils.getConnectionstr(); var db = new LinqtoSQLDataContext(connection_string); db.ExecuteCommand("DELETE FROM tbl_KaCustomer"); // dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod); db.SubmitChanges(); datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //---------------fill data System.Data.DataTable sourceData = new System.Data.DataTable(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { try { conn.Open(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill", MessageBoxButtons.OK, MessageBoxIcon.Error); } // Get the data from the source table as a SqlDataReader. // Customer Vendor SalesOrg FullName TradingName Street District City Telephone1 Telephone2 FaxNumber VATregistrationNo Indirect CustomerGroup SALORG_CTR OleDbCommand command = new OleDbCommand( @"SELECT Customer, SalesOrg, FullName, TradingName , Street, District, City , Telephone1, VATregistrationNo, Indirect, SALORG_CTR FROM [Sheet1$] WHERE (Customer IS NOT NULL)", conn); OleDbDataAdapter adapter = new OleDbDataAdapter(command); try { adapter.Fill(sourceData); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill", MessageBoxButtons.OK, MessageBoxIcon.Error); } conn.Close(); } /// Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); // sourceData.Columns.Add("SapCode"); // sourceData.Columns["SapCode"].DefaultValue = true; //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { // @"SELECT BU, CCDescription, // CConsumption, CentralOrBlk, CHN , // City, CreatedOn, CTDescription , // Customer, District, FullNameN , // KAGROUP, KANAME, KeyAcc , // OrBlk, PaymentTerms, PriceList , // ReconciliationAcct, Region, SalesDistrict , // SalesOrg, Street, Telephone1 , // UPDDAT, VATregistrationNo FROM [Sheet1$] // // Vendor Telephone2 FaxNumber bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tbl_KaCustomer"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("Customer", "Customer"); bulkCopy.ColumnMappings.Add("SalesOrg", "Region"); bulkCopy.ColumnMappings.Add("FullName", "FullNameN"); bulkCopy.ColumnMappings.Add("TradingName", "KANAME"); bulkCopy.ColumnMappings.Add("Street", "Street"); bulkCopy.ColumnMappings.Add("District", "District"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("Telephone1", "Telephone1"); bulkCopy.ColumnMappings.Add("VATregistrationNo", "VATregistrationNo"); // bulkCopy.ColumnMappings.Add("CustomerGroup", "CustomerGroup"); bulkCopy.ColumnMappings.Add("Indirect", "indirectCode"); bulkCopy.ColumnMappings.Add("SALORG_CTR", "SALORG_CTR"); //bulkCopy.ColumnMappings.Add("BU", "BU"); //bulkCopy.ColumnMappings.Add("CCDescription", "CCDescription"); //bulkCopy.ColumnMappings.Add("CConsumption", "CConsumption"); //bulkCopy.ColumnMappings.Add("CentralOrBlk", "CentralOrBlk"); //bulkCopy.ColumnMappings.Add("CHN", "CHN"); //bulkCopy.ColumnMappings.Add("CreatedOn", "CreatedOn"); //bulkCopy.ColumnMappings.Add("CTDescription", "CTDescription"); //bulkCopy.ColumnMappings.Add("KAGROUP", "KAGROUP"); //bulkCopy.ColumnMappings.Add("KeyAcc", "KeyAcc"); //bulkCopy.ColumnMappings.Add("OrBlk", "OrBlk"); //bulkCopy.ColumnMappings.Add("PaymentTerms", "PaymentTerms"); //bulkCopy.ColumnMappings.Add("PriceList", "PriceList"); //bulkCopy.ColumnMappings.Add("ReconciliationAcct", "ReconciliationAcct"); //bulkCopy.ColumnMappings.Add("SalesDistrict", "SalesDistrict"); //bulkCopy.ColumnMappings.Add("UPDDAT", "UPDDAT"); try { bulkCopy.WriteToServer(sourceData); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } // Thread.CurrentThread.Abort(); }
private void importsexceltblNKACustomer(object obj) { // List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>(); // fbl5n_ctrl md = new fbl5n_ctrl(); // bool kq = md.deleteFbl5n(); datainportF inf = (datainportF)obj; string filename = inf.filename; // ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("No", typeof(double)); batable.Columns.Add("Customer", typeof(double)); batable.Columns.Add("SOrg", typeof(string)); batable.Columns.Add("Area", typeof(string)); batable.Columns.Add("Region", typeof(string)); batable.Columns.Add("Group", typeof(string)); batable.Columns.Add("GroupLetter", typeof(string)); batable.Columns.Add("Branch", typeof(string)); batable.Columns.Add("IncoT", typeof(string)); batable.Columns.Add("KeyAcc", typeof(string)); batable.Columns.Add("Pt", typeof(string)); batable.Columns.Add("Plant", typeof(string)); batable.Columns.Add("ASM", typeof(string)); batable.Columns.Add("Name", typeof(string)); batable.Columns.Add("Nameinletter", typeof(string)); batable.Columns.Add("NameAccountofCustomer", typeof(string)); batable.Columns.Add("Mail", typeof(string)); batable.Columns.Add("Phone", typeof(string)); batable.Columns.Add("Address", typeof(string)); batable.Columns.Add("Account", typeof(string)); int Noid = -1; int Customerid = -1; int SOrgid = -1; int Areaid = -1; int Regionid = -1; int Groupid = -1; int GroupLetterid = -1; int Branchid = -1; int IncoTid = -1; int KeyAccid = -1; int Ptid = -1; int Plantid = -1; int ASMid = -1; int Nameid = -1; int Nameinletterid = -1; int NameAccountofCustomerid = -1; int Mailid = -1; int Phoneid = -1; int Addressid = -1; int Accountid = -1; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region // // // string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null && value != "") { // #region setcolum if (value.Trim() == ("No")) { Noid = columid; // headindex = rowid; } if (value.Trim() == ("Customer")) { Customerid = columid; // headindex = 0; } if (value.Trim() == ("SOrg.")) { SOrgid = columid; // headindex = 0; } if (value.Trim() == ("Area")) { Areaid = columid;// } if (value.Trim() == ("Region")) { Regionid = columid;// } if (value.Trim() == ("Group")) { Groupid = columid;// } if (value.Trim() == ("Group Letter")) { GroupLetterid = columid;// } if (value.Trim() == ("Branch")) { Branchid = columid;// } if (value.Trim() == ("IncoT")) { IncoTid = columid;// } if (value.Trim() == ("KeyAcc")) { KeyAccid = columid;// } if (value.Trim() == ("Pt")) { Ptid = columid;// } if (value.Trim() == ("Plant")) { Plantid = columid;// } if (value.Trim() == ("ASM")) { ASMid = columid;// } if (value.Trim() == ("Name")) { Nameid = columid;// } if (value.Trim() == ("Name in letter")) { Nameinletterid = columid;// } if (value.Trim() == ("Name's Account of Customer")) { NameAccountofCustomerid = columid;// } if (value.Trim() == ("Mail")) { Mailid = columid;// } if (value.Trim() == ("Phone")) { Phoneid = columid;// } if (value.Trim() == ("Address")) { Addressid = columid;// } if (value.Trim() == ("Account")) { Accountid = columid;// } } #endregion } // colum } // roww off heatder if (Noid == -1) { MessageBox.Show("Dữ liệu thiếu cột No", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Customerid == -1) { MessageBox.Show("Dữ liệu thiếu cột Customer", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SOrgid == -1) { MessageBox.Show("Dữ liệu thiếu cột SOrg.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Areaid == -1) { MessageBox.Show("Dữ liệu thiếu cột Area", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Regionid == -1) { MessageBox.Show("Dữ liệu thiếu cột Region", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Groupid == -1) { MessageBox.Show("Dữ liệu thiếu cột Group", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (GroupLetterid == -1) { MessageBox.Show("Dữ liệu thiếu cột Group Letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Branchid == -1) { MessageBox.Show("Dữ liệu thiếu cột Branch", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (IncoTid == -1) { MessageBox.Show("Dữ liệu thiếu cột IncoT", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (KeyAccid == -1) { MessageBox.Show("Dữ liệu thiếu cột KeyAcc", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Ptid == -1) { MessageBox.Show("Dữ liệu thiếu cột Pt", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Plantid == -1) { MessageBox.Show("Dữ liệu thiếu cột Plant", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (ASMid == -1) { MessageBox.Show("Dữ liệu thiếu cột ASM", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Nameid == -1) { MessageBox.Show("Dữ liệu thiếu cột Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Nameinletterid == -1) { MessageBox.Show("Dữ liệu thiếu cột Name in letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (NameAccountofCustomerid == -1) { MessageBox.Show("Dữ liệu thiếu cột Name 's Account of Customer", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Mailid == -1) { MessageBox.Show("Dữ liệu thiếu cột Mail", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Phoneid == -1) { MessageBox.Show("Dữ liệu thiếu cột Phone", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Addressid == -1) { MessageBox.Show("Dữ liệu thiếu cột Address", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Accountid == -1) { MessageBox.Show("Dữ liệu thiếu cột Account", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string Customer = sourceData.Rows[rowixd][Customerid].ToString(); if (Customer != "" && Utils.IsValidnumber(Customer)) { if (double.Parse(Customer) > 0) { DataRow dr = batable.NewRow(); try { dr["No"] = double.Parse(sourceData.Rows[rowixd][Noid].ToString());//.Trim } catch (Exception) { MessageBox.Show("Dữ liệu thiếu cột No dòng: " + rowixd.ToString() + "cột: " + Noid.ToString() + " bị lỗi", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } dr["Customer"] = double.Parse(sourceData.Rows[rowixd][Customerid].ToString());//.Trim(); dr["SOrg"] = sourceData.Rows[rowixd][SOrgid].ToString().Trim(); dr["Area"] = sourceData.Rows[rowixd][Areaid].ToString().Trim(); dr["Region"] = sourceData.Rows[rowixd][Regionid].ToString().Trim(); dr["Group"] = sourceData.Rows[rowixd][Groupid].ToString().Trim(); dr["GroupLetter"] = sourceData.Rows[rowixd][GroupLetterid].ToString().Trim(); dr["Branch"] = sourceData.Rows[rowixd][Branchid].ToString().Trim(); dr["IncoT"] = sourceData.Rows[rowixd][IncoTid].ToString().Trim(); dr["KeyAcc"] = sourceData.Rows[rowixd][KeyAccid].ToString().Trim(); dr["Pt"] = sourceData.Rows[rowixd][Ptid].ToString().Trim(); dr["Plant"] = sourceData.Rows[rowixd][Plantid].ToString().Trim(); dr["ASM"] = sourceData.Rows[rowixd][ASMid].ToString().Trim(); dr["Name"] = sourceData.Rows[rowixd][Nameid].ToString().Trim(); dr["Nameinletter"] = sourceData.Rows[rowixd][Nameinletterid].ToString().Trim(); dr["NameAccountofCustomer"] = sourceData.Rows[rowixd][NameAccountofCustomerid].ToString().Trim(); dr["Mail"] = sourceData.Rows[rowixd][Mailid].ToString().Trim(); dr["Phone"] = sourceData.Rows[rowixd][Phoneid].ToString().Trim(); dr["Address"] = sourceData.Rows[rowixd][Addressid].ToString().Trim(); dr["Account"] = sourceData.Rows[rowixd][Accountid].ToString().Trim(); batable.Rows.Add(dr); } } #endregion } // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.DestinationTableName = "tblNKACustomer"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("No", "[No]"); bulkCopy.ColumnMappings.Add("Customer", "[Customer]"); bulkCopy.ColumnMappings.Add("SOrg", "[SOrg]"); bulkCopy.ColumnMappings.Add("Area", "[Area]"); bulkCopy.ColumnMappings.Add("Region", "[Region]"); bulkCopy.ColumnMappings.Add("Group", "[Group]"); bulkCopy.ColumnMappings.Add("GroupLetter", "[Group Letter]"); bulkCopy.ColumnMappings.Add("Branch", "[Branch]"); bulkCopy.ColumnMappings.Add("IncoT", "[IncoT]"); bulkCopy.ColumnMappings.Add("KeyAcc", "[KeyAcc]"); bulkCopy.ColumnMappings.Add("Pt", "[Pt]"); bulkCopy.ColumnMappings.Add("Plant", "[Plant]"); bulkCopy.ColumnMappings.Add("ASM", "[ASM]"); bulkCopy.ColumnMappings.Add("Name", "[Name]"); bulkCopy.ColumnMappings.Add("Nameinletter", "[Name in letter]"); bulkCopy.ColumnMappings.Add("NameAccountofCustomer", "[Name's Account of Customer]"); bulkCopy.ColumnMappings.Add("Mail", "[Mail]"); bulkCopy.ColumnMappings.Add("Phone", "[Phone]"); bulkCopy.ColumnMappings.Add("Address", "[Address]"); bulkCopy.ColumnMappings.Add("Account", "[Account]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexcel(object obj) { // List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>(); fbl5n_ctrl md = new fbl5n_ctrl(); bool kq = md.deleteFbl5n(); datainportF inf = (datainportF)obj; string filename = inf.filename; // ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("Account", typeof(double)); batable.Columns.Add("Assignment", typeof(string)); batable.Columns.Add("PostingDate", typeof(DateTime)); // PF Inv Date batable.Columns.Add("PFInvDate", typeof(DateTime)); // PF Inv Number batable.Columns.Add("PFInvNumber", typeof(string)); // Invoice Registration No. batable.Columns.Add("InvoiceRegistration", typeof(string)); batable.Columns.Add("DocumentType", typeof(string)); batable.Columns.Add("BusinessArea", typeof(string)); batable.Columns.Add("DocumentNumber", typeof(double)); batable.Columns.Add("Amountinlocalcurrency", typeof(double)); batable.Columns.Add("Deposit", typeof(double)); int InvoiceRegistrationid = -1; int PFInvDateid = -1; int PFInvNumberid = -1; int Depositid = -1; int Accountid = -1; int Assignmentid = -1; int PostingDateid = -1; int DocumentTypeid = -1; int DocumentNumberid = -1; int BusinessAreaid = -1; int Amountinlocalcurrencyid = -1; int rowseet = sourceData.Rows.Count; if (rowseet > 20) { rowseet = 20; } for (int rowid = 0; rowid < rowseet; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region string value = ""; try { value = sourceData.Rows[rowid][columid].ToString(); } catch (Exception) { value = ""; // throw; } // MessageBox.Show(value +":"+ rowid); if (value != null && value != "") { // #region setcolum if (value.Trim() == ("Account")) //Account { Accountid = columid; // headindex = rowid; } if (value.Trim() == ("Assignment")) { Assignmentid = columid; // headindex = 0; } if (value.Trim() == ("Posting Date")) { PostingDateid = columid; // headindex = 0; } if (value.Trim() == ("Deposit")) { Depositid = columid; // headindex = 0; } if (value.Trim() == ("Document Type")) { DocumentTypeid = columid; } if (value.Trim() == ("Document Number")) { DocumentNumberid = columid; } if (value.Trim() == ("Amount in local currency")) { Amountinlocalcurrencyid = columid; } if (value.Trim() == ("Business Area")) { BusinessAreaid = columid; } //// PF Inv Date // batable.Columns.Add("PFInvDate", typeof(DateTime)); if (value.Trim() == ("PF Inv Date")) { PFInvDateid = columid; } if (value.Trim() == ("PF Inv Number")) { PFInvNumberid = columid; } if (value.Trim() == ("Invoice Registration No.")) { InvoiceRegistrationid = columid; } } #endregion } // colum } // roww off heatder if (Accountid == -1) { MessageBox.Show("Dữ liệu thiếu cột Account", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (InvoiceRegistrationid == -1) { MessageBox.Show("Dữ liệu thiếu cột Invoice Registration No.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Depositid == -1) { MessageBox.Show("Dữ liệu thiếu cột Deposit", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Assignmentid == -1) { MessageBox.Show("Dữ liệu thiếu cột Assignment", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (DocumentNumberid == -1) { MessageBox.Show("Dữ liệu thiếu cột Document Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (DocumentTypeid == -1) { MessageBox.Show("Dữ liệu thiếu cột Document Type", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (BusinessAreaid == -1) { MessageBox.Show("Dữ liệu thiếu cột Business Area", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Amountinlocalcurrencyid == -1) { MessageBox.Show("Dữ liệu thiếu cột Amount in local currencyid", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PostingDateid == -1) { MessageBox.Show("Dữ liệu thiếu cột Posting Date", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PFInvDateid == -1) { MessageBox.Show("Dữ liệu thiếu cột PF Inv Date", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PFInvNumberid == -1) { MessageBox.Show("Dữ liệu thiếu cột PF Inv Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string Account = sourceData.Rows[rowixd][Accountid].ToString(); if (Account != "" && Utils.IsValidnumber(Account) && sourceData.Rows[rowixd][DocumentTypeid].ToString().Trim() != "") { if (double.Parse(Account) > 0) { DataRow dr = batable.NewRow(); dr["Account"] = double.Parse(sourceData.Rows[rowixd][Accountid].ToString());//.Trim(); dr["Assignment"] = sourceData.Rows[rowixd][Assignmentid].ToString().Truncate(225).Trim(); dr["PFInvNumber"] = sourceData.Rows[rowixd][PFInvNumberid].ToString().Truncate(50).Trim(); dr["InvoiceRegistration"] = sourceData.Rows[rowixd][InvoiceRegistrationid].ToString().Truncate(50).Trim(); try { dr["PostingDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][PostingDateid].ToString()); } catch (Exception) { dr["PostingDate"] = ""; } try { dr["PFInvDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][PFInvDateid].ToString()); } catch (Exception) { dr["PFInvDate"] = ""; } dr["DocumentType"] = sourceData.Rows[rowixd][DocumentTypeid].ToString().Truncate(225).Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][DocumentNumberid].ToString())) { dr["DocumentNumber"] = double.Parse(sourceData.Rows[rowixd][DocumentNumberid].ToString());//.Trim(); } else { dr["DocumentNumber"] = 0; } if (Utils.IsValidnumber(sourceData.Rows[rowixd][Amountinlocalcurrencyid].ToString())) { dr["Amountinlocalcurrency"] = double.Parse(sourceData.Rows[rowixd][Amountinlocalcurrencyid].ToString());//.Trim(); } else { dr["Amountinlocalcurrency"] = 0; } dr["BusinessArea"] = sourceData.Rows[rowixd][BusinessAreaid].ToString().Truncate(225).Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][Depositid].ToString().Trim())) { dr["Deposit"] = double.Parse(sourceData.Rows[rowixd][Depositid].ToString().Trim()); } else { dr["Deposit"] = 0; } batable.Rows.Add(dr); } } #endregion } // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tblFBL5N"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("[Account]", "[Account]"); bulkCopy.ColumnMappings.Add("[Assignment]", "[Assignment]"); bulkCopy.ColumnMappings.Add("[PostingDate]", "[Posting Date]"); bulkCopy.ColumnMappings.Add("[DocumentType]", "[Document Type]"); bulkCopy.ColumnMappings.Add("[DocumentNumber]", "[Document Number]"); bulkCopy.ColumnMappings.Add("[BusinessArea]", "[Business Area]"); bulkCopy.ColumnMappings.Add("[Amountinlocalcurrency]", "[Amount in local currency]"); bulkCopy.ColumnMappings.Add("[Deposit]", "[Deposit]"); bulkCopy.ColumnMappings.Add("[PFInvDate]", "[PFInvDate]"); bulkCopy.ColumnMappings.Add("[PFInvNumber]", "[PFInvNumber]"); bulkCopy.ColumnMappings.Add("[InvoiceRegistration]", "[InvoiceRegistration]"); // Invoice Registration No. // batable.Columns.Add("InvoiceRegistration", typeof(string)); //// PF Inv Number //batable.Columns.Add("PFInvNumber", typeof(string)); //PFInvNumberid = columid; try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
public void importShiptolist(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); datainportF inf = (datainportF)obj; string filename = inf.filename; // string filename = theDialog.FileName.ToString(); // string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); dc.ExecuteCommand("DELETE FROM tbl_MKT_Soldtocode"); // dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod); dc.CommandTimeout = 0; dc.SubmitChanges(); // Customer Sales Organization Name 1 House num &Street Street 4 City Telephone 1 Sales Office Delivering Plant Terms of Payment Price List Key Account No Sales district Created on Created by VAT Registration No.Central order block Order block for sales area // // SoldtoCode Shiptocode SalesOrg FullNameN Street District City Telephone1 ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("SoldtoCode", typeof(string)); batable.Columns.Add("Shiptocode", typeof(string)); batable.Columns.Add("Soldtype", typeof(Boolean)); batable.Columns.Add("FullNameN", typeof(string)); batable.Columns.Add("Telephone1", typeof(string)); //batable.Columns.Add("Note", typeof(string)); batable.Columns.Add("District", typeof(string)); batable.Columns.Add("SalesOrg", typeof(string)); batable.Columns.Add("Street", typeof(string)); batable.Columns.Add("City", typeof(string)); // batable.Columns.Add("Region", typeof(string)); // batable.Columns.Add("PaymentTerms", typeof(string)); // batable.Columns.Add("PriceList", typeof(string)); // batable.Columns.Add("KeyAcc", typeof(float)); // batable.Columns.Add("SalesDistrict", typeof(string)); batable.Columns.Add("Createdon", typeof(DateTime)); batable.Columns.Add("Createby", typeof(string)); // batable.Columns.Add("VATregistrationNo", typeof(string)); //dr[""] = sourceData.Rows[rowixd][Customerid].ToString().Trim(); //dr["[]"] = true; int SoldtoCodeid = 0; int FullNameNid = 0; int Telephone1id = 0; int Shiptocodeid = 0; int Districtid = 0; int SalesOrgid = 0; int Streetid = 0; int Cityid = 0; // int Regionid = 0; // int PaymentTermsid = 0; // int PriceListid = 0; // int KeyAccid = 0; // int SalesDistrictid = 0; // int VATregistrationNoid = 0; // View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test"); // vi1.ShowDialog(); int headindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim().Contains("SoldtoCode")) { SoldtoCodeid = columid; headindex = rowid; } if (value.Trim().Contains("FullNameN") && headindex == rowid) { FullNameNid = columid; // headindex = 0; } if (value.Trim().Contains("Telephone1") && headindex == rowid) { Telephone1id = columid; // headindex = 0; } if (value.Trim().Contains("Shiptocode") && headindex == rowid) { Shiptocodeid = columid; } if (value.Trim().Contains("District") && headindex == rowid) { //if (columid != SalesDistrictid) //{ Districtid = columid; //} } if (value.Trim().Contains("SalesOrg") && headindex == rowid) { SalesOrgid = columid; } if (value.Trim().Contains("Street") && headindex == rowid) { Streetid = columid; // headindex = 0; } if (value.Trim().Contains("City") && headindex == rowid) { Cityid = columid; } //if (value.Trim().Contains("Region") && headindex == rowid) //{ // Regionid = columid; //} //if (value.Trim().Contains("PaymentTerms") && headindex == rowid) //{ // PaymentTermsid = columid; //} //if (value.Trim().Contains("PriceList") && headindex == rowid) //{ // PriceListid = columid; //} //if (value.Trim().Contains("KeyAcc") && headindex == rowid) //{ // KeyAccid = columid; //} //if (value.Trim().Contains("VATregistrationNo") && headindex == rowid) //{ // VATregistrationNoid = columid; //} #endregion } } // colum } // roww off heatder if (headindex == -2) { MessageBox.Show("File excel không đúng format !", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string customer = sourceData.Rows[rowixd][SoldtoCodeid].ToString(); if (customer != "" && Utils.IsValidnumber(customer)) { DataRow dr = batable.NewRow(); dr["SoldtoCode"] = sourceData.Rows[rowixd][SoldtoCodeid].ToString().Trim(); dr["Shiptocode"] = sourceData.Rows[rowixd][Shiptocodeid].ToString().Trim(); if (sourceData.Rows[rowixd][SoldtoCodeid].ToString().Trim() == sourceData.Rows[rowixd][Shiptocodeid].ToString().Trim()) { dr["Soldtype"] = true; } else { dr["Soldtype"] = false; } dr["FullNameN"] = sourceData.Rows[rowixd][FullNameNid].ToString().Truncate(225); dr["Telephone1"] = sourceData.Rows[rowixd][Telephone1id].ToString().Truncate(50); dr["District"] = sourceData.Rows[rowixd][Districtid].ToString().Truncate(50); dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrgid].ToString().Truncate(50); dr["Street"] = sourceData.Rows[rowixd][Streetid].ToString().Truncate(225); dr["City"] = sourceData.Rows[rowixd][Cityid].ToString().Truncate(50); //dr["Region"] = sourceData.Rows[rowixd][Regionid].ToString().Trim(); //dr["PaymentTerms"] = sourceData.Rows[rowixd][PaymentTermsid].ToString().Trim(); //dr["PriceList"] = sourceData.Rows[rowixd][PriceListid].ToString().Trim(); //dr["KeyAcc"] = sourceData.Rows[rowixd][KeyAccid].ToString().Trim(); //dr["SalesDistrict"] = sourceData.Rows[rowixd][SalesDistrictid].ToString().Trim(); //dr["VATregistrationNo"] = sourceData.Rows[rowixd][VATregistrationNoid].ToString().Trim(); dr["Createdon"] = DateTime.Today;// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); dr["Createby"] = Model.Username.getUsername(); batable.Rows.Add(dr); } #endregion }// row //conpy to server string destConnString = Utils.getConnectionstr(); //adapter.FillSchema(sourceData, SchemaType.Source); //sourceData.Columns["Posting Date"].DataType = typeof(DateTime); //batable.Columns.Add("", typeof(string)); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.DestinationTableName = "tbl_MKT_Soldtocode"; // Write from the source to the destination. bulkCopy.BulkCopyTimeout = 0; bulkCopy.ColumnMappings.Add("SoldtoCode", "Customer"); bulkCopy.ColumnMappings.Add("Shiptocode", "ShiptoCode"); bulkCopy.ColumnMappings.Add("Soldtype", "Soldtype"); bulkCopy.ColumnMappings.Add("FullNameN", "FullNameN"); bulkCopy.ColumnMappings.Add("Telephone1", "Telephone1"); bulkCopy.ColumnMappings.Add("District", "District"); bulkCopy.ColumnMappings.Add("SalesOrg", "SalesOrg"); bulkCopy.ColumnMappings.Add("Street", "Street"); bulkCopy.ColumnMappings.Add("City", "City"); //bulkCopy.ColumnMappings.Add("Region", "Region"); //bulkCopy.ColumnMappings.Add("PaymentTerms", "PaymentTerms"); //bulkCopy.ColumnMappings.Add("PriceList", "PriceList"); //bulkCopy.ColumnMappings.Add("KeyAcc", "KeyAcc"); //bulkCopy.ColumnMappings.Add("SalesDistrict", "SalesDistrict"); bulkCopy.ColumnMappings.Add("Createdon", "Createdon"); bulkCopy.ColumnMappings.Add("Createby", "Createby"); // bulkCopy.ColumnMappings.Add("VATregistrationNo", "VATregistrationNo"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } //copy to server // string connection_string = Utils.getConnectionstr(); // LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); // var typeffmain = typeof(tbl_KAbaseprice); // var typeffsub = typeof(tbl_KAbaseprice); // VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", ""); // inputcdata1.ShowDialog(); // View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA"); // TB.ShowDialog(); // } }
private void importRemarksexcel(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); // List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>(); Remarks Rm = new Remarks(); bool kq = Rm.deleteallremarks(); datainportF inf = (datainportF)obj; string filename = inf.filename; #region new by datatable // ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("DocumentNo", typeof(double)); batable.Columns.Add("Customer", typeof(double)); batable.Columns.Add("Remark", typeof(string)); int DocumentNoid = -1; int Customerid = -1; int Remarkid = -1; int rowseet = sourceData.Rows.Count; if (rowseet > 5) { rowseet = 5; } for (int rowid = 0; rowid < rowseet; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region string value = sourceData.Rows[rowid][columid].ToString(); if (value != null && value != "") { // #region setcolum if (value.Trim() == ("DocumentNo") && DocumentNoid == -1) { DocumentNoid = columid; // headindex = rowid; } if (value.Trim() == ("Customer") && Customerid == -1) { Customerid = columid; // headindex = 0; } if (value.Trim() == ("Remark") && Remarkid == -1) { Remarkid = columid; // headindex = 0; } } #endregion } // colum } // roww off heatder if (DocumentNoid == -1) { MessageBox.Show("Dữ liệu thiếu cột DocumentNoid", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Customerid == -1) { MessageBox.Show("Dữ liệu thiếu cột Customer", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Remarkid == -1) { MessageBox.Show("Dữ liệu thiếu cột Remark", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string DocumentNoVal = sourceData.Rows[rowixd][DocumentNoid].ToString(); if (DocumentNoVal != "" && Utils.IsValidnumber(DocumentNoVal)) { if (double.Parse(DocumentNoVal) > 0) { DataRow dr = batable.NewRow(); try { dr["Remark"] = sourceData.Rows[rowixd][Remarkid].ToString().Truncate(225).Trim(); dr["DocumentNo"] = double.Parse(sourceData.Rows[rowixd][DocumentNoid].ToString()); dr["Customer"] = double.Parse(sourceData.Rows[rowixd][Customerid].ToString()); } catch (Exception) { MessageBox.Show("Data có đủ cột nhưng dữ liệu ở dưới bị lost, please check", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); } batable.Rows.Add(dr); } } #endregion } #endregion // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tbl_Remark"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("[DocumentNo]", "[DocumentNo]"); bulkCopy.ColumnMappings.Add("[Customer]", "[Customer]"); bulkCopy.ColumnMappings.Add("[Remark]", "[Remark]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
public void Inputapprovalpaymentcode(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); datainportF inf = (datainportF)obj; string filename = inf.filename; string username = inf.username; LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); dc.ExecuteCommand("DELETE FROM tbl_MKT_Payment_AprovalTMP where tbl_MKT_Payment_AprovalTMP.username = '******'"); // dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod); dc.CommandTimeout = 0; dc.SubmitChanges(); ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("Customercode", typeof(string)); batable.Columns.Add("CustomerName", typeof(string)); batable.Columns.Add("CustomerAddress", typeof(string)); batable.Columns.Add("AprovalBudget", typeof(float)); batable.Columns.Add("username", typeof(string)); int Customercodeid = 0; int CustomerNameid = 0; int CustomerAddressid = 0; int AprovalBudgetid = 0; int rowheadindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim().Contains("Customercode")) { Customercodeid = columid; rowheadindex = rowid; // headindex = 0; } if (value.Trim().Contains("CustomerName")) { CustomerNameid = columid; // headindex = 0; } if (value.Trim().Contains("CustomerAddress")) { CustomerAddressid = columid; } if (value.Trim().Contains("AprovalBudget")) { AprovalBudgetid = columid; } #endregion } } // colum } // roww off heatder for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string Customercode = sourceData.Rows[rowixd][Customercodeid].ToString(); if (Customercode != "" && rowheadindex != rowixd) { DataRow dr = batable.NewRow(); dr["Customercode"] = sourceData.Rows[rowixd][Customercodeid].ToString().Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][AprovalBudgetid].ToString())) { dr["AprovalBudget"] = double.Parse(sourceData.Rows[rowixd][AprovalBudgetid].ToString()); } else { dr["AprovalBudget"] = 0; } dr["CustomerName"] = sourceData.Rows[rowixd][CustomerNameid].ToString().Trim(); dr["CustomerAddress"] = sourceData.Rows[rowixd][CustomerAddressid].ToString().Trim(); //dr["Description"] = sourceData.Rows[rowixd][Descriptionid].ToString().Trim(); //dr["UNIT"] = sourceData.Rows[rowixd][UNITid].ToString().Trim(); dr["username"] = username; batable.Rows.Add(dr); } #endregion }// row //conpy to server string destConnString = Utils.getConnectionstr(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { //batable.Columns.Add("SAPCODE", typeof(string)); //batable.Columns.Add("ITEMCode", typeof(string)); //batable.Columns.Add("MATERIAL", typeof(string)); //batable.Columns.Add("Description", typeof(string)); //batable.Columns.Add("UNIT", typeof(string)); //batable.Columns.Add("ENDSTOCK", typeof(float)); //batable.Columns.Add("Store_code", typeof(string)); bulkCopy.DestinationTableName = "tbl_MKT_Payment_AprovalTMP"; // Write from the source to the destination. bulkCopy.BulkCopyTimeout = 0; bulkCopy.ColumnMappings.Add("Customercode", "[Customercode]"); bulkCopy.ColumnMappings.Add("AprovalBudget", "[AprovalBudget]"); bulkCopy.ColumnMappings.Add("CustomerName", "[CustomerName]"); bulkCopy.ColumnMappings.Add("CustomerAddress", "[CustomerAddress]"); bulkCopy.ColumnMappings.Add("username", "[username]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
public void Addnewproduct(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); datainportF inf = (datainportF)obj; string filename = inf.filename; string storelocation = inf.storelocation; LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); string Username = Utils.getusername(); dc.ExecuteCommand("DELETE FROM tbl_MKT_StockendTMP where tbl_MKT_StockendTMP.Username = '******'"); dc.CommandTimeout = 0; dc.SubmitChanges(); ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); // batable.Columns.Add("Delivery_No", typeof(float)); batable.Columns.Add("SAPCODE", typeof(string)); batable.Columns.Add("ITEMCode", typeof(string)); batable.Columns.Add("MATERIAL", typeof(string)); batable.Columns.Add("Description", typeof(string)); batable.Columns.Add("UNIT", typeof(string)); batable.Columns.Add("ENDSTOCK", typeof(float)); batable.Columns.Add("Username", typeof(string)); batable.Columns.Add("Store_code", typeof(string)); int SAPCODEid = 0; int ITEMCodeid = 0; int MATERIALid = 0; int Descriptionid = 0; int UNITid = 0; int ENDSTOCKid = 0; int rowheadindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim().Contains("ITEMCode")) { ITEMCodeid = columid; rowheadindex = rowid; // headindex = 0; } if (value.Trim().Contains("SAPCODE")) { SAPCODEid = columid; // headindex = 0; } if (value.Trim().Contains("MATERIAL")) { MATERIALid = columid; } if (value.Trim().Contains("Description")) { Descriptionid = columid; } if (value.Trim().Contains("UNIT")) { UNITid = columid; } if (value.Trim().Contains("ENDSTOCK")) { ENDSTOCKid = columid; // headindex = 0; } #endregion } } // colum } // roww off heatder for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string Itemcode = sourceData.Rows[rowixd][ITEMCodeid].ToString(); if (Itemcode != "" && rowheadindex != rowixd) { DataRow dr = batable.NewRow(); dr["SAPCODE"] = sourceData.Rows[rowixd][SAPCODEid].ToString().Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][ENDSTOCKid].ToString())) { dr["ENDSTOCK"] = double.Parse(sourceData.Rows[rowixd][ENDSTOCKid].ToString()); } else { dr["ENDSTOCK"] = 0; } dr["ITEMCode"] = sourceData.Rows[rowixd][ITEMCodeid].ToString().Trim(); dr["MATERIAL"] = sourceData.Rows[rowixd][MATERIALid].ToString().Trim(); dr["Description"] = sourceData.Rows[rowixd][Descriptionid].ToString().Trim(); dr["UNIT"] = sourceData.Rows[rowixd][UNITid].ToString().Trim(); dr["Store_code"] = storelocation; dr["Username"] = Username; batable.Rows.Add(dr); } #endregion }// row //conpy to server string destConnString = Utils.getConnectionstr(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { //batable.Columns.Add("SAPCODE", typeof(string)); //batable.Columns.Add("ITEMCode", typeof(string)); //batable.Columns.Add("MATERIAL", typeof(string)); //batable.Columns.Add("Description", typeof(string)); //batable.Columns.Add("UNIT", typeof(string)); //batable.Columns.Add("ENDSTOCK", typeof(float)); //batable.Columns.Add("Store_code", typeof(string)); bulkCopy.DestinationTableName = "tbl_MKT_StockendTMP"; // Write from the source to the destination. bulkCopy.BulkCopyTimeout = 0; // bulkCopy.ColumnMappings.Add("Delivery_No", "Delivery_No"); bulkCopy.ColumnMappings.Add("SAPCODE", "[SAP_CODE]"); bulkCopy.ColumnMappings.Add("ITEMCode", "[ITEM_Code]"); bulkCopy.ColumnMappings.Add("MATERIAL", "[MATERIAL]"); bulkCopy.ColumnMappings.Add("Description", "[Description]"); bulkCopy.ColumnMappings.Add("ENDSTOCK", "[END_STOCK]"); bulkCopy.ColumnMappings.Add("UNIT", "[UNIT]"); bulkCopy.ColumnMappings.Add("Store_code", "[Store_code]"); bulkCopy.ColumnMappings.Add("Username", "Username"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
public void InputBeginstoredetailwithlocation(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); datainportF inf = (datainportF)obj; string filename = inf.filename; string storelocation = inf.storelocation; LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); dc.ExecuteCommand("DELETE FROM tbl_MKT_Stockendlocationdetail where tbl_MKT_Stockendlocationdetail.Store_code = '" + storelocation + "'" + "and tbl_MKT_Stockendlocationdetail.[Doctype] = 'Begin'"); // dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod); dc.CommandTimeout = 0; dc.SubmitChanges(); ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); // batable.Columns.Add("Delivery_No", typeof(float)); batable.Columns.Add("location", typeof(string)); batable.Columns.Add("SAPCODE", typeof(string)); batable.Columns.Add("ITEMCode", typeof(string)); batable.Columns.Add("MATERIAL", typeof(string)); batable.Columns.Add("Description", typeof(string)); batable.Columns.Add("UNIT", typeof(string)); batable.Columns.Add("ENDSTOCK", typeof(float)); batable.Columns.Add("Store_code", typeof(string)); batable.Columns.Add("Doctype", typeof(string)); batable.Columns.Add("Createdate", typeof(DateTime)); // [location] //,[SAP_CODE] //,[ITEM_Code] //,[MATERIAL] //,[Description] //,[UNIT] //,[END_STOCK] //,[id] //,[Store_code] //,[Doctype] //,[DocNumber] int Store_codeid = 0; int locationid = 0; int SAPCODEid = 0; int ITEMCodeid = 0; int MATERIALid = 0; int Descriptionid = 0; int UNITid = 0; int ENDSTOCKid = 0; int rowheadindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim().Contains("Storage Location")) { locationid = columid; rowheadindex = rowid; // headindex = 0; } if (value.Trim().Contains("Plant")) { Store_codeid = columid; } if (value.Trim().Contains("Material") && !value.Trim().Contains("Material Description") && !value.Trim().Contains("Description in Vietnamese")) { SAPCODEid = columid; ITEMCodeid = columid; // rowheadindex = rowid; // headindex = 0; } //if (value.Trim().Contains("Material")) //{ // SAPCODEid = columid; // // headindex = 0; //} if (value.Trim().Contains("Material Description")) { MATERIALid = columid; } if (value.Trim().Contains("Description in Vietnamese")) { Descriptionid = columid; } if (value.Trim().Contains("Base Unit of Measure")) { UNITid = columid; } if (value.Trim() == ("Unrestricted")) { ENDSTOCKid = columid; // headindex = 0; } #endregion } } // colum } // roww off heatder for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string Itemcode = sourceData.Rows[rowixd][ITEMCodeid].ToString(); if (Itemcode != "" && rowheadindex != rowixd && sourceData.Rows[rowixd][Store_codeid].ToString().Trim() == storelocation) // chỉ lấy giá trị của kho cần lấy { DataRow dr = batable.NewRow(); dr["location"] = sourceData.Rows[rowixd][locationid].ToString().Trim().Truncate(50); dr["SAPCODE"] = sourceData.Rows[rowixd][SAPCODEid].ToString().Trim(); if (Utils.IsValidnumber(sourceData.Rows[rowixd][ENDSTOCKid].ToString())) { dr["ENDSTOCK"] = double.Parse(sourceData.Rows[rowixd][ENDSTOCKid].ToString()); } else { dr["ENDSTOCK"] = 0; } dr["ITEMCode"] = sourceData.Rows[rowixd][ITEMCodeid].ToString().Trim(); dr["MATERIAL"] = sourceData.Rows[rowixd][MATERIALid].ToString().Trim(); dr["Description"] = sourceData.Rows[rowixd][Descriptionid].ToString().Trim(); dr["UNIT"] = sourceData.Rows[rowixd][UNITid].ToString().Trim(); dr["Store_code"] = storelocation; dr["Doctype"] = "Begin"; dr["Createdate"] = DateTime.Today; batable.Rows.Add(dr); } #endregion }// row //conpy to server string destConnString = Utils.getConnectionstr(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { //batable.Columns.Add("SAPCODE", typeof(string)); //batable.Columns.Add("ITEMCode", typeof(string)); //batable.Columns.Add("MATERIAL", typeof(string)); //batable.Columns.Add("Description", typeof(string)); //batable.Columns.Add("UNIT", typeof(string)); //batable.Columns.Add("ENDSTOCK", typeof(float)); //batable.Columns.Add("Store_code", typeof(string)); bulkCopy.DestinationTableName = "tbl_MKT_Stockendlocationdetail"; // Write from the source to the destination. bulkCopy.BulkCopyTimeout = 0; // bulkCopy.ColumnMappings.Add("Delivery_No", "Delivery_No"); bulkCopy.ColumnMappings.Add("location", "[location]"); bulkCopy.ColumnMappings.Add("SAPCODE", "[SAP_CODE]"); bulkCopy.ColumnMappings.Add("ITEMCode", "[ITEM_Code]"); bulkCopy.ColumnMappings.Add("MATERIAL", "[MATERIAL]"); bulkCopy.ColumnMappings.Add("Description", "[Description]"); bulkCopy.ColumnMappings.Add("ENDSTOCK", "[END_STOCK]"); bulkCopy.ColumnMappings.Add("UNIT", "[UNIT]"); bulkCopy.ColumnMappings.Add("Store_code", "[Store_code]"); bulkCopy.ColumnMappings.Add("Doctype", "[Doctype]"); bulkCopy.ColumnMappings.Add("Createdate", "[Createdate]"); // dr["Store_code"] = storelocation; // dr["Doctype"] = "Begin"; try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexcel(object obj) { // List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>(); vat_ctrl md = new vat_ctrl(); bool kq = md.deleteallvat(); datainportF inf = (datainportF)obj; string filename = inf.filename; ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); //bulkCopy.ColumnMappings.Add("[InvoiceRegistrationNumber]", "[Invoice Registration Number]"); //bulkCopy.ColumnMappings.Add("[InvoiceNumber]", "[Invoice Number]"); //bulkCopy.ColumnMappings.Add("[SAPDeliveryNumber]", "[SAP Delivery Number]"); //bulkCopy.ColumnMappings.Add("[SAPInvoiceNumber]", "[SAP Invoice Number]"); //bulkCopy.ColumnMappings.Add("[ProFormaDate]", "[Pro Forma Date]"); //bulkCopy.ColumnMappings.Add("[InvoiceAmountBeforeVAT]", "[Invoice Amount Before VAT]");//-sua am //bulkCopy.ColumnMappings.Add("[VATAmount]", "[VAT Amount]"); //bulkCopy.ColumnMappings.Add("[CustomerNumber]", "[Customer Number]"); //bulkCopy.ColumnMappings.Add("[CustomerName]", "[Customer Name]"); //bulkCopy.ColumnMappings.Add("[StreetAddress]", "[Street Address]"); batable.Columns.Add("InvoiceRegistrationNumber", typeof(string)); batable.Columns.Add("InvoiceNumber", typeof(double)); batable.Columns.Add("SAPDeliveryNumber", typeof(double)); batable.Columns.Add("SAPInvoiceNumber", typeof(double)); batable.Columns.Add("ProFormaDate", typeof(DateTime)); batable.Columns.Add("InvoiceAmountBeforeVAT", typeof(double)); batable.Columns.Add("VATAmount", typeof(double)); batable.Columns.Add("CustomerNumber", typeof(double)); batable.Columns.Add("CustomerName", typeof(string)); batable.Columns.Add("StreetAddress", typeof(string)); // int InvoiceRegistrationNumber = -1; int InvoiceNumber = -1; int SAPDeliveryNumber = -1; int SAPInvoiceNumber = -1; // int ProFormaDate = -1; int InvoiceAmountBeforeVAT = -1; int VATAmount = -1; int CustomerNumber = -1; int CustomerName = -1; int StreetAddress = -1; // int CustomerNumber = -1; int rowseet = sourceData.Rows.Count; if (rowseet > 5) { rowseet = 5; } for (int rowid = 0; rowid < rowseet; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); //OleDbCommand command = new OleDbCommand( // @"SELECT [Invoice Registration Number],[Invoice Number],[SAP Delivery Number],[SAP Invoice Number], // [Pro Forma Date],[Customer Number],[Customer Name],[Invoice Amount Before VAT],[ VAT Amount], // [Street Address] FROM [Sheet1$] // WHERE ([SAP Invoice Number] is not null ) ", conn); if (value != null && value != "") { // #region setcolum //if (value.Trim() == ("Invoice Registration Number")) //{ // InvoiceRegistrationNumber = columid; // // headindex = rowid; //} if (value.Trim() == ("Invoice Number")) { InvoiceNumber = columid; // headindex = 0; } if (value.Trim() == ("SAP Invoice Number")) { SAPInvoiceNumber = columid; // headindex = 0; } if (value.Trim() == ("SAP Delivery Number")) { SAPDeliveryNumber = columid; // headindex = 0; } if (value.Trim() == ("Customer Name")) { CustomerName = columid; } //if (value.Trim()==("Pro Forma Date")) //{ // ProFormaDate = columid; //} if (value.Trim() == ("Customer Number")) { CustomerNumber = columid; } if (value.Trim() == ("Invoice Amount Before VAT")) { InvoiceAmountBeforeVAT = columid; } if (value.Trim() == ("VAT Amount")) { VATAmount = columid; } if (value.Trim() == ("Street Address")) { StreetAddress = columid; } } #endregion } // colum } // roww off heatder //if (InvoiceRegistrationNumber == -1) //{ // MessageBox.Show("Dữ liệu thiếu cột Invoice Registration Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); // return; //} if (InvoiceNumber == -1) { MessageBox.Show("Dữ liệu thiếu cột Invoice Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SAPInvoiceNumber == -1) { MessageBox.Show("Dữ liệu thiếu cột SAP invoice Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SAPDeliveryNumber == -1) { MessageBox.Show("Dữ liệu thiếu cột SAP Delivery Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } //if (ProFormaDate == -1) //{ // MessageBox.Show("Dữ liệu thiếu cột Pro Forma Date ngày hóa đơn in", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); // return; //} if (InvoiceAmountBeforeVAT == -1) { MessageBox.Show("Dữ liệu thiếu cột Invoice Amount Before VAT", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (VATAmount == -1) { MessageBox.Show("Dữ liệu thiếu cột VAT Amount", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CustomerNumber == -1) { MessageBox.Show("Dữ liệu thiếu cột Customer Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CustomerName == -1) { MessageBox.Show("Dữ liệu thiếu cột Customer Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (StreetAddress == -1) { MessageBox.Show("Dữ liệu thiếu cột Street Address", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string SAPInvoiceNumberv = sourceData.Rows[rowixd][SAPInvoiceNumber].ToString(); if (SAPInvoiceNumberv != "" && Utils.IsValidnumber(SAPInvoiceNumberv)) { if (double.Parse(SAPInvoiceNumberv) > 0) { DataRow dr = batable.NewRow(); // dr["InvoiceRegistrationNumber"] = sourceData.Rows[rowixd][InvoiceRegistrationNumber].ToString().Truncate(225).Trim(); if (sourceData.Rows[rowixd][InvoiceNumber].ToString() != "" && Utils.IsValidnumber(sourceData.Rows[rowixd][InvoiceNumber].ToString())) { dr["InvoiceNumber"] = double.Parse(sourceData.Rows[rowixd][InvoiceNumber].ToString()); } try { dr["SAPDeliveryNumber"] = double.Parse(sourceData.Rows[rowixd][SAPDeliveryNumber].ToString()); dr["SAPInvoiceNumber"] = double.Parse(sourceData.Rows[rowixd][SAPInvoiceNumber].ToString()); //try //{ // dr["ProFormaDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][ProFormaDate].ToString()); //} //catch (Exception) //{ // dr["ProFormaDate"] = ""; //} dr["InvoiceAmountBeforeVAT"] = double.Parse(sourceData.Rows[rowixd][InvoiceAmountBeforeVAT].ToString());//.Trim(); dr["VATAmount"] = double.Parse(sourceData.Rows[rowixd][VATAmount].ToString()); dr["CustomerNumber"] = double.Parse(sourceData.Rows[rowixd][CustomerNumber].ToString());//.Trim(); dr["CustomerName"] = sourceData.Rows[rowixd][CustomerName].ToString().Truncate(225).Trim(); dr["StreetAddress"] = sourceData.Rows[rowixd][StreetAddress].ToString().Truncate(225).Trim(); } catch (Exception) { MessageBox.Show("Data có đủ cột nhưng dữ liệu ở dưới bị lost, please check", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } batable.Rows.Add(dr); } } #endregion } // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tblVat"; // Write from the source to the destination. // bulkCopy.ColumnMappings.Add("[InvoiceRegistrationNumber]", "[Invoice Registration Number]"); bulkCopy.ColumnMappings.Add("[InvoiceNumber]", "[Invoice Number]"); bulkCopy.ColumnMappings.Add("[SAPDeliveryNumber]", "[SAP Delivery Number]"); bulkCopy.ColumnMappings.Add("[SAPInvoiceNumber]", "[SAP Invoice Number]"); // bulkCopy.ColumnMappings.Add("[ProFormaDate]", "[Pro Forma Date]"); bulkCopy.ColumnMappings.Add("[InvoiceAmountBeforeVAT]", "[Invoice Amount Before VAT]");//-sua am bulkCopy.ColumnMappings.Add("[VATAmount]", "[VAT Amount]"); bulkCopy.ColumnMappings.Add("[CustomerNumber]", "[Customer Number]"); bulkCopy.ColumnMappings.Add("[CustomerName]", "[Customer Name]"); bulkCopy.ColumnMappings.Add("[StreetAddress]", "[Street Address]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importFuctionexcel(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); fuctionprog Rm = new fuctionprog(); bool kq = Rm.deleteallfction(); datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //------ //---------------fill data System.Data.DataTable sourceData = new System.Data.DataTable(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { try { conn.Open(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Open conext !", MessageBoxButtons.OK, MessageBoxIcon.Error); } // Get the data from the source table as a SqlDataReader. OleDbCommand command = new OleDbCommand( @"SELECT Code, Description,Example,Explan FROM [Sheet1$] WHERE ( Code is not null ) ", conn); OleDbDataAdapter adapter = new OleDbDataAdapter(command); // adapter.FillSchema(sourceData, SchemaType.Source); // sourceData.Columns["Posting Date"].DataType = typeof(DateTime); //sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float); //sourceData.Columns["Billed Qty"].DataType = typeof(float); //sourceData.Columns["Cond Value"].DataType = typeof(float); //sourceData.Columns["Sales Org"].DataType = typeof(string); //sourceData.Columns["Cust Name"].DataType = typeof(string); //sourceData.Columns["Outbound Delivery"].DataType = typeof(string); //sourceData.Columns["Mat Group"].DataType = typeof(string); //sourceData.Columns["Mat Group Text"].DataType = typeof(string); //sourceData.Columns["UoM"].DataType = typeof(string); try { adapter.Fill(sourceData); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill !", MessageBoxButtons.OK, MessageBoxIcon.Error); } conn.Close(); } // Utils util = new Utils(); string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { //@"SELECT Code, Description,Example,Explan // FROM [Sheet1$] // WHERE ( Code is not null ) ", conn); bulkCopy.DestinationTableName = "tbl_Kafuctionlist"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("Code", "Code"); bulkCopy.ColumnMappings.Add("Description", "Description"); bulkCopy.ColumnMappings.Add("Explan", "Explan"); bulkCopy.ColumnMappings.Add("Example", "Example"); #region tìm id #endregion try { bulkCopy.WriteToServer(sourceData); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
public void importsexceltoPricingcheck(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); datainportF inf = (datainportF)obj; string filename = inf.filename; // string filename = theDialog.FileName.ToString(); // string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); dc.ExecuteCommand("DELETE FROM tbl_ka_prCustomer"); // dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod); dc.CommandTimeout = 0; dc.SubmitChanges(); // Customer Sales Organization Name 1 House num &Street Street 4 City Telephone 1 Sales Office Delivering Plant Terms of Payment Price List Key Account No Sales district Created on Created by VAT Registration No.Central order block Order block for sales area // ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("Customer", typeof(double)); batable.Columns.Add("SalesOrg", typeof(string)); batable.Columns.Add("Name", typeof(string)); batable.Columns.Add("House", typeof(string)); batable.Columns.Add("Street", typeof(string)); batable.Columns.Add("City", typeof(string)); batable.Columns.Add("Telephone", typeof(string)); batable.Columns.Add("Salesoff", typeof(string)); batable.Columns.Add("Plant", typeof(string)); batable.Columns.Add("Termpayment", typeof(string)); batable.Columns.Add("Pricelist", typeof(string)); batable.Columns.Add("KeyAccount", typeof(double)); batable.Columns.Add("SalesDist", typeof(string)); batable.Columns.Add("Createdon", typeof(DateTime)); batable.Columns.Add("Createby", typeof(string)); batable.Columns.Add("VATregistno", typeof(string)); batable.Columns.Add("orderblock", typeof(string)); batable.Columns.Add("salesblock", typeof(string)); int Customerid = 0; int Salesogid = 0; int Nameid = 0; int Houseid = 0; int Streetid = 0; int Cityid = 0; int telephoneid = 0; int salesofficeid = 0; int Deliveringid = 0; int Termsid = 0; int Priceid = 0; int Keyid = 0; int Salesdistid = 0; int Createdonid = 0; int Createdbyid = 0; int VATid = 0; int orderblockid = 0; int salesblockid = 0; // View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test"); // vi1.ShowDialog(); int headindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim().Contains("Customer")) { Customerid = columid; headindex = rowid; } if (value.Trim().Contains("Sales Organization") && headindex == rowid) { Salesogid = columid; // headindex = 0; } if (value.Trim().Contains("Name 1") && headindex == rowid) { Nameid = columid; // headindex = 0; } if (value.Trim().Contains("House num & Street") && headindex == rowid) { Houseid = columid; } if (value.Trim().Contains("Street 4") && headindex == rowid) { Streetid = columid; } if (value.Trim().Contains("City") && headindex == rowid) { Cityid = columid; } if (value.Trim().Contains("Telephone 1") && headindex == rowid) { telephoneid = columid; // headindex = 0; } if (value.Trim().Contains("Sales Office") && headindex == rowid) { salesofficeid = columid; } if (value.Trim().Contains("Delivering Plant") && headindex == rowid) { Deliveringid = columid; } if (value.Trim().Contains("Terms of Payment") && headindex == 0) { Termsid = columid; headindex = 0; } if (value.Trim().Contains("Price List") && headindex == rowid) { Priceid = columid; } if (value.Trim().Contains("Key Account No") && headindex == rowid) { Keyid = columid; } if (value.Trim().Contains("Sales district") && headindex == rowid) { Salesdistid = columid; } if (value.Trim().Contains("Created on") && headindex == rowid) { Createdonid = columid; } if (value.Trim().Contains("Created by") && headindex == rowid) { Createdbyid = columid; } if (value.Trim().Contains("VAT Registration No") && headindex == rowid) { VATid = columid; } if (value.Trim().Contains("Central order block") && headindex == rowid) { orderblockid = columid; } if (value.Trim().Contains("Order block for sales area") && headindex == rowid) { salesblockid = columid; } #endregion } } // colum } // roww off heatder for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string customer = sourceData.Rows[rowixd][Customerid].ToString(); if (customer != "" && Utils.IsValidnumber(customer)) { DataRow dr = batable.NewRow(); dr["Customer"] = double.Parse(sourceData.Rows[rowixd][Customerid].ToString()); dr["SalesOrg"] = sourceData.Rows[rowixd][Salesogid].ToString().Trim(); dr["Name"] = sourceData.Rows[rowixd][Nameid].ToString().Trim(); dr["House"] = sourceData.Rows[rowixd][Houseid].ToString().Trim(); dr["Street"] = sourceData.Rows[rowixd][Streetid].ToString().Trim(); dr["City"] = sourceData.Rows[rowixd][Cityid].ToString().Trim(); dr["Telephone"] = sourceData.Rows[rowixd][telephoneid].ToString().Trim(); dr["Salesoff"] = sourceData.Rows[rowixd][salesofficeid].ToString().Trim(); dr["Plant"] = sourceData.Rows[rowixd][Deliveringid].ToString().Trim(); dr["Termpayment"] = sourceData.Rows[rowixd][Termsid].ToString().Trim(); dr["Pricelist"] = sourceData.Rows[rowixd][Priceid].ToString().Trim(); dr["KeyAccount"] = double.Parse(sourceData.Rows[rowixd][Keyid].ToString());//.Trim(); dr["SalesDist"] = sourceData.Rows[rowixd][Salesdistid].ToString().Trim(); dr["Createby"] = sourceData.Rows[rowixd][Createdbyid].ToString().Trim(); dr["VATregistno"] = sourceData.Rows[rowixd][VATid].ToString().Trim(); dr["orderblock"] = sourceData.Rows[rowixd][orderblockid].ToString().Trim(); dr["salesblock"] = sourceData.Rows[rowixd][salesblockid].ToString().Trim(); dr["Createdon"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][Createdonid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); batable.Rows.Add(dr); } #endregion }// row //conpy to server string destConnString = Utils.getConnectionstr(); //adapter.FillSchema(sourceData, SchemaType.Source); //sourceData.Columns["Posting Date"].DataType = typeof(DateTime); //batable.Columns.Add("", typeof(string)); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.DestinationTableName = "tbl_ka_prCustomer"; // Write from the source to the destination. bulkCopy.BulkCopyTimeout = 0; bulkCopy.ColumnMappings.Add("Customer", "Customer"); bulkCopy.ColumnMappings.Add("SalesOrg", "SalesOrg"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("House", "House"); bulkCopy.ColumnMappings.Add("Street", "Street"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("Telephone", "Telephone"); bulkCopy.ColumnMappings.Add("Salesoff", "SalesOff"); bulkCopy.ColumnMappings.Add("Plant", "Plant"); bulkCopy.ColumnMappings.Add("Termpayment", "TermPayment"); bulkCopy.ColumnMappings.Add("Pricelist", "PriceList"); bulkCopy.ColumnMappings.Add("KeyAccount", "KeyAccount"); bulkCopy.ColumnMappings.Add("SalesDist", "Salesdistrict"); bulkCopy.ColumnMappings.Add("Createdon", "Createdon"); bulkCopy.ColumnMappings.Add("Createby", "Createdby"); bulkCopy.ColumnMappings.Add("VATregistno", "VATRegistration"); bulkCopy.ColumnMappings.Add("orderblock", "blockOrder"); bulkCopy.ColumnMappings.Add("salesblock", "blockArea"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } //copy to server // string connection_string = Utils.getConnectionstr(); // LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string); // var typeffmain = typeof(tbl_KAbaseprice); // var typeffsub = typeof(tbl_KAbaseprice); // VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", ""); // inputcdata1.ShowDialog(); // View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA"); // TB.ShowDialog(); // } }
private void importFreglassessexcel(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); fREEGALSSES_CTRL Rm = new fREEGALSSES_CTRL(); string username = Utils.getusername(); bool kq = Rm.deleteallFreglassesBEgin(); datainportF inf = (datainportF)obj; string filename = inf.filename; //OleDbCommand command = new OleDbCommand( // @"SELECT [CUSTOMER], [SALORG],[COLAMT],[PERNO] // FROM [Sheet1$] // WHERE ( [CUSTOMER] is not null ) ", conn); // ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename); System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("CUSTOMER", typeof(double)); batable.Columns.Add("SALORG", typeof(string)); // batable.Columns.Add("PERNO", typeof(string)); batable.Columns.Add("Freeglass3years", typeof(int)); batable.Columns.Add("Freeglass3phantram", typeof(int)); batable.Columns.Add("AmountFreeglassesValue", typeof(double)); batable.Columns.Add("TypeDoc", typeof(string)); batable.Columns.Add("userupdate", typeof(string)); batable.Columns.Add("Posting_Date", typeof(DateTime)); // CUSTOMER SALORG Freeglass3years Freeglass3phantram AmountFreeglassesValue int CUSTOMERid = -1; int SALORGid = -1; int Freeglass3yearsid = -1; int Freeglass3phantramid = -1; int AmountFreeglassesValueid = -1; int rowseet = sourceData.Rows.Count; if (rowseet > 5) { rowseet = 5; } for (int rowid = 0; rowid < rowseet; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null && value != "") { // #region setcolum if (value.Trim() == ("CUSTOMER")) { CUSTOMERid = columid; // headindex = rowid; } if (value.Trim() == ("SALORG")) { SALORGid = columid; // headindex = 0; } if (value.Trim() == ("Freeglass3years")) { Freeglass3yearsid = columid; // headindex = 0; } if (value.Trim() == ("Freeglass3phantram")) { Freeglass3phantramid = columid; } if (value.Trim() == ("AmountFreeglassesValue")) { AmountFreeglassesValueid = columid; } } #endregion } // colum } // roww off heatder if (CUSTOMERid == -1) { MessageBox.Show("Dữ liệu thiếu cột CUSTOMER", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SALORGid == -1) { MessageBox.Show("Dữ liệu thiếu cột SALORG", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Freeglass3yearsid == -1) { MessageBox.Show("Dữ liệu thiếu cột Freeglass3years", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Freeglass3phantramid == -1) { MessageBox.Show("Dữ liệu thiếu cột Freeglass3phantram", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (AmountFreeglassesValueid == -1) { MessageBox.Show("Dữ liệu thiếu cột AmountFreeglassesValue", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string CUSTOMERvl = sourceData.Rows[rowixd][CUSTOMERid].ToString(); if (CUSTOMERvl != "" && Utils.IsValidnumber(CUSTOMERvl)) { if (double.Parse(CUSTOMERvl) > 0) { DataRow dr = batable.NewRow(); dr["CUSTOMER"] = double.Parse(sourceData.Rows[rowixd][CUSTOMERid].ToString());//.Trim(); dr["SALORG"] = sourceData.Rows[rowixd][SALORGid].ToString().Trim(); if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "") { dr["Freeglass3years"] = int.Parse(sourceData.Rows[rowixd][Freeglass3yearsid].ToString().Trim()); } else { dr["Freeglass3years"] = 0; } //---------------- if (sourceData.Rows[rowixd][Freeglass3phantramid].ToString() != "") { dr["Freeglass3phantram"] = int.Parse(sourceData.Rows[rowixd][Freeglass3phantramid].ToString().Trim()); } else { dr["Freeglass3phantram"] = 0; } ///---- if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "") { dr["AmountFreeglassesValue"] = int.Parse(sourceData.Rows[rowixd][AmountFreeglassesValueid].ToString().Trim()); } else { dr["AmountFreeglassesValue"] = 0; } dr["TypeDoc"] = "Begin";//.Trim();//sourceData.Rows[rowixd][COLAMTid].ToString().Trim(); dr["userupdate"] = username; dr["Posting_Date"] = DateTime.Today; batable.Rows.Add(dr); } } #endregion // Utils util = new Utils(); //---------------fill data } string destConnString = Utils.getConnectionstr(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.DestinationTableName = "tblFBL5NNewCol3year"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("CUSTOMER", "[Customer code]"); bulkCopy.ColumnMappings.Add("SALORG", "[Region]"); bulkCopy.ColumnMappings.Add("Freeglass3years", "[Freeglass3years]"); bulkCopy.ColumnMappings.Add("Freeglass3phantram", "[Freeglass3phantram]"); bulkCopy.ColumnMappings.Add("AmountFreeglassesValue", "[AmountFreeglassesValue]"); bulkCopy.ColumnMappings.Add("TypeDoc", "TypeDoc"); bulkCopy.ColumnMappings.Add("userupdate", "[userupdate]"); bulkCopy.ColumnMappings.Add("Posting_Date", "[Posting Date]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexcel2(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); Salesinput_ctrl md = new Salesinput_ctrl(); bool kq = md.deleteedlp(); //if (!kq) //{ // MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information); //} datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //------ //---------------fill data ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); // Sales Group Sales Group desc Sales Off Sales Office Desc System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("Soldto", typeof(float)); batable.Columns.Add("CustName", typeof(string)); batable.Columns.Add("MatNumber", typeof(string)); batable.Columns.Add("SalesOrg", typeof(string)); batable.Columns.Add("InvoiceDocNr", typeof(float)); batable.Columns.Add("OutboundDelivery", typeof(float)); batable.Columns.Add("DeliveryDate", typeof(DateTime)); batable.Columns.Add("SalesDistrict", typeof(string)); batable.Columns.Add("SalesDistrictdesc", typeof(string)); // batable.Columns.Add("SalesGroup", typeof(string)); // batable.Columns.Add("SalesOff", typeof(string)); // batable.Columns.Add("SalesOfficeDesc", typeof(float)); batable.Columns.Add("KeyAccNr", typeof(float)); batable.Columns.Add("InvoiceDate", typeof(DateTime)); batable.Columns.Add("MatGroup", typeof(float)); batable.Columns.Add("MatGroupText", typeof(string)); batable.Columns.Add("MatText", typeof(string)); batable.Columns.Add("CondType", typeof(string)); // batable.Columns.Add("CondTypedesc", typeof(string)); batable.Columns.Add("BilledQty", typeof(float)); batable.Columns.Add("Netvalue", typeof(float)); batable.Columns.Add("CondValue", typeof(float)); batable.Columns.Add("Currency", typeof(string)); // batable.Columns.Add("Username", typeof(string)); batable.Columns.Add("UoM", typeof(string)); string username = Utils.getusername(); batable.Columns.Add("Username"); batable.Columns["Username"].DefaultValue = username; #region setcolum int Soldtoid = -1; int CustNameid = -1; int MatNumberid = -1; int SalesOrgid = -1; int InvoiceDocNrid = -1; int OutboundDeliveryid = -1; int DeliveryDateid = -1; int SalesDistrictid = -1; int SalesDistrictdescid = -1; int KeyAccNrid = -1; int InvoiceDateid = -1; int MatGroupid = -1; int MatGroupTextid = -1; int MatTextid = -1; int BilledQtyid = -1; int Netvalueid = -1; int CondValueid = -1; int Currencyid = -1; int UoMid = -1; int CondTypeid = -1; // View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test"); // vi1.ShowDialog(); // int headindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim() == "Sold-to") { Soldtoid = columid; // headindex = rowid; } if (value.Trim() == ("Cust Name")) { CustNameid = columid; // headindex = rowid; } if (value.Trim() == ("Cond Type")) { CondTypeid = columid; // headindex = rowid; } if (value.Trim() == ("Mat Number")) { MatNumberid = columid; // headindex = rowid; } if (value.Trim() == "Sales Org") { SalesOrgid = columid; // headindex = rowid; } if (value.Trim() == "Invoice Doc Nr") { InvoiceDocNrid = columid; // headindex = rowid; } if (value.Trim() == "Outbound Delivery") { OutboundDeliveryid = columid; // headindex = rowid; } if (value.Trim() == "Delivery Date") { DeliveryDateid = columid; // headindex = rowid; } if (value.Trim() == "Sales District") { SalesDistrictid = columid; // headindex = rowid; } if (value.Trim() == "Sales District desc") { SalesDistrictdescid = columid; // headindex = rowid; } if (value.Trim() == "Key Acc Nr") { KeyAccNrid = columid; // headindex = rowid; } if (value.Trim() == "Mat Group") { MatGroupid = columid; // headindex = rowid; } if (value.Trim() == "Mat Group Text") { MatGroupTextid = columid; // headindex = rowid; } if (value.Trim() == "Mat Text") { MatTextid = columid; // headindex = rowid; } if (value.Trim() == "UoM") { UoMid = columid; // headindex = rowid; } if (value.Trim() == "Billed Qty") { BilledQtyid = columid; // headindex = rowid; } if (value.Trim() == "Net value") { Netvalueid = columid; // headindex = rowid; } if (value.Trim().Contains("Cond Value")) { CondValueid = columid; // headindex = rowid; } if (value.Trim() == "Invoice Date") { InvoiceDateid = columid; // headindex = rowid; } if (value.Trim() == "Currency") { Currencyid = columid; // headindex = rowid; } #endregion } } // colum } // roww off heatder #endregion if (Soldtoid == -1) { MessageBox.Show("Please check sold-to colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CondTypeid == -1) { MessageBox.Show("Please check Cond Type colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CustNameid == -1) { MessageBox.Show("Please check Customer colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatNumberid == -1) { MessageBox.Show("Please check MatNumber colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesOrgid == -1) { MessageBox.Show("Please check SalesOrg colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (InvoiceDocNrid == -1) { MessageBox.Show("Please check Invoice Doc Nr colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (OutboundDeliveryid == -1) { MessageBox.Show("Please check Outbound Delivery colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (DeliveryDateid == -1) { MessageBox.Show("Please check Delivery Date colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesDistrictid == -1) { MessageBox.Show("Please check Sales District colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesDistrictdescid == -1) { MessageBox.Show("Please check Sales District desc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (KeyAccNrid == -1) { MessageBox.Show("Please check KeyAcc Nr colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (InvoiceDateid == -1) { MessageBox.Show("Please check Invoice Date colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatGroupid == -1) { MessageBox.Show("Please check MatGroup colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatGroupTextid == -1) { MessageBox.Show("Please check MatGroupText colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (BilledQtyid == -1) { MessageBox.Show("Please check Billed Qty colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Netvalueid == -1) { MessageBox.Show("Please check Netvalue colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CondValueid == -1) { MessageBox.Show("Please check CondValue colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Currencyid == -1) { MessageBox.Show("Please check Currency colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (UoMid == -1) { MessageBox.Show("Please check UoM colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string customer = sourceData.Rows[rowixd][Soldtoid].ToString(); if (customer != "" && Utils.IsValidnumber(customer)) { DataRow dr = batable.NewRow(); dr["Soldto"] = double.Parse(sourceData.Rows[rowixd][Soldtoid].ToString()); dr["CustName"] = sourceData.Rows[rowixd][CustNameid].ToString().Trim(); dr["MatNumber"] = sourceData.Rows[rowixd][MatNumberid].ToString().Trim(); dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrgid].ToString().Trim(); dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNrid].ToString().Trim()); dr["OutboundDelivery"] = double.Parse(sourceData.Rows[rowixd][OutboundDeliveryid].ToString().Trim()); dr["DeliveryDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][DeliveryDateid].ToString().Trim()); dr["SalesDistrict"] = sourceData.Rows[rowixd][SalesDistrictid].ToString().Trim(); dr["CondType"] = sourceData.Rows[rowixd][CondTypeid].ToString().Trim(); dr["SalesDistrictdesc"] = sourceData.Rows[rowixd][SalesDistrictdescid].ToString().Trim(); if (sourceData.Rows[rowixd][KeyAccNrid].ToString() != "" && sourceData.Rows[rowixd][KeyAccNrid] != null) { dr["KeyAccNr"] = double.Parse(sourceData.Rows[rowixd][KeyAccNrid].ToString().Trim()); } dr["MatGroup"] = double.Parse(sourceData.Rows[rowixd][MatGroupid].ToString().Trim()); dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupTextid].ToString();//.Trim(); dr["MatText"] = sourceData.Rows[rowixd][MatTextid].ToString().Trim(); dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQtyid].ToString().Trim()); dr["Netvalue"] = double.Parse(sourceData.Rows[rowixd][Netvalueid].ToString().Trim()); dr["Currency"] = sourceData.Rows[rowixd][Currencyid].ToString().Trim(); dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValueid].ToString().Trim()); dr["InvoiceDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][InvoiceDateid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); dr["UoM"] = sourceData.Rows[rowixd][UoMid].ToString().Trim(); // dr["Username"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][Createdonid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); batable.Rows.Add(dr); } #endregion }// row string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tbl_kasalesTemp"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("Soldto", "[Sold-to]"); bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]"); bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]"); bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]"); bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]"); bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]"); bulkCopy.ColumnMappings.Add("[DeliveryDate]", "[Delivery Date]"); bulkCopy.ColumnMappings.Add("[SalesDistrict]", "[Sales District]"); bulkCopy.ColumnMappings.Add("[SalesDistrictdesc]", "[Sales District desc]"); // bulkCopy.ColumnMappings.Add("[SalesGroup]", "[Sales Group]"); //// bulkCopy.ColumnMappings.Add("[SalesOff]", "[Sales Off]"); // bulkCopy.ColumnMappings.Add("[SalesOfficeDesc]", "[Sales Office Desc]"); bulkCopy.ColumnMappings.Add("[KeyAccNr]", "[Key Acc Nr]"); bulkCopy.ColumnMappings.Add("[InvoiceDate]", "[Invoice Date]"); bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]"); bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]"); bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]"); bulkCopy.ColumnMappings.Add("[CondType]", "[Cond Type]"); // bulkCopy.ColumnMappings.Add("[CondTypedesc]", "[Cond Type desc]"); bulkCopy.ColumnMappings.Add("[BilledQty]", "[EC]"); bulkCopy.ColumnMappings.Add("[Netvalue]", "[NSR]"); bulkCopy.ColumnMappings.Add("[CondValue]", "[GSR]"); bulkCopy.ColumnMappings.Add("[Currency]", "[Currency]"); bulkCopy.ColumnMappings.Add("[Username]", "[Username]"); // bulkCopy.ColumnMappings.Add("[SalesGroupdesc]", "[Sales Group desc]"); bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]"); // bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }