Ejemplo n.º 1
0
        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();
                }
            }
        }
Ejemplo n.º 2
0
        private void importsexce2sales(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("CustName", typeof(string));
            batable.Columns.Add("MatNumber", typeof(string));
            batable.Columns.Add("SalesOrg", typeof(string));
            batable.Columns.Add("InvoiceDocNr", typeof(double));
            batable.Columns.Add("OutboundDelivery", typeof(double));
            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(double));
            batable.Columns.Add("KeyAccNr", typeof(double));
            batable.Columns.Add("InvoiceDate", typeof(DateTime));
            batable.Columns.Add("MatGroup", typeof(double));
            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(double));
            batable.Columns.Add("Netvalue", typeof(double));
            batable.Columns.Add("CondValue", typeof(double));
            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 < 6; rowid++)
            {
                if (rowid < sourceData.Rows.Count)
                {
                    #region



                    for (int columid = 0; columid < sourceData.Columns.Count; columid++)
                    {
                        #region


                        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
                        }

                        #endregion
                    }// colum


                    #endregion
                }
            }// 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());

                    if (Utils.chageExceldatetoData(sourceData.Rows[rowixd][DeliveryDateid].ToString().Trim()) == new DateTime(00001, 01, 01))
                    {
                        MessageBox.Show("Định dạng ngày tháng của dòng  " + rowixd.ToString() + " cột " + DeliveryDateid.ToString() + " sai, must be dd.MM.yyyy", "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    else
                    {
                        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());

                    if (Utils.chageExceldatetoData(sourceData.Rows[rowixd][InvoiceDateid].ToString()) == new DateTime(00001, 01, 01))
                    {
                        MessageBox.Show("Định dạng ngày tháng của dòng  " + rowixd.ToString() + " cột " + InvoiceDateid.ToString() + " sai, must be dd.MM.yyyy", "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    else
                    {
                        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();
                }
            }
        }