Bulk Copy from JDE Table to SQL Table

//Write Jde Query to Bring Record.
//Float field should be handled by below code
//CAST(CAST(a.ABAN8 as  decimal(20,0)) as varchar)
//Map table

 var columnMapping = new List<string>();
                //columnMapping.Add("HashField,HashField,0");
                // columnMapping.Add("AddressId,AddressId");
                columnMapping.Add("ERPAddressId,ERPAddressId");
                columnMapping.Add("AddressName,AddressName");
                columnMapping.Add("BranchPlantForPricing,BranchPlantForPricing");
                columnMapping.Add("BranchPlantAvail,BranchPlantAvail");
                columnMapping.Add("BranchPlantAvailGroup,BranchPlantAvailGroup");
                columnMapping.Add("Lang,Lang");
                columnMapping.Add("Ordertype,Ordertype");
                columnMapping.Add("CreatedBy,CreatedBy");
                columnMapping.Add("DateCreated,DateCreated");
                columnMapping.Add("AddressLine1,AddressLine1");
                columnMapping.Add("AddressLine2,AddressLine2");
                columnMapping.Add("AddressLine3,AddressLine3");
                columnMapping.Add("AddressLine4,AddressLine4");
                columnMapping.Add("City,City");
                columnMapping.Add("State,State");
                columnMapping.Add("ZipCode,ZipCode");
                columnMapping.Add("County,County");
                columnMapping.Add("Country,Country");
                columnMapping.Add("MailingName,MailingName");
                columnMapping.Add("AdjustmentSchedule,AdjustmentSchedule");
                columnMapping.Add("BillingType,BillingType");
                columnMapping.Add("IsPORequired,IsPORequired");
                columnMapping.Add("AllowBackorder,AllowBackorder");
                columnMapping.Add("ParentAddressId,ParentAddressId");
                columnMapping.Add("ParentAddressName,ParentAddressName");
                columnMapping.Add("IsActive,IsActive");



Calling Function

  //Truncating Staging Address Table
                string TruncateQuery = "TRUNCATE TABLE ssAddressStagging";
                ExecuteNonQuery(TruncateQuery, E2wConnection);
                E2WLogWriter.Log("----------------------------------------------");
                ReturnMessage.AppendLine(Common.BulkImportData(queryJDE.ToString(), ConnStr, E2wConnection, "ssAddressStagging", columnMapping));


//Function
 public static string ExecuteNonQuery(string Sql, String ConString)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConString))
                {
                    SqlCommand cmd = new SqlCommand();
                    con.Open();
                    cmd.CommandTimeout = 0;
                    cmd.CommandText = Sql;
                    cmd.Connection = con;
                    int Result = cmd.ExecuteNonQuery();
                    return "Success!," + Result + " Records Affected!";
                }
            }
            catch (Exception ex)
            {
                return "Failed :" + ex.Message;
            }
        }
 private static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("Imported {0} Records...", e.RowsCopied);
        }

public static string BulkImportData(string SourceScript, string SourceConnectionString, string DestinationConnectionString, string DestinationTable, List<string> columnMapping)
        {
            string ReturnMessage = "";
            string CountScript = "Select count(*) from " + DestinationTable;

            try
            {
               
                using (SqlConnection sourceConnection = new SqlConnection(SourceConnectionString.Replace("Provider=SQLOLEDB;","")))
                {
                    sourceConnection.Open();
                    // Perform an initial count on the destination table.
                    SqlConnection DestinationConnection = new SqlConnection(DestinationConnectionString);
                    DestinationConnection.Open();
                    SqlCommand commandRowCount = new SqlCommand(CountScript, DestinationConnection);
                    commandRowCount.CommandTimeout = 0;
                    long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
                    ReturnMessage +=
                    ReturnMessage += "Starting rows Count " + countStart + Environment.NewLine;

                    // Get data from the source table as a SqlDataReader.
                    SqlCommand commandSourceData = new SqlCommand(SourceScript, sourceConnection);
                    commandSourceData.CommandTimeout = 0;
                    SqlDataReader reader = commandSourceData.ExecuteReader();
                    using (SqlConnection destinationConnection = new SqlConnection(DestinationConnectionString))
                    {
                        destinationConnection.Open();
                        using (SqlBulkCopy bulkCopy =
                                   new SqlBulkCopy(destinationConnection))
                        {
                            bulkCopy.DestinationTableName = DestinationTable;
                            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                            bulkCopy.NotifyAfter = 5000; //For every 5000 records notify
                            bulkCopy.BulkCopyTimeout = 0;

                            try
                            {
                                foreach (var mapping in columnMapping)
                                {
                                    var split = mapping.Split(new[] { ',' });
                                    bulkCopy.ColumnMappings.Add(split[0], split[1]);
                                }

                                // Write from the source to the destination.
                                bulkCopy.WriteToServer(reader);
                            }
                            catch (Exception ex)
                            {

                                if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
                                {
                                    string pattern = @"\d+";
                                    Match match = Regex.Match(ex.Message.ToString(), pattern);
                                    var index = Convert.ToInt32(match.Value) - 1;

                                    FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
                                    var sortedColumns = fi.GetValue(bulkCopy);
                                    var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

                                    FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
                                    var metadata = itemdata.GetValue(items[index]);

                                    var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                                    var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                                    ReturnMessage+=(String.Format("Column: {0} contains data with a length greater than: {1}", column, length));
                                }                                                         
                               
                                ReturnMessage += ex.Message + Environment.NewLine;
                                //       Console.WriteLine(ex.Message);
                                throw ex;
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }

                        // Perform a final count on the destination 
                        // table to see how many rows were added.
                        long countEnd = System.Convert.ToInt32(
                            commandRowCount.ExecuteScalar());

                        // System.IO.File.AppendAllText(sLogSummaryFileName,
                        //E2WLogWriter.WriteSummaryLog(sSummary.ToString(), sLogSummaryFileName);
                        ReturnMessage += "Ending rows Count " + countEnd + Environment.NewLine;
                        ReturnMessage += countEnd - countStart + " rows were added." + Environment.NewLine;
                        ReturnMessage = "Bulk Import was Successful!" + Environment.NewLine + ReturnMessage;
                        Console.WriteLine(ReturnMessage);
                        E2WLogWriter.Log(ReturnMessage);
                    }
                }
            }
            catch (Exception ex)
            {           
               
                ReturnMessage += "Bulk Import Failed!" + Environment.NewLine + ex.Message + Environment.NewLine;
                Console.WriteLine(ReturnMessage);
            }
            return ReturnMessage;
        }


//Updating Unique field with NextNumber appending AD retrieving it from another table

   string SourceScript = "";
                StringBuilder queryUpdate = new StringBuilder();

                //queryUpdate.Append("Update t1 Set t1.AddressID=t2.Addr From ssAddressStagging t1 inner join ");
                //queryUpdate.Append("(SELECT    Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as Addr1,ERPAddressid,'AD'+ (Case When Len(Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) )<8 Then  REPLACE(STR( Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) , 8), SPACE(1), '0') ELSE  Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid))  end)  Addr");
                //queryUpdate.Append("  FROM    ssAddressStagging x    WHERE   x.ERPAddressid = x.ERPAddressid and x.ErpAddressid not in (Select ErpAddressId from ssAddress )) as t2 on t1.ErpAddressID=t2.ErpAddressID");
                queryUpdate.Append("Update t1 Set t1.AddressID=t2.Addr From ssAddressStagging t1 inner join ( ");
                queryUpdate.Append("SELECT   ( Convert(Varchar(100),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int) + cast( ROW_NUMBER() OVER(ORDER BY x.ERPAddressid) as int))) as Addr1, ");
                queryUpdate.Append(" ERPAddressid,'AD'+ (Case When Len(Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)  + CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as int)) )<=8 Then  ");
                queryUpdate.Append(" REPLACE(STR( Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)+ CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid))as int) , 2)), SPACE(3), '0') ");
                queryUpdate.Append(" ELSE Case when ");
                queryUpdate.Append("Len(Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)  + CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as int)) )>8 OR (Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)  + CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as int))=99999999) ");
                queryUpdate.Append("Then ");
                queryUpdate.Append("RIGHT(REPLACE(STR( Convert(varchar(50),CAST(('1') as int)+ CAST((ROW_NUMBER() OVER(ORDER BY x.ErpAddressid))as int) , 4)), SPACE(1), '0'), LEN(REPLACE(STR( Convert(varchar(50),CAST(('1') as int)+ CAST((ROW_NUMBER() OVER(ORDER BY x.ErpAddressid))as int) , 4)), SPACE(1), '0')) - 2) ");
                queryUpdate.Append("END ");
                queryUpdate.Append("END ");
                queryUpdate.Append(" ) Addr  FROM    ssAddressStagging x    WHERE   x.ERPAddressid = x.ERPAddressid and x.ErpAddressid not in (Select ErpAddressId from ssAddress )) as t2 on t1.ErpAddressID=t2.ErpAddressID ");
                E2WLogWriter.Log("----------------------------------------------");
                int Result = DBtools.ExecuteUpdate(queryUpdate.ToString(), E2wConnection + ";Provider=SQLOLEDB;");
                ReturnMessage.AppendLine(Convert.ToString(Result));


//END Update SsNextNumber with the last updated AddressId

 StringBuilder queryUpdateNextNumber = new StringBuilder();
                queryUpdateNextNumber.Append("update ssnextnumber SET NextNumber=");
                queryUpdateNextNumber.Append("(SELECT REPLACE(LTRIM(REPLACE((SELECT top 1 * from Split((select top 1 addressid from ssAddress order by addressid desc),'D') order by items asc), '0', ' ')),' ', '0'))   where Prefix='AD'");
                int ResultUpdateNextNumber = DBtools.ExecuteUpdate(queryUpdateNextNumber.ToString(), E2wConnection + ";Provider=SQLOLEDB;");
                ReturnMessage.AppendLine(Convert.ToString(ResultUpdateNextNumber));