Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL Threading optimization , +
Hey All,
 
so I have a console application written in C# and I was under the impression that I would have multiple files and could insert them all at once. As we all know, things and specs change. Instead of having about 1000 files and being able to spawn a new thread to download and process, I have 1 file that contains around 300 records that require 3 inserts to make one potent object. This obviously is taking a very long time. I cant lump records in the file and spawn a new thread as they have to be inserted in order. Any ideas or help would be appreciated
 
code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Net;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
 

namespace AWG_Special_Insert
{
    class Program
    {
       
        public class DownloadThreadStartParam
        {
            public string FileName { get; set; }
 
            public ManualResetEvent ResetEvent { get; set; }
        }
        
        static void Main(string[] args)
        {
            WaitForThreads(DownloadFiles());
        }
 
        public static void WaitForThreads(List<downloadthreadstartparam> threads)
        {
            //make sure the application doesnt close until all the threads are completed
            while (threads.Any())
            {
                threads[0].ResetEvent.WaitOne();
                threads.RemoveAt(0);
            }
        }
 
        public static List<downloadthreadstartparam> DownloadFiles()
        {
            var threadStartParams = new List<downloadthreadstartparam>();
 
            var Files = GetBatchFiles();
 
            foreach (object newFile in Files)
            {
                //find if its acatually data or the archive folder
                string file = newFile.ToString();
                if (file.StartsWith("LBL"))
                {
                    //download the file and process for thread asynchronous insert
                    DownloadThreadStartParam param = new DownloadThreadStartParam();
                    param.FileName = newFile.ToString();
                    param.ResetEvent = new ManualResetEvent(false);
                    threadStartParams.Add(param); //add to the list of threadstart params -- so we know how to track

                    Thread downloadThread = new Thread(DownloadFile);
                    downloadThread.Start(param);
                }
            }
 
            return threadStartParams;
        }
 
        private static string[] GetBatchFiles()
        {
            string ReturnStr = "";
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create("Filler");
            request.Method = WebRequestMethods.Ftp.ListDirectory;
 
            StringWriter sw = new StringWriter();
            //Get a response
            FtpWebResponse response = (FtpWebResponse)request.GetResponse();
            Stream responseStream = response.GetResponseStream();
 
            //Convert the response to a string
            int ch;
            while ((ch = responseStream.ReadByte()) != -1)
                ReturnStr = ReturnStr + Convert.ToChar(ch);
 
            //clean up
            responseStream.Close();
            response.Close();
            //split the string by new line
            string[] sep = { "\r\n" };
            string[] Files = ReturnStr.Split(sep, StringSplitOptions.RemoveEmptyEntries);
            return Files;
        }
 
        private static void DeleteBatchFiles(string fileName)
        {
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create("Filler" + fileName);
            request.Method = WebRequestMethods.Ftp.DeleteFile;
 
            //Get a response
            FtpWebResponse response = (FtpWebResponse)request.GetResponse();
            Stream responseStream = response.GetResponseStream();
 
        }
 
        private static void DownloadFile(object param)
        {
            var threadParam = param as DownloadThreadStartParam;
 
            //Connect to the FTP
            string fileName = threadParam.FileName;
            //make sure that we are getting the data files not the archive folder

            FtpWebRequest request = (FtpWebRequest)WebRequest.Create(FTP Filler);
            //Specify the download location
            request.Method = WebRequestMethods.Ftp.DownloadFile;
 
            string replaceFile = null;
            if (fileName.Contains(".DTA"))
            {
                replaceFile = fileName.Replace(".DTA", ".txt");
            }
            else if (fileName.Contains(".DAT"))
            {
                replaceFile = fileName.Replace(".DAT", ".txt");
            }
            else
            {
                replaceFile = fileName;
            }
 
            //Initialize the FileStream and specify the locale path
            FileStream localFileStream = new FileStream(@"E:\replaceFile", FileMode.Create, FileAccess.Write);
 
            //get a response
            WebResponse response = request.GetResponse();
            Stream responseStream = response.GetResponseStream();
 
            //create the file
            byte[] buffer = new byte[1024];
            int bytesRead = responseStream.Read(buffer, 0, 1012);
            while (bytesRead != 0)
            {
                localFileStream.Write(buffer, 0, bytesRead);
                bytesRead = responseStream.Read(buffer, 0, 1024);
            }
 
            //delete the file after its been downloaded
            //request.Method = WebRequestMethods.Ftp.DeleteFile;
            //FtpWebResponse responseFileDelete = (FtpWebResponse)request.GetResponse();

            //clean up
            localFileStream.Close();
            response.Close();
            responseStream.Close();
 
            //Now we actually want to process the file
            InsertIntoTables(replaceFile);
            DeleteBatchFiles(fileName);
 
            threadParam.ResetEvent.Set();
        }
 
        public static void InsertIntoTables(string FileName)
        {
            string line = null;
            DataParse parse = null;
            Level newLevel = null;
            int tagTypeId = 0;
            int stampID = 0;
 
            string strConnection = @"Filler";
           
 
            using (StreamReader sr = File.OpenText(FileName))
            {
                using (SqlConnection conn = new SqlConnection(strConnection))
                {
                    
                    List<sign> signs = new List<sign>();
                    while ((line = sr.ReadLine()) != null)
                    {
                        if (conn.State != ConnectionState.Open)
                        {
                            conn.Open();
                        }
 
                        Sign sign = new Sign();
 
                        //need to find level 
                        newLevel = SignManager.GetLevelByLevelName(line.Substring(3, 4));
 
                        if (newLevel != null)
                        {
                            parse = new DataParse();
                            tagTypeId = Convert.ToInt32(line.Substring(0, 2));
                            if (tagTypeId == 01)
                                stampID = parse.StockCode01StampId;
                            else if (tagTypeId == 02)
                                stampID = parse.StockCode02StampId;
                            else if (tagTypeId == 03)
                                stampID = parse.StockCode03StampId;
                            else if (tagTypeId == 04)
                                stampID = parse.StockCode04StampId;
                            else if (tagTypeId == 05)
                                stampID = parse.StockCode05StampId;
                            else if (tagTypeId == 06)
                                stampID = parse.StockCode06StampId;
                            else if (tagTypeId == 07)
                                stampID = parse.StockCode07StampId;
                            else if (tagTypeId == 08)
                                stampID = parse.StockCode08StampId;
                            else if (tagTypeId == 10)
                                stampID = parse.StockCode10StampId;
                            else if (tagTypeId == 12)
                                stampID = parse.StockCode12StampId;
                            else if (tagTypeId == 15)
                                stampID = parse.StockCode15StampId;
                            else if (tagTypeId == 18)
                                stampID = parse.StockCode18StampId;
                            else if (tagTypeId == 19)
                                stampID = parse.StockCode19StampId;
                            else if (tagTypeId == 20)
                                stampID = parse.StockCode20StampId;
                            else if (tagTypeId == 21)
                                stampID = parse.StockCode21StampId;
                            else if (tagTypeId == 22)
                                stampID = parse.StockCode22StampId;
                            else if (tagTypeId == 23)
                                stampID = parse.StockCode23StampId;
                            else if (tagTypeId == 24)
                                stampID = parse.StockCode24StampId;
                            else if (tagTypeId == 25)
                                stampID = parse.StockCode25StampId;
                            else if (tagTypeId == 33)
                                stampID = parse.StockCode33StampId;
                            else if (tagTypeId == 39)
                                stampID = parse.StockCode39StampId;
                            else if (tagTypeId == 40)
                                stampID = parse.StockCode40StampId;
                            else if (tagTypeId == 44)
                                stampID = parse.StockCode44StampId;
                            else if (tagTypeId == 45)
                                stampID = parse.StockCode45StampId;
                            else if (tagTypeId == 53)
                                stampID = parse.StockCode53StampId;
                            else if (tagTypeId == 61)
                                stampID = parse.StockCode61StampId;
                            else if (tagTypeId == 62)
                                stampID = parse.StockCode62StampId;
                            else if (tagTypeId == 63)
                                stampID = parse.StockCode63StampId;
                            else if (tagTypeId == 66)
                                stampID = parse.StockCode66StampId;
                            else if (tagTypeId == 73)
                                stampID = parse.StockCode73StampId;
                            else if (tagTypeId == 74)
                                stampID = parse.StockCode74StampId;
                            else if (tagTypeId == 77)
                                stampID = parse.StockCode77StampId;
                            else if (tagTypeId == 78)
                                stampID = parse.StockCode78StampId;
                            else if (tagTypeId == 79)
                                stampID = parse.StockCode79StampId;
                            else if (tagTypeId == 80)
                                stampID = parse.StockCode80StampId;
                            else if (tagTypeId == 83)
                                stampID = parse.StockCode83StampId;
                            else if (tagTypeId == 86)
                                stampID = parse.StockCode86StampId;
                            else if (tagTypeId == 87)
                                stampID = parse.StockCode87StampId;
                            else if (tagTypeId == 88)
                                stampID = parse.StockCode88StampId;
                            else if (tagTypeId == 89)
                                stampID = parse.StockCode89StampId;
                            else if (tagTypeId == 92)
                                stampID = parse.StockCode92StampId;
                            else if (tagTypeId == 93)
                                stampID = parse.StockCode93StampId;
                            else if (tagTypeId == 96)
                                stampID = parse.StockCode96StampId;
                            else if (tagTypeId == 98)
                                stampID = parse.StockCode98StampId;
                            else if (tagTypeId == 99)
                                stampID = parse.StockCode99StampId;
 
                            parse.ChangeTypeFieldValue = line.Substring(10, 2);
                            parse.OBIFieldValue = line.Substring(14, 5); //make 5 digits on the upload per MH 10/4
                            parse.MajorCategoryValue = line.Substring(19, 5);
                            parse.MinorCategoryValue = line.Substring(24, 5);
                            parse.ItemCodeValue = line.Substring(41, 6);//per LS and MH 1022 need to make it not strip leading zeros, but only 6 digits, start at 42
                            parse.ItemDescriptionValue = line.Substring(47, 30);
                            parse.PackValue = line.Substring(77, 5);
                            parse.SizeValue = line.Substring(82, 9);
                            parse.VendorValue = line.Substring(91, 7);
                            parse.EffectiveMSU = line.Substring(98, 3);
                            parse.EffectiveRetailValue = line.Substring(101, 6);
                            parse.SecondEffectiveMSU = line.Substring(107, 3);
                            parse.SecondRetailValue = line.Substring(110, 6);
                            parse.UsaveValue = line.Substring(116, 6);
                            parse.PricePerUnitValue = line.Substring(122, 6);
                            parse.SaleStartDateValue = line.Substring(128, 6);
                            parse.SaleEndDateValue = line.Substring(134, 6);
                            parse.PalletValue = line.Substring(140, 4);
                            parse.MasterPackValue = line.Substring(144, 4);
                            parse.TodaysDateValue = line.Substring(150, 6);
                            parse.AWGSizeIDValue = line.Substring(156, 2);
                            parse.UPC13Value = line.Substring(158, 13);
                            parse.LOQValue = line.Substring(171, 3);
 
                            DateTime startDate = new DateTime();
                            DateTime endDate = new DateTime();
                            string stringStartDate = line.Substring(128, 6);
                            if (stringStartDate == null)
                            {
                                startDate = Convert.ToDateTime(stringStartDate);
                            }
                            else
                            {
                                startDate = DateTime.Now;
                            }
 
                            string stringEndDate = line.Substring(128, 6);
                            if (stringEndDate == null)
                            {
                                endDate = Convert.ToDateTime(stringStartDate);
                            }
                            else
                            {
                                endDate = DateTime.Now.AddDays(6);
                            }
 
                            int Quantity = 1;
                            sign.LevelID = newLevel.LevelID;
                            sign.SignIsValid = true;
                            sign.ArbitraryDate = startDate.AddDays(6);
                            sign.SoftPrint = true;
                            sign.DepartmentID = 1;
                            sign.SignLastUpdated = DateTime.Now;
                            sign.StampID = stampID;
                            sign.SignTypeID = 8;
                            sign.SignQuantity = Quantity;
 
                            DateTime today = DateTime.Now;
                            if (newLevel.LevelUser.LevelUserInfoId == 11)
                            {
                                if (today.DayOfWeek.Equals(DayOfWeek.Friday))
                                {
                                    sign.BatchTypeId = 1;
                                }
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Saturday))
                                //{
                                //    sign.BatchTypeId = 1;
                                //}
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Sunday))
                                //{
                                //    sign.BatchTypeId = 1;
                                //}
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Monday))
                                //{
                                //    sign.BatchTypeId = 1;
                                //}
                                else if (today.DayOfWeek.Equals(DayOfWeek.Tuesday))
                                {
                                    sign.BatchTypeId = 1;
                                }
                                else if (today.DayOfWeek.Equals(DayOfWeek.Wednesday))
                                {
                                    sign.BatchTypeId = 2;
                                }
                                else if (today.DayOfWeek.Equals(DayOfWeek.Thursday))
                                {
                                    sign.BatchTypeId = 2;
                                }
                            }
                            else if (newLevel.LevelUser.LevelUserInfoId == 12)
                            {
                                if (today.DayOfWeek.Equals(DayOfWeek.Friday))
                                {
                                    sign.BatchTypeId = 5;
                                }
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Saturday))
                                //{
                                //    sign.BatchTypeId = 5;
                                //}
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Sunday))
                                //{
                                //    sign.BatchTypeId = 5;
                                //}
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Monday))
                                //{
                                //    sign.BatchTypeId = 5;
                                //}
                                else if (today.DayOfWeek.Equals(DayOfWeek.Tuesday))
                                {
                                    sign.BatchTypeId = 5;
                                }
                                else if (today.DayOfWeek.Equals(DayOfWeek.Wednesday))
                                {
                                    sign.BatchTypeId = 6;
                                }
                                else if (today.DayOfWeek.Equals(DayOfWeek.Thursday))
                                {
                                    sign.BatchTypeId = 6;
                                }
                            }
                            else if (newLevel.LevelUser.LevelUserInfoId == 13)
                            {
                                if (today.DayOfWeek.Equals(DayOfWeek.Friday))
                                {
                                    sign.BatchTypeId = 3;
                                }
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Saturday))
                                //{
                                //    sign.BatchTypeId = 5;
                                //}
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Sunday))
                                //{
                                //    sign.BatchTypeId = 5;
                                //}
                                //else if (today.DayOfWeek.Equals(DayOfWeek.Monday))
                                //{
                                //    sign.BatchTypeId = 5;
                                //}
                                else if (today.DayOfWeek.Equals(DayOfWeek.Tuesday))
                                {
                                    sign.BatchTypeId = 3;
                                }
                                else if (today.DayOfWeek.Equals(DayOfWeek.Wednesday))
                                {
                                    sign.BatchTypeId = 4;
                                }
                                else if (today.DayOfWeek.Equals(DayOfWeek.Thursday))
                                {
                                    sign.BatchTypeId = 4;
                                }
                            }
 

                            //Fill up all the signfields first
                            if (!String.IsNullOrWhiteSpace(parse.ChangeTypeFieldValue))
                            {
                                SignField field1 = new SignField();
                                field1.FieldID = parse.ChangeTypeFieldID;
                                field1.SignFieldValue = parse.ChangeTypeFieldValue.TrimStart(' ');
                                field1.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field1);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.EffectiveRetailValue))
                            {
                                //LS and MH - need to handle 2nd MSU if its there and make the 2/4 example
                                SignField field2 = new SignField();
                                field2.FieldID = parse.EffectiveRetailID;
                                string effectiveRetail = null;
                                string before = null;
                                string after = null;
 
                                if (parse.EffectiveRetailValue.Length == 6)
                                {
                                    before = parse.EffectiveRetailValue.Substring(0, 4);
                                    after = parse.EffectiveRetailValue.Substring(4, 2);
                                    effectiveRetail = before + "." + after;
                                }
                                else if (parse.EffectiveRetailValue.Length == 5)
                                {
                                    before = parse.EffectiveRetailValue.Substring(0, 3);
                                    after = parse.EffectiveRetailValue.Substring(3, 2);
                                    effectiveRetail = before + "." + after;
                                }
                                else if (parse.EffectiveRetailValue.Length == 4)
                                {
                                    before = parse.EffectiveRetailValue.Substring(0, 2);
                                    after = parse.EffectiveRetailValue.Substring(2, 2);
                                    effectiveRetail = before + "." + after;
                                }
                                else if (parse.EffectiveRetailValue.Length == 3)
                                {
                                    before = parse.EffectiveRetailValue.Substring(0, 1);
                                    after = parse.EffectiveRetailValue.Substring(1, 2);
                                    effectiveRetail = before + "." + after;
                                }
                                else if (parse.EffectiveRetailValue.Length == 2)
                                {
                                    after = parse.EffectiveRetailValue.Substring(0, 2);
                                    effectiveRetail = "." + after;
                                }
 
                                string EffectiveTrimmed = effectiveRetail.TrimStart(' ');
 
                                if (String.IsNullOrWhiteSpace(parse.EffectiveMSU))
                                {
                                    field2.SignFieldValue = EffectiveTrimmed;
                                }
                                else
                                {
                                    if (Convert.ToInt32(parse.EffectiveMSU) > 1)
                                    {
                                        field2.SignFieldValue = parse.EffectiveMSU.TrimStart(' ') + "/" + EffectiveTrimmed;
                                    }
                                    else
                                    {
                                        field2.SignFieldValue = EffectiveTrimmed;
                                    }
                                }
 
                                if (EffectiveTrimmed == ".00")
                                {
                                    //do nothing
                                }
                                else if (EffectiveTrimmed == ".0")
                                {
 
                                }
                                else if (String.IsNullOrEmpty(EffectiveTrimmed))
                                {
                                    //do nothing
                                }
                                else
                                {
                                    field2.SignFieldLastUpdate = DateTime.Now;
                                    sign.SignFields.Add(field2);
                                }
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.SecondRetailValue))
                            {
                                SignField field3 = new SignField();
                                field3.FieldID = parse.SecondRetailID;
                                string SecondEffectiveRetail = null;
                                string before = null;
                                string after = null;
                                string value = parse.SecondRetailValue.TrimStart(' ');
 
                                if (parse.SecondRetailValue.Length == 6)
                                {
                                    before = parse.SecondRetailValue.Substring(0, 4);
                                    after = parse.SecondRetailValue.Substring(4, 2);
                                    SecondEffectiveRetail = before + "." + after;
                                }
                                else if (parse.SecondRetailValue.Length == 5)
                                {
                                    before = parse.SecondRetailValue.Substring(0, 3);
                                    after = parse.SecondRetailValue.Substring(3, 2);
                                    SecondEffectiveRetail = before + "." + after;
                                }
                                else if (parse.SecondRetailValue.Length == 4)
                                {
                                    before = parse.SecondRetailValue.Substring(0, 2);
                                    after = parse.SecondRetailValue.Substring(2, 2);
                                    SecondEffectiveRetail = before + "." + after;
                                }
                                else if (parse.SecondRetailValue.Length == 3)
                                {
                                    before = parse.SecondRetailValue.Substring(0, 1);
                                    after = parse.SecondRetailValue.Substring(1, 2);
                                    SecondEffectiveRetail = before + "." + after;
                                }
                                else if (parse.SecondRetailValue.Length == 2)
                                {
                                    after = parse.SecondRetailValue.Substring(0, 2);
                                    SecondEffectiveRetail = "." + after;
                                }
 
                                string secondEffectiveTrimmed = SecondEffectiveRetail.TrimStart(' ');
 
                                if (String.IsNullOrWhiteSpace(parse.SecondEffectiveMSU))
                                {
                                    field3.SignFieldValue = secondEffectiveTrimmed;
                                }
                                else
                                {
                                    if (Convert.ToInt32(parse.SecondEffectiveMSU) > 1)
                                    {
                                        field3.SignFieldValue = parse.SecondEffectiveMSU.TrimStart(' ') + "/" + secondEffectiveTrimmed;
                                    }
                                    else
                                    {
                                        field3.SignFieldValue = secondEffectiveTrimmed;
                                    }
 
                                }
 
                                if (secondEffectiveTrimmed == ".00")
                                {
                                    //do nothing
                                }
                                else if (secondEffectiveTrimmed == ".0")
                                {
 
                                }
                                else if (String.IsNullOrEmpty(secondEffectiveTrimmed))
                                {
                                    //do nothing
                                }
                                else
                                {
                                    field3.SignFieldLastUpdate = DateTime.Now;
                                    sign.SignFields.Add(field3);
                                }
 
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.SizeValue))
                            {
                                SignField field4 = new SignField();
                                field4.FieldID = parse.SizeID;
                                //need to strip down the lb and others
                                string FieldValue = null;
 
                                if (parse.SizeValue.Contains("LB"))
                                    FieldValue = parse.SizeValue.Replace("LB", "");
                                else if (parse.SizeValue.Contains("CT"))
                                    FieldValue = parse.SizeValue.Replace("CT", "");
                                else if (parse.SizeValue.Contains("OZ"))
                                    FieldValue = parse.SizeValue.Replace("OZ", " ");
                                else if (parse.SizeValue.Contains("Z"))
                                    FieldValue = parse.SizeValue.Replace("Z", "");
                                else if (parse.SizeValue.Contains("USE"))
                                    FieldValue = parse.SizeValue.Replace("USE", "");
                                else if (parse.SizeValue.Contains("CARTON"))
                                    FieldValue = parse.SizeValue.Replace("CARTON", "");
                                else if (parse.SizeValue.Contains("INCH"))
                                    FieldValue = parse.SizeValue.Replace("INCH", "");
                                else if (parse.SizeValue.Contains("FT"))
                                    FieldValue = parse.SizeValue.Replace("FT", "");
                                else if (parse.SizeValue.Contains("LT"))
                                    FieldValue = parse.SizeValue.Replace("LT", "");
                                else if (parse.SizeValue.Contains("BULK"))
                                    FieldValue = parse.SizeValue.Replace("BULK", "");
                                else if (parse.SizeValue.Contains("PK"))
                                    FieldValue = parse.SizeValue.Replace("PK", "");
                                else if (parse.SizeValue.Contains("COUNT"))
                                    FieldValue = parse.SizeValue.Replace("COUNT", "");
                                else if (parse.SizeValue.Contains("-"))
                                    FieldValue = parse.SizeValue.Replace("-", "");
                                else if (parse.SizeValue.Contains("GR"))
                                    FieldValue = parse.SizeValue.Replace("GR", "");
                                else if (parse.SizeValue.Contains("YD"))
                                    FieldValue = parse.SizeValue.Replace("YD", "");
                                else if (parse.SizeValue.Contains("PAIR"))
                                    FieldValue = parse.SizeValue.Replace("PAIR", "");
                                else if (parse.SizeValue.Contains("+"))
                                    FieldValue = parse.SizeValue.Replace("+", "");
                                else if (parse.SizeValue.Contains("BH"))
                                    FieldValue = parse.SizeValue.Replace("BH", "");
                                else if (parse.SizeValue.Contains("BR"))
                                    FieldValue = parse.SizeValue.Replace("BR", "");
                                else if (parse.SizeValue.Contains("BU"))
                                    FieldValue = parse.SizeValue.Replace("BU", "");
                                else if (parse.SizeValue.Contains("BX"))
                                    FieldValue = parse.SizeValue.Replace("BX", "");
                                else if (parse.SizeValue.Contains("CF"))
                                    FieldValue = parse.SizeValue.Replace("CF", "");
                                else if (parse.SizeValue.Contains("CM"))
                                    FieldValue = parse.SizeValue.Replace("CM", "");
                                else if (parse.SizeValue.Contains("CN"))
                                    FieldValue = parse.SizeValue.Replace("CN", "");
                                else if (parse.SizeValue.Contains("CR"))
                                    FieldValue = parse.SizeValue.Replace("CR", "");
                                else if (parse.SizeValue.Contains("CS"))
                                    FieldValue = parse.SizeValue.Replace("CS", "");
                                else if (parse.SizeValue.Contains("DZ"))
                                    FieldValue = parse.SizeValue.Replace("DZ", "");
                                else if (parse.SizeValue.Contains("ER"))
                                    FieldValue = parse.SizeValue.Replace("ER", "");
                                else if (parse.SizeValue.Contains("PAIR"))
                                    FieldValue = parse.SizeValue.Replace("PAIR", "");
 
                                int value = 1;
                                if (String.IsNullOrWhiteSpace(FieldValue))
                                    FieldValue = value.ToString();
 
                                field4.SignFieldValue = FieldValue.TrimStart(' ');
                                field4.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field4);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.PackValue))
                            {
                                SignField field5 = new SignField();
                                field5.FieldID = parse.PackID;
                                field5.SignFieldValue = parse.PackValue.TrimStart(' ');
                                field5.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field5);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.ItemDescriptionValue))
                            {
                                SignField field6 = new SignField();
                                field6.FieldID = parse.ItemDescriptionID;
                                field6.SignFieldValue = parse.ItemDescriptionValue.TrimStart(' ');
                                field6.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field6);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.ItemCodeValue))
                            {
                                SignField field7 = new SignField();
                                field7.FieldID = parse.ItemCodeID;
                                field7.SignFieldValue = parse.ItemCodeValue.TrimStart(' '); //make it 6 digits always as opposed to strip leading zeros as LS instructed 10122012, now 10/19/2012
                                field7.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field7);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.MinorCategoryValue))
                            {
                                //need to strip leading zeros and blank spaces
                                SignField field8 = new SignField();
                                field8.FieldID = parse.MinorCategoryID;
                                field8.SignFieldValue = parse.MinorCategoryValue.TrimStart(' ', '0'); //strip leading zeros per MH 9122012
                                field8.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field8);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.OBIFieldValue))
                            {
                                SignField field9 = new SignField();
                                field9.FieldID = parse.OBIId;
                                field9.SignFieldValue = parse.OBIFieldValue.TrimStart(' ');
                                field9.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field9);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.MajorCategoryValue))
                            {
                                SignField field10 = new SignField();
                                field10.FieldID = parse.MajorCategoryID;
                                field10.SignFieldValue = parse.MajorCategoryValue.TrimStart(' ', '0'); //strip leading zeros per MH 9122012
                                field10.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field10);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.VendorValue))
                            {
                                SignField field11 = new SignField();
                                field11.FieldID = parse.VendorID;
                                field11.SignFieldValue = parse.VendorValue.TrimStart(' ', '0'); //strip leading zeros per LS 10082012
                                field11.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field11);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.UsaveValue))
                            {
                                SignField field12 = new SignField();
                                field12.FieldID = parse.UsaveID;
                                field12.SignFieldValue = parse.UsaveValue.TrimStart(' ');
                                field12.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field12);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.PricePerUnitValue))
                            {
                                SignField field13 = new SignField();
                                field13.FieldID = parse.PricePerUnitID;
                                field13.SignFieldValue = parse.PricePerUnitValue.TrimStart(' ');
                                field13.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field13);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.PalletValue))
                            {
                                SignField field14 = new SignField();
                                field14.FieldID = parse.PalletID;
                                field14.SignFieldValue = parse.PalletValue.TrimStart(' ');
                                field14.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field14);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.MasterPackValue))
                            {
                                SignField field15 = new SignField();
                                field15.FieldID = parse.MasterPackId;
                                field15.SignFieldValue = parse.MasterPackValue.TrimStart(' ');
                                field15.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field15);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.TodaysDateValue))
                            {
                                string month = parse.TodaysDateValue.Substring(0, 2);
                                string day = parse.TodaysDateValue.Substring(2, 2);
                                string year = parse.TodaysDateValue.Substring(4, 2);
                                SignField field16 = new SignField();
                                field16.FieldID = parse.TodaysDateID;
                                DateTime date = Convert.ToDateTime(month + "/" + day + "/20" + year);
                                field16.SignFieldValue = month + "/" + day + "/20" + year;
                                field16.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field16);
 
                                sign.SignDateFrom = date;
                                sign.SignDateTo = date.AddDays(6);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.AWGSizeIDValue))
                            {
                                SignField field17 = new SignField();
                                field17.FieldID = parse.AWGSizeID;
 
                                if (parse.AWGSizeIDValue.TrimStart(' ') == "CF")
                                {
                                    field17.SignFieldValue = "5";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "CM")
                                {
                                    field17.SignFieldValue = "6";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "CN")
                                {
                                    field17.SignFieldValue = "7";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "CR")
                                {
                                    field17.SignFieldValue = "8";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "CS")
                                {
                                    field17.SignFieldValue = "9";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "CT")
                                {
                                    field17.SignFieldValue = "10";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "DZ")
                                {
                                    field17.SignFieldValue = "11";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "BX")
                                {
                                    field17.SignFieldValue = "4";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "BU")
                                {
                                    field17.SignFieldValue = "3";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "BR")
                                {
                                    field17.SignFieldValue = "2";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "BH")
                                {
                                    field17.SignFieldValue = "1";
                                }
 
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "EA")
                                {
                                    field17.SignFieldValue = "12";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "ER")
                                {
                                    field17.SignFieldValue = "13";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "FT")
                                {
                                    field17.SignFieldValue = "14";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "GL")
                                {
                                    field17.SignFieldValue = "15";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "GR")
                                {
                                    field17.SignFieldValue = "16";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "HD")
                                {
                                    field17.SignFieldValue = "17";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "IN")
                                {
                                    field17.SignFieldValue = "18";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "KG")
                                {
                                    field17.SignFieldValue = "19";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "LB")
                                {
                                    field17.SignFieldValue = "20";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "LG")
                                {
                                    field17.SignFieldValue = "21";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "LT")
                                {
                                    field17.SignFieldValue = "22";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "ME")
                                {
                                    field17.SignFieldValue = "23";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "MG")
                                {
                                    field17.SignFieldValue = "24";
                                }
                                else if (parse.AWGSizeIDValue.Trim(' ') == "ML")
                                {
                                    field17.SignFieldValue = "25";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "MM")
                                {
                                    field17.SignFieldValue = "26";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "MT")
                                {
                                    field17.SignFieldValue = "27";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "NA")
                                {
                                    field17.SignFieldValue = "28";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "OZ")
                                {
                                    field17.SignFieldValue = "29";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "PC")
                                {
                                    field17.SignFieldValue = "30";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "PK")
                                {
                                    field17.SignFieldValue = "31";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "PR")
                                {
                                    field17.SignFieldValue = "32";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "PT")
                                {
                                    field17.SignFieldValue = "33";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "QT")
                                {
                                    field17.SignFieldValue = "34";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "RL")
                                {
                                    field17.SignFieldValue = "35";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "RW")
                                {
                                    field17.SignFieldValue = "36";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "SH")
                                {
                                    field17.SignFieldValue = "37";
                                }
                                else if (parse.AWGSizeIDValue.Trim(' ') == "SK")
                                {
                                    field17.SignFieldValue = "38";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "SM")
                                {
                                    field17.SignFieldValue = "39";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "ST")
                                {
                                    field17.SignFieldValue = "40";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "SZ")
                                {
                                    field17.SignFieldValue = "41";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "US")
                                {
                                    field17.SignFieldValue = "42";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "YD")
                                {
                                    field17.SignFieldValue = "44";
                                }
                                else if (parse.AWGSizeIDValue.TrimStart(' ') == "WT")
                                {
                                    field17.SignFieldValue = "43";
                                }
 
                                field17.SignFieldLastUpdate = DateTime.Now;
                                if (field17.SignFieldValue != null)
                                {
                                    sign.SignFields.Add(field17);
                                }
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.UPC13Value))
                            {
                                SignField field18 = new SignField();
                                field18.FieldID = parse.UPC13ID;
                                field18.SignFieldValue = parse.UPC13Value.TrimStart(' ');
                                field18.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field18);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.LOQValue))
                            {
                                SignField field19 = new SignField();
                                field19.FieldID = parse.LOQID;
                                field19.SignFieldValue = parse.LOQValue.TrimStart(' ');
                                field19.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field19);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.SaleStartDateValue))
                            {
                                string month = parse.SaleStartDateValue.Substring(0, 2);
                                string day = parse.SaleStartDateValue.Substring(2, 2);
                                string year = parse.SaleStartDateValue.Substring(4, 2);
                                SignField field20 = new SignField();
                                field20.FieldID = parse.SaleStartDateID;
                                field20.SignFieldValue = month + "/" + day + "/20" + year;
                                field20.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field20);
                            }
 
                            if (!String.IsNullOrWhiteSpace(parse.SaleEndDateValue))
                            {
                                string month = parse.SaleEndDateValue.Substring(0, 2);
                                string day = parse.SaleEndDateValue.Substring(2, 2);
                                string year = parse.SaleEndDateValue.Substring(4, 2);
                                SignField field21 = new SignField();
                                field21.FieldID = parse.SaleEndDateID;
                                field21.SignFieldValue = month + "/" + day + "/20" + year;
                                field21.SignFieldLastUpdate = DateTime.Now;
                                sign.SignFields.Add(field21);
                            }
 
                            LevelSign levelSign = new LevelSign();
                            levelSign.LevelID = newLevel.LevelID;
                            levelSign.LevelSignLastUpdated = DateTime.Now;
                            levelSign.LevelSignQuantity = Quantity;
                            levelSign.LevelSignSoftDelete = false;
                            levelSign.StampID = stampID;
                            sign.LevelSigns.Add(levelSign);
                            sign.SignID = -1;
 
                            if (sign.SignDateFrom == null)
                                sign.SignDateFrom = DateTime.Now;
                            if (sign.SignDateTo == null)
                                sign.SignDateTo = DateTime.Now.AddDays(6);
 
                            SignManager.SaveSign(sign, true, true);
                        }
                        else
                        {
                            //do nothing
                        }
 
                    }
                }
            }
        }
    }
}
 

Anything would help, ideas on better processing, ive tried bulk insert but without a lot of luck.
 
thanks!
Posted 15-Apr-13 10:12am
bobb024317
Edited 15-Apr-13 11:55am
v5
Comments
joshrduncan2012 at 15-Apr-13 15:30pm
   
Do you have a specific question regarding this issue?
ThePhantomUpvoter at 15-Apr-13 16:33pm
   
"ideas or help", with what exactly?
ThePhantomUpvoter at 15-Apr-13 16:57pm
   
OMG! You really expect someone to wade through all this code for you and figure it out? Profile your code and fix your bottlenecks. Best I can do since I am not going to debug your code for you.
bobb024 at 15-Apr-13 17:19pm
   
I am not looking for someone to sift through my code, or debug it, i am showing the overall concept of the bottle neck portion so people can get an idea of the structure and help stimulation other ideas. For every potent Sign i make, you can see there are a lot of secondary inserts that need to occur, creating multiple (up to 15 signfields) and one levelsign.
Pheonyx at 15-Apr-13 18:03pm
   
I have a question for you... what format is the file in? Can you read it into a dataset before your process it?
If so, why dont you use order markers so that they go into the database in any order, but the markers dictate what the original order was?
Have a simple table in the database called "OrderKey" which has a single field in it...
This field should be a "Bigint"...
 
You grab say the first 10 entries to be posted, and grab the next 10 numbers from the OrderKey.. pass that to the posting routine on a seperate thread... repeat the process for all batches of entries to be posted. So when you post your entry it has the correct order key added to it?
 
Then you can batch it and use threading yet still ensure that the original order can be recalled?
bobb024 at 15-Apr-13 20:22pm
   
The file format is in DTA, but I end up replacing it and saving it as a .txt. Now I am going to add some breaks to the long if else, and transition them to case statements probably. Also, i am going to try to do the read line within the using statement for the streamreader so i can just read line by line. Any additional help would be great!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,
 
I think I like where this is going. Your speed will be down to the inserts into SQL as we all probably know. One way to really improve performance is bulk inserts but you mention that you have tried this before but no luck.
 
You have not mentioned what .Net framework version you are using. I would assume (hope) you are using .net 4 +
 
Have you considered the following scenario?
 
1) Using some of your method in reading the files to be inserted maybe you can break this down into insert chunks. i.e. you mentioned that you could have 3 inserts per potent object. If so create a ConcurrentBag (see ConcurrentBag[^], basically a thread safe list) of these objects to be inserted in one go. Once you have this bag / collection item (and if order is needed then use a ConcurrentStack) you can then add these to a BlockingCollection.
 
2) Using a BlockingCollection (BlockingCollection[^]) you can then enumerate over each object in parallel as this blocking collection is threadsafe. You can do something like:
 
Parallel.ForEach(_bc.GetConsumingEnumerable(), item =>
{
    … // process item here. i.e. insert into database
});
 
By doing this you can operate on your data chunks in parallel which would increase performance.
 
BIG NOTE!
 
By doing the above on its own will cause some problems with concurrency which is not great. However, if you use the Parallel extensions from Microsoft this problem is less painful.
 
Check out : http://blogs.msdn.com/b/pfxteam/archive/2010/04/06/9990420.aspx[^]
 
Once you have the GetConsumingPartitioner in place you can really add performance gains.
 
Secondly, I personally love TPL. Get into that instead of spawning new threads. Sasha Barber did a great article on TPL Task Parallel Library: 1 of n[^]. By doing your chunk collection generation in parallel or in task collections you can process things very quickly.
 
Lastly, there will be problems and strange exceptions that are thrown when issues are encountered and managing exceptions can be tricky however with a bit of patients, love and care, you should be able to generate something quite fantastic.
 
Let me know how you get on!
  Permalink  
Comments
bobb024 at 15-Apr-13 20:28pm
   
thank you for the AMAZING feedback, makes you feel good after a day like today in Boston. Much appreciated and I will start looking into this ASAP
db7uk at 16-Apr-13 17:14pm
   
Yes I saw it on the news. Hope you are getting on ok!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi me again.
 
I am interested to find out what went wrong with bulk inserts.
 
Have you also looked at:
 
1) Generate your chunk files as explained in solution 1. Instead of inserting into a database directly consider writing the file(s) to a folder/directory.
2) Using SSIS to read the file(s) use the very powerful processing power of SSIS to bulk insert the files by looping over them and processing them asynchronously.
 
thanks
  Permalink  
Comments
bobb024 at 15-Apr-13 20:27pm
   
I will be sure to look into this ASAP

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 15 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100