Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Reading and Parsing >2GB FixedWidth Text Files with the StreamReader in a WebForm

Posted on 2008-10-03
12
435 Views
Last Modified: 2013-11-08
I've developed a parser in C# in a Webform. This is to parse and load >2GB Multiple RecordType FixedWidth Text Files into SQL Server 2005/2008. There are 6 basic record types 1-6. RecordType 1 is the parent and 2-6 are the child records. RecordType1 can have many or none of each of the child record type. My parser reads the text file and imports them into the appropiate tables with a PK composed of three (3) fields in RecordType1. This process works fine with small files (up to 20MB) once I start processing large files the the page just times out. I am using a StreamReader. I started with the Readline method and that just times out saying Internet Explorer cannot display this page. I've tried the ReadToEnd method and I get a SystemOutofMemory Exception. The read method just times out also. Does any one have any suggestions? I am attaching my code and a small sample of the text file. I am processing this  on a Dell PowerEdge 2850 with Dual Xeons and 11GB of ram. Any and all help will be appreciated.
//MultipleRecord FixedWidth Parser code in CodeBehing Page
 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
 
public partial class MainPage : System.Web.UI.Page
{
 
    string PKIdentity = "";//Added RW
    string RecordType = "";
    string CountyCode = "";
    string CountyName = "";
    string CaseYear = "";
    string CaseType = "";
    string CaseSequence = "";
    string DefendantName = "";
    string DefendantAddress1 = "";
    string DefendantAddress2 = "";
    string DefendantCity = "";
    string DefendantState = "";
    string DefendantZip = "";
    string RaceCode = "";
    string SexCode = "";
    string DefendantDOB = "";
    string Filler = "";
    string SSNLast4Digits = "";
    string DefendantDriversLicNo = "";
    string DefendantDriversLicState = "";
    string CitationNumber = "";
    string TrialDate = "";
    string DistrictCourtCaseProccess = "";
    string SuperiorCourtCaseProcess = "";
    string DefendantStateIdentifier = "";
    string DefendantInJailIndicator = "";
    string DateCaseServed = "";
    string CheckDigitNumber = "";
    string LID = "";
    string ORI = "";
    string CaseOfficeLocationCode = "";
    string DomesticViolenceCharge = "";
    string VictimsRightAct = "";
    
    string DefendantAliasName = "";
    
    string OffenseSequenceNo = "";
    string ChargedOffenseCode = "";
    string ChargedOffenseType = "";
    string ChargedOffenseDescription = "";
    string ChargedOffenseStatuteNo = "";
    string ArraignedOffenseCode = "";
    string ArraignedOffenseType = "";
    string ArraignedOffenseDescription = "";
    string ArraignedOffenseStatuteNo = "";
    string ConvictedOffenseCode = "";
    string ConvictedOffenseType = "";
    string ConvictedOffenseDescription = "";
    string ConvictedOffenseStatuteNo = "";
    string CalledAndFailedDate = "";
    string FailureToAppearDate = "";
    string OrderForArrestDate = "";
    string ConvictedOffenseClass = "";
    string PleaCode = "";
    string VerdictCode = "";
    string MethodofDispositionCode = "";
    string OffenseDispositionDate = "";
    string FineAmount = "";
    string CourtCost = "";
    string RestitutionAmount = "";
    string MoniesPaidIndicator = "";
    string MoniesToBePaidDate = "";
    string NonMVFailureToComplyDate = "";
    string ShowCauseOrderDate = "";
    string ProbationViolationDate = "";
    string MVFailureToComplyDate = "";
    string SpecialCondition = "";
    string ChargedOffenseDate = "";
    string BloodAlcoholGreaterThan_16 = "";
    string PriorPoints = "";
    string JudgeInitials = "";
    string ADAInitials = "";
    string DomesticViolenceConvictedFlag = "";
 
    string ComplaintantWitnessName = "";
    string AgencyCode = "";
    
    string JudgementOffenseSequenceNo = "";
    string ConsolidatedForJudgementFileNo = "";
    string ConsolidatedForJudgementOffenseNo = "";
    string SentenceLengthMin = "";
    string SentenceLengthMinFrame = "";
    string SentenceLengthMax = "";
    string SentenceLengthMaxFrame = "";
    string SentenceType = "";
    string ProbationLength = "";
    string ProbationLengthFrame = "";
    string SupervisedProbationFlag = "";
    string CreditForTimeServed = "";
    string ConsecutiveSentencingIndicator = "";
    string CommunityServiceLength = "";
    string CommunityServiceToBeCompleted = "";
    string Assesment = "";
    string CommunityServiceLicenseRevocation = "";
    string CommunityServiceLicenseRevocationCompliance = "";
    string EnhancedFirearmPenalty = "";
    string SentencingServicePlan = "";
    string SpecialProbationLength = "";
    string SpecialProbationActiveTermFrame = "";
    string CustodyOf = "";
    string ImpactProgram = "";
    string ProgramName = "";
    string ResidentialProgramLength = "";
    string ResidentialProgramLengthFrame = "";
    string ElectronicMonitoringHouseArrest = "";
    string ElectronicMonitoringHouseArrestFrame = "";
    string IntensiveProbationSupervision = "";
    string DayReportingCenter = "";
    string DayReportingCenterFrame = "";
    string SpecialConditionsSexOffender = "";
    string DrugTreatmentCourt = "";
    
    string SpecialConditionsOffenseSequenceNo = "";
    string SpecialCondition2 = "";
    string SpecialCondition3 = "";
    string SpecialCondition4 = "";
    
    string CountOfTotalRecords = "";
    string CountOfDeleteRecords = "";
    string CountOfCaseRecords = "";
    string CountOfAliasRecords = "";
    string CountOfOffenseRecords = "";
    string CountOfWitnessRecords = "";
    string CountOfJudgementRecords = "";
    string CountOfSpecialConditionsRecords = "";
 
 
    public SQLCode GetSQLCode
    {
        get { return new SQLCode(); }
    }
 
    public Logger GetLogger
    {
        get { return new Logger(); }
    }
 
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string myFile = "";
        string path = AppDomain.CurrentDomain.BaseDirectory + "\\Data\\Crimn0001.txt";//Changed RW
        string newpath = AppDomain.CurrentDomain.BaseDirectory + "\\Data\\CriminalOutput.txt";//Changed W
        string fileName = @path;
        string newFileName = newpath;
        bool ColumnHeader = false;
 
        StreamReader srRead = File.OpenText(fileName);
 
        srRead.BaseStream.Seek(0, SeekOrigin.Current);
 
 
        while (srRead.Peek() > -1)
        {
 
            //myFile = myFile + srRead.ReadLine() + "\n";
            myFile = myFile + srRead.Read() + "\n";
 
 
        }
 
 
        srRead.Close();
 
        //Split the file into an array using a newline character as the split character
 
        char[] splitChar = { '\n' };
 
        string[] fileArray;
 
        fileArray = myFile.Split(splitChar);
 
 
        //Clear the old file contents 
 
        myFile = "";
 
 
        //iterate over the Array which contains each line of the file 
 
        for (int x = 0; x < fileArray.Length; x++)
        {
            //Record Type 00 Case Delete
            if (fileArray[x].StartsWith("00"))
            {
                string RecordType = "";
                string CountyCode = "";
                string CountyName = "";
                string CaseYear = "";
                string CaseType = "";
                string CaseSequence = "";
 
                RecordType = fileArray[x].Substring(0, 2);
                CountyCode = fileArray[x].Substring(2, 3);
                CountyName = fileArray[x].Substring(5, 12);
                CaseYear = fileArray[x].Substring(17, 4);
                CaseType = fileArray[x].Substring(21, 3);
                CaseSequence = fileArray[x].Substring(24, 6);
                PKIdentity = CountyCode + CaseYear + CaseSequence;//Added RW
 
                //Code to insert into SQL here
                GetSQLCode.Insert_CaseDelete00_Table(PKIdentity, RecordType, CountyCode, CountyName, CaseYear, CaseType, CaseSequence);
                GetSQLCode.Delete_AliasData02_Record(PKIdentity);
                GetSQLCode.Delete_OffenseData03_Record(PKIdentity);
                GetSQLCode.Delete_WitnessData04_Record(PKIdentity);
                GetSQLCode.Delete_JudgementData05_Record(PKIdentity);
                GetSQLCode.Delete_SpecialConditionData06_Record(PKIdentity);
                GetSQLCode.Delete_CaseData01_Record(PKIdentity);
            }
 
            // Record Type 01 CaseData
            if (fileArray[x].StartsWith("01"))
            {
 
                //Reinitialize all variables
 
                PKIdentity = "";//Added RW
                RecordType = "";
                CountyCode = "";
                CountyName = "";
                CaseYear = "";
                CaseType = "";
                CaseSequence = "";
                DefendantName = "";
                DefendantAddress1 = "";
                DefendantAddress2 = "";
                DefendantCity = "";
                DefendantState = "";
                DefendantZip = "";
                RaceCode = "";
                SexCode = "";
                DefendantDOB = "";
                Filler = "";
                SSNLast4Digits = "";
                DefendantDriversLicNo = "";
                DefendantDriversLicState = "";
                CitationNumber = "";
                TrialDate = "";
                DistrictCourtCaseProccess = "";
                SuperiorCourtCaseProcess = "";
                DefendantStateIdentifier = "";
                DefendantInJailIndicator = "";
                DateCaseServed = "";
                CheckDigitNumber = "";
                LID = "";
                ORI = "";
                CaseOfficeLocationCode = "";
                DomesticViolenceCharge = "";
                VictimsRightAct = "";
 
                RecordType = fileArray[x].Substring(0, 2);
                CountyCode = fileArray[x].Substring(2, 3);
                CountyName = fileArray[x].Substring(5, 12);
                CaseYear = fileArray[x].Substring(17, 4);
                CaseType = fileArray[x].Substring(21, 3);
                CaseSequence = fileArray[x].Substring(24, 6);
                DefendantName = fileArray[x].Substring(30, 28);
                DefendantAddress1 = fileArray[x].Substring(58, 20);
                DefendantAddress2 = fileArray[x].Substring(78, 15);
                DefendantCity = fileArray[x].Substring(93, 15);
                DefendantState = fileArray[x].Substring(108, 2);
                DefendantZip = fileArray[x].Substring(110, 2);
                RaceCode = fileArray[x].Substring(119, 1);
                SexCode = fileArray[x].Substring(120, 1);
                DefendantDOB = fileArray[x].Substring(121, 8);
                Filler = fileArray[x].Substring(129, 5);
                SSNLast4Digits = fileArray[x].Substring(134, 4);
                DefendantDriversLicNo = fileArray[x].Substring(138, 25);
                DefendantDriversLicState = fileArray[x].Substring(163, 2);
                CitationNumber = fileArray[x].Substring(165, 8);
                TrialDate = fileArray[x].Substring(173, 8);
                DistrictCourtCaseProccess = fileArray[x].Substring(181, 1);
                SuperiorCourtCaseProcess = fileArray[x].Substring(182, 1);
                DefendantStateIdentifier = fileArray[x].Substring(183, 10);
                DefendantInJailIndicator = fileArray[x].Substring(193, 1);
                DateCaseServed = fileArray[x].Substring(194, 8);
                CheckDigitNumber = fileArray[x].Substring(202, 7);
                LID = fileArray[x].Substring(209, 15);
                ORI = fileArray[x].Substring(224, 9);
                CaseOfficeLocationCode = fileArray[x].Substring(233, 2);
                DomesticViolenceCharge = fileArray[x].Substring(235, 1);
                VictimsRightAct = fileArray[x].Substring(236, 1);
                PKIdentity = CountyCode + CaseYear + CaseSequence;//Added RW
 
                //Code to insert into SQL here
 
                //Insert Parent Record to AOCTest1_Table 
 
                GetSQLCode.Insert_CaseData01_Table(PKIdentity, RecordType, CountyCode, CountyName, CaseYear,
                                                 CaseType,CaseSequence,DefendantName,DefendantAddress1,DefendantAddress2,
                                                 DefendantCity,DefendantState,DefendantZip,RaceCode,SexCode,DefendantDOB,
                                                 Filler,SSNLast4Digits,DefendantDriversLicNo,DefendantDriversLicState,
                                                 CitationNumber,TrialDate,DistrictCourtCaseProccess,SuperiorCourtCaseProcess,
                                                 DefendantStateIdentifier,DefendantInJailIndicator,DateCaseServed,CheckDigitNumber,
                                                 LID,ORI,CaseOfficeLocationCode,DomesticViolenceCharge,VictimsRightAct);
 
            }
 
            //RecordType 02 Alias Data
            if (fileArray[x].StartsWith("02"))
            {
 
                //Reinitialize variables
                RecordType = "";
                DefendantAliasName = "";
 
                RecordType = fileArray[x].Substring(0, 2);
                DefendantAliasName = fileArray[x].Substring(2, 28);
 
                //Code to insert into SQL here
                GetSQLCode.Insert_AliasData02_Table(PKIdentity, RecordType, DefendantAliasName);
            }
 
            //RecordType 03 Offense Data
            if (fileArray[x].StartsWith("03"))
            {
 
                //Reinitialize all variables
 
                RecordType = "";
                OffenseSequenceNo = "";
                ChargedOffenseCode = "";
                ChargedOffenseType = "";
                ChargedOffenseDescription = "";
                ChargedOffenseStatuteNo = "";
                ArraignedOffenseCode = "";
                ArraignedOffenseType = "";
                ArraignedOffenseDescription = "";
                ArraignedOffenseStatuteNo = "";
                ConvictedOffenseCode = "";
                ConvictedOffenseType = "";
                ConvictedOffenseDescription = "";
                ConvictedOffenseStatuteNo = "";
                CalledAndFailedDate = "";
                FailureToAppearDate = "";
                OrderForArrestDate = "";
                ConvictedOffenseClass = "";
                PleaCode = "";
                VerdictCode = "";
                MethodofDispositionCode = "";
                OffenseDispositionDate = "";
                FineAmount = "";
                CourtCost = "";
                RestitutionAmount = "";
                MoniesPaidIndicator = "";
                MoniesToBePaidDate = "";
                NonMVFailureToComplyDate = "";
                ShowCauseOrderDate = "";
                ProbationViolationDate = "";
                MVFailureToComplyDate = "";
                SpecialCondition = "";
                ChargedOffenseDate = "";
                BloodAlcoholGreaterThan_16 = "";
                PriorPoints = "";
                JudgeInitials = "";
                ADAInitials = "";
                DomesticViolenceConvictedFlag = "";
 
                RecordType = fileArray[x].Substring(0, 2);
                OffenseSequenceNo = fileArray[x].Substring(2, 2);
                ChargedOffenseCode = fileArray[x].Substring(4, 4);
                ChargedOffenseType = fileArray[x].Substring(8, 1);
                ChargedOffenseDescription = fileArray[x].Substring(9, 45);
                ChargedOffenseStatuteNo = fileArray[x].Substring(54, 15);
                ArraignedOffenseCode = fileArray[x].Substring(69, 4);
                ArraignedOffenseType = fileArray[x].Substring(73, 1);
                ArraignedOffenseDescription = fileArray[x].Substring(74, 45);
                ArraignedOffenseStatuteNo = fileArray[x].Substring(119, 15);
                ConvictedOffenseCode = fileArray[x].Substring(134, 4);
                ConvictedOffenseType = fileArray[x].Substring(138, 1);
                ConvictedOffenseDescription = fileArray[x].Substring(139, 45);
                ConvictedOffenseStatuteNo = fileArray[x].Substring(184, 15);
                CalledAndFailedDate = fileArray[x].Substring(199, 8);
                FailureToAppearDate = fileArray[x].Substring(207, 8);
                OrderForArrestDate = fileArray[x].Substring(215, 8);
                ConvictedOffenseClass = fileArray[x].Substring(223, 2);
                PleaCode = fileArray[x].Substring(225, 2);
                VerdictCode = fileArray[x].Substring(227, 2);
                MethodofDispositionCode = fileArray[x].Substring(229, 2);
                OffenseDispositionDate = fileArray[x].Substring(231, 8);
                FineAmount = fileArray[x].Substring(239, 5);
                CourtCost = fileArray[x].Substring(244, 7);
                RestitutionAmount = fileArray[x].Substring(251, 9);
                MoniesPaidIndicator = fileArray[x].Substring(260, 1);
                MoniesToBePaidDate = fileArray[x].Substring(261, 8);
                NonMVFailureToComplyDate = fileArray[x].Substring(269, 8);
                ShowCauseOrderDate = fileArray[x].Substring(277, 8);
                ProbationViolationDate = fileArray[x].Substring(285, 8);
                MVFailureToComplyDate = fileArray[x].Substring(293, 8);
                SpecialCondition = fileArray[x].Substring(301, 59);
                ChargedOffenseDate = fileArray[x].Substring(360, 8);
                BloodAlcoholGreaterThan_16 = fileArray[x].Substring(368, 1);
                PriorPoints = fileArray[x].Substring(369, 2);
                JudgeInitials = fileArray[x].Substring(371, 3);
                ADAInitials = fileArray[x].Substring(374, 3);
                DomesticViolenceConvictedFlag = fileArray[x].Substring(375, 1);
                
 
                //Code to insert into SQL here
                GetSQLCode.Insert_OffenseData03_Table(PKIdentity, RecordType, OffenseSequenceNo, ChargedOffenseCode, ChargedOffenseType,
                                                     ChargedOffenseDescription, ChargedOffenseStatuteNo, ArraignedOffenseCode,
                                                     ArraignedOffenseType, ArraignedOffenseDescription, ArraignedOffenseStatuteNo,
                                                     ConvictedOffenseCode, ConvictedOffenseType, ConvictedOffenseDescription,
                                                     ConvictedOffenseStatuteNo, CalledAndFailedDate, FailureToAppearDate,
                                                     OrderForArrestDate, ConvictedOffenseClass, PleaCode,
                                                     VerdictCode, MethodofDispositionCode, OffenseDispositionDate, FineAmount,
                                                     CourtCost, RestitutionAmount, MoniesPaidIndicator, MoniesToBePaidDate,
                                                     NonMVFailureToComplyDate, ShowCauseOrderDate, ProbationViolationDate, MVFailureToComplyDate,
                                                     SpecialCondition, ChargedOffenseDate, BloodAlcoholGreaterThan_16, PriorPoints,
                                                     JudgeInitials, ADAInitials, DomesticViolenceConvictedFlag);
 
            }
 
            //RecordType 04 Witness Data
            if (fileArray[x].StartsWith("04"))
            {
 
                //Reintialize variables
 
                RecordType = "";
                ComplaintantWitnessName = "";
                AgencyCode = "";
 
                RecordType = fileArray[x].Substring(0, 2);
                ComplaintantWitnessName = fileArray[x].Substring(2, 28);
                AgencyCode = fileArray[x].Substring(30, 3);
 
 
                //Code to insert into SQL here
                GetSQLCode.Insert_WitnessData04_Table(PKIdentity, RecordType,ComplaintantWitnessName,AgencyCode);
 
            }
 
            //RecordType 05 Judgement Data
            if (fileArray[x].StartsWith("05"))
            {
 
                //Reinitialize variables
 
                RecordType = "";
                JudgementOffenseSequenceNo = "";
                ConsolidatedForJudgementFileNo = "";
                ConsolidatedForJudgementOffenseNo = "";
                SentenceLengthMin = "";
                SentenceLengthMinFrame = "";
                SentenceLengthMax = "";
                SentenceLengthMaxFrame = "";
                SentenceType = "";
                ProbationLength = "";
                ProbationLengthFrame = "";
                SupervisedProbationFlag = "";
                CreditForTimeServed = "";
                ConsecutiveSentencingIndicator = "";
                CommunityServiceLength = "";
                CommunityServiceToBeCompleted = "";
                Assesment = "";
                CommunityServiceLicenseRevocation = "";
                CommunityServiceLicenseRevocationCompliance = "";
                EnhancedFirearmPenalty = "";
                SentencingServicePlan = "";
                SpecialProbationLength = "";
                SpecialProbationActiveTermFrame = "";
                CustodyOf = "";
                ImpactProgram = "";
                ProgramName = "";
                ResidentialProgramLength = "";
                ResidentialProgramLengthFrame = "";
                ElectronicMonitoringHouseArrest = "";
                ElectronicMonitoringHouseArrestFrame = "";
                IntensiveProbationSupervision = "";
                DayReportingCenter = "";
                DayReportingCenterFrame = "";
                SpecialConditionsSexOffender = "";
                DrugTreatmentCourt = "";
 
                RecordType = fileArray[x].Substring(0, 2);
                JudgementOffenseSequenceNo = fileArray[x].Substring(2, 2);
                ConsolidatedForJudgementFileNo = fileArray[x].Substring(4, 13);
                ConsolidatedForJudgementOffenseNo = fileArray[x].Substring(17, 2);
                SentenceLengthMin = fileArray[x].Substring(19, 3);
                SentenceLengthMinFrame = fileArray[x].Substring(22, 1);
                SentenceLengthMax = fileArray[x].Substring(23, 3);
                SentenceLengthMaxFrame = fileArray[x].Substring(26, 1);
                SentenceType = fileArray[x].Substring(27, 1);
                ProbationLength = fileArray[x].Substring(28, 3);
                ProbationLengthFrame = fileArray[x].Substring(31, 1);
                SupervisedProbationFlag = fileArray[x].Substring(32, 1);
                CreditForTimeServed = fileArray[x].Substring(33, 4);
                ConsecutiveSentencingIndicator = fileArray[x].Substring(37, 1);
                CommunityServiceLength = fileArray[x].Substring(38, 4);
                CommunityServiceToBeCompleted = fileArray[x].Substring(42, 3);
                Assesment = fileArray[x].Substring(45, 1);
                CommunityServiceLicenseRevocation = fileArray[x].Substring(46, 8);
                CommunityServiceLicenseRevocationCompliance = fileArray[x].Substring(54, 8);
                EnhancedFirearmPenalty = fileArray[x].Substring(62, 1);
                SentencingServicePlan = fileArray[x].Substring(63, 1);
                SpecialProbationLength = fileArray[x].Substring(64, 4);
                SpecialProbationActiveTermFrame = fileArray[x].Substring(68, 1);
                CustodyOf = fileArray[x].Substring(69, 1);
                ImpactProgram = fileArray[x].Substring(70, 1);
                ProgramName = fileArray[x].Substring(71, 50);
                ResidentialProgramLength = fileArray[x].Substring(121, 4);
                ResidentialProgramLengthFrame = fileArray[x].Substring(125, 1);
                ElectronicMonitoringHouseArrest = fileArray[x].Substring(126, 4);
                ElectronicMonitoringHouseArrestFrame = fileArray[x].Substring(130, 1);
                IntensiveProbationSupervision = fileArray[x].Substring(131, 4);
                DayReportingCenter = fileArray[x].Substring(135, 4);
                DayReportingCenterFrame = fileArray[x].Substring(139, 1);
                SpecialConditionsSexOffender = fileArray[x].Substring(140, 1);
                DrugTreatmentCourt = fileArray[x].Substring(141, 1);
 
                //Code to insert into SQL here
                GetSQLCode.Insert_JudgementData05_Table(PKIdentity, RecordType, OffenseSequenceNo, ConsolidatedForJudgementFileNo,
                                                     ConsolidatedForJudgementOffenseNo, SentenceLengthMin, SentenceLengthMinFrame, SentenceLengthMax,
                                                     SentenceLengthMaxFrame, SentenceType, ProbationLength, ProbationLengthFrame, SupervisedProbationFlag,
                                                     CreditForTimeServed, ConsecutiveSentencingIndicator, CommunityServiceLength, CommunityServiceToBeCompleted,
                                                     Assesment, CommunityServiceLicenseRevocation, CommunityServiceLicenseRevocationCompliance, EnhancedFirearmPenalty,
                                                     SentencingServicePlan, SpecialProbationLength, SpecialProbationActiveTermFrame, CustodyOf, ImpactProgram, ProgramName,
                                                     ResidentialProgramLength, ResidentialProgramLengthFrame, ElectronicMonitoringHouseArrest, ElectronicMonitoringHouseArrestFrame,
                                                     IntensiveProbationSupervision, DayReportingCenter, DayReportingCenterFrame, SpecialConditionsSexOffender, DrugTreatmentCourt);
 
            }
 
            //RecordType 06 Special Conditions Data
            if (fileArray[x].StartsWith("06"))
            {                
 
                RecordType = fileArray[x].Substring(0, 2);
                SpecialConditionsOffenseSequenceNo = fileArray[x].Substring(2, 2);
                SpecialCondition2 = fileArray[x].Substring(4, 59);
                SpecialCondition3 = fileArray[x].Substring(63, 59);
                SpecialCondition4 = fileArray[x].Substring(122, 59);
 
                //Code to insert into SQL here
                GetSQLCode.Insert_SpecialConditionData06_Table(PKIdentity, RecordType, OffenseSequenceNo,
                    SpecialCondition2, SpecialCondition3, SpecialCondition4);
 
            }
 
            if (fileArray[x].StartsWith("99"))
            {
                RecordType = "";
                CountOfTotalRecords = "";
                CountOfDeleteRecords = "";
                CountOfCaseRecords = "";
                CountOfAliasRecords = "";
                CountOfOffenseRecords = "";
                CountOfWitnessRecords = "";
                CountOfJudgementRecords = "";
                CountOfSpecialConditionsRecords = "";
 
 
                RecordType = fileArray[x].Substring(0, 2);
                CountOfTotalRecords = fileArray[x].Substring(2, 13);
                CountOfDeleteRecords = fileArray[x].Substring(15, 13);
                CountOfCaseRecords = fileArray[x].Substring(28, 13);
                CountOfAliasRecords = fileArray[x].Substring(41, 13);
                CountOfOffenseRecords = fileArray[x].Substring(54, 13);
                CountOfWitnessRecords = fileArray[x].Substring(67, 13);
                CountOfJudgementRecords = fileArray[x].Substring(80, 13);
                CountOfSpecialConditionsRecords = fileArray[x].Substring(93, 13);
 
                //Code to insert into SQL here
 
            }
 
 
 
 
            myFile = myFile + fileArray[x].ToString() + "\n";
        }
 
        GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Inserted new data to AOC tables",""); 
 
      
    }
}
 
 
 
//SQL Code to insert records
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
/// <summary>
/// Summary description for SQLCode
/// </summary>
public class SQLCode
{
	public SQLCode()
	{
		//
		// TODO: Add constructor logic here
		//
	}
 
    public Logger GetLogger
    {
        get { return new Logger(); }
    }
 
    //Insert routine for CaseDelete00 data
    public bool Insert_CaseDelete00_Table(string PKIdentity, string RecordType, string CountyCode,string CountyName,string CaseYear,string CaseType, string CaseSequence)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();            
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert CaseDelete00(PKIdentity,RecordType,CountyCode,CountyName,CaseYear,CaseType,CaseSequence)" +
                               "Values('" + PKIdentity + "','" + RecordType + "','" + CountyCode + "','" + CountyName + "'," + 
                               "'" + CaseYear + "','" + CaseType + "','" + CaseSequence + "')";
 
            cmd.Connection = sqlConnection;
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
 
    //Insert Routine for CaseData01
    public bool Insert_CaseData01_Table(string PKIdentity, string RecordType, string CountyCode, string CountyName,
                                      string CaseYear, string CaseType, string CaseSequence, string DefendantName,
                                      string DefendantAddress1,string DefendantAddress2, string DefendantCity, 
                                      string DefendantState,string DefendantZipCode, string RaceCode, string SexCode,
                                      string DefendantDOB, string Filler, string SSN,string DefendantsDriversLicNo,
                                      string DefendantDriverLicState, string CitationNumber, string TrialDate,
                                      string DistrictCourtCaseProcessType,string SuperiorCourtCaseProcessType,
                                      string DefendantStateIdentifier, string DefendantInJailIndicator,
                                      string DateCaseServedonDefendant,string CheckDigitNumber, string LIDNo, string ORINo,
                                      string CaseOfficeLocation, string DomesticViolenceFlag, string VictimsRightsActFlag)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
            RecordType = RecordType.Trim();           
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert CaseData01(PKIdentity,RecordType,CountyCode,CountyName,CaseYear,CaseType,CaseSequence,DefendantName,DefendantAddress1," +
                                               "DefendantAddress2,DefendantCity,DefendantState,DefendantZipCode,RaceCode,SexCode,DefendantDOB,Filler,SSN," +
                                               "DefendantsDriversLicNo,DefendantDriverLicState,CitationNumber,TrialDate,DistrictCourtCaseProcessType," +
                                               "SuperiorCourtCaseProcessType,DefendantStateIdentifier,DefendantInJailIndicator,DateCaseServedonDefendant," +
                                               "CheckDigitNumber,LIDNo,ORINo,CaseOfficeLocation,DomesticViolenceFlag,VictimsRightsActFlag)" +
                                               "Values('" + PKIdentity + "', '" + RecordType + "', '" + CountyCode + "', '" + CountyName + "', + '" + CaseYear + "'," +
                                               "'" +  CaseType + "', '" +  CaseSequence + "', '" +  DefendantName + "', '" +  DefendantAddress1 + "'," + 
                                               "'" +  DefendantAddress2 + "', '" +  DefendantCity + "', '" +  DefendantState + "', '" +  DefendantZipCode + "'," +
                                               "'" +  RaceCode + "', '" +  SexCode + "', '" +  DefendantDOB + "', '" +  Filler + "', '" +  SSN + "'," + 
                                               "'" +  DefendantsDriversLicNo + "', '" +  DefendantDriverLicState + "', '" +  CitationNumber + "', '" +  TrialDate + "'," +
                                               "'" +  DistrictCourtCaseProcessType + "','" +  SuperiorCourtCaseProcessType + "', '" +  DefendantStateIdentifier + "', " +
                                               "'" +  DefendantInJailIndicator + "', '" +  DateCaseServedonDefendant + "', '" +  CheckDigitNumber + "', '" +  LIDNo + "'," +
                                               "'" +  ORINo + "', '" +  CaseOfficeLocation + "', '" +  DomesticViolenceFlag + "', '" +  VictimsRightsActFlag + "')";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
 
    //Insert Routine for AliasData02
    public bool Insert_AliasData02_Table(string PKIdentity, string RecordType, string DefendantAliasName)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
            RecordType = RecordType.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert AliasData02(PKIdentity,RecordType,DefendantAliasName) Values('" + PKIdentity + "','" + RecordType + "'," +
                               "'" + DefendantAliasName + "')";
 
            cmd.Connection = sqlConnection;
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
 
    //Insert Routine for OffenseData03
    public bool Insert_OffenseData03_Table(string PKIdentity, string RecordType, string OffenseSequence, string ChargedOffenseCode, string ChargedOffenseType,
                                          string ChargedOffenseDescription,string ChargedOffenseStatuteNo, string ArraignedOffenseCode,
                                          string ArraignedOffenseType,string ArraignedOffenseDescription, string ArraignedOffenseStatuteNumber,
                                          string ConvictedOffenseCode, string ConvictedOffenseType, string ConvictedOffenseDescription,
                                          string ConvictedOffenseStatuteNo, string CalledandFailedDate, string FailuretoAppearDate,
                                          string OrderforArrestDate, string ConvictedOffenseClass, string PleaCode,
                                          string VerdictCode, string MethodofDispositionCode, string OffenseDispositionDate, string FineAmount,
                                          string CourtCost, string RestitutionAmount, string MoniesPaidIndicator,string MoniestobePaidDate,
                                          string NonMVFailtoComplyDate,string ShowCauseOrderDate, string ProbationViolationDate, string MVFailuretoComplyDate,
                                          string SpecialCondition,string ChargedOffenseDate, string BloodAlcoholGreaterThan_16, string PriorPoints,
                                          string JudgeInitials,string ADAInitials, string DomesticViolednceConvictedFlag)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
            RecordType = RecordType.Trim();
           
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert OffenseData03(PKIdentity,RecordType,OffenseSequenceNo,ChargedOffenseCode,ChargedOffenseType,ChargedOffenseDescription,ChargedOffenseStatuteNo,ArraignedOffenseCode,ArraignedOffenseType," +
                                               "ArraignedOffenseDescription,ConvictedOffenseStatuteNo,ConvictedOffenseCode,ConvictedOffenseType,ConvictedOffenseDescription," +
                                               "CalledandFailedDate,FailuretoAppearDate,OrderforArrestDate,ConvictedOffenseClass,PleaCode," +
                                               "VerdictCode,MethodofDispositionCode,OffenseDispositionDate,FineAmt," +
                                               "CourtCost,RestitutionAmt,MoniesPaidIndicator,MoniestobePaidDate,NonMVFailtoComplyDate,ShowCauseOrderDate," +
                                               "ProbationViolationDate,MVFailuretoComplyDate,SpecialCondition,ChargedOffenseDate,BloodAlcoholGreaterThan_16," +
                                               "PriorPoints,JudgeInitials,ADAInitials,DomesticViolenceConvictedFlag)" +
                                               "Values('" + PKIdentity + "','" + RecordType + "', '" + OffenseSequence + "', '" + ChargedOffenseCode + "', '" + ChargedOffenseType + "','" + ChargedOffenseDescription + "', '" + ChargedOffenseStatuteNo + "', '" + ArraignedOffenseCode + "','" + ArraignedOffenseType + "'," +
                                               "'" + ArraignedOffenseDescription + "','" + ConvictedOffenseStatuteNo + "','" + ConvictedOffenseCode + "','" + ConvictedOffenseType + "','" + ConvictedOffenseDescription + "'," +  
                                               "'" + CalledandFailedDate + "', '" + FailuretoAppearDate + "', '" + OrderforArrestDate + "','" + ConvictedOffenseClass + "','" + PleaCode + "'," +
                                               "'" + VerdictCode + "', '" + MethodofDispositionCode + "', '" + OffenseDispositionDate + "','" + FineAmount + "'," +
                                               "'" + CourtCost + "','" + RestitutionAmount + "','"  + MoniesPaidIndicator + "','"  + MoniestobePaidDate + "','" + NonMVFailtoComplyDate + "', '" + ShowCauseOrderDate + "'," +
                                               "'" + ProbationViolationDate + "', '" + MVFailuretoComplyDate + "','"  + SpecialCondition + "','"  + ChargedOffenseDate + "','"  + BloodAlcoholGreaterThan_16 + "'," +
                                               "'" + PriorPoints + "','"  + JudgeInitials + "','"  + ADAInitials + "','"  + DomesticViolednceConvictedFlag + "')";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
 
    //Insert Routine for WitnessData04
    public bool Insert_WitnessData04_Table(string PKIdentity, string RecordType,string ComplaintantWitnessName, string AgencyCode)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
            RecordType = RecordType.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert WitnessData04(PKIdentity,RecordType,ComplaintantWitnessName,AgencyCode) Values('" + PKIdentity + "','" + RecordType + "'" +
                               ",'" + ComplaintantWitnessName + "','" + AgencyCode + "' )";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
 
    //Insert routine for JudgementData05
    public bool Insert_JudgementData05_Table(string PKIdentity, string RecordType, string OffenseSequenceNo, string ConsolidatedForJudgementFileNo,
                              string ConsolidatedForJudgementOffenseNo, string SentenceLengthMin, string SentenceLengthMinFrame, string SentenceLengthMax,
                              string SentenceLengthMaxFrame, string SentenceType, string ProbationLength, string ProbationLengthFrame, string SupervidedProbationFlag,
                              string CreditForTimeServed, string ConsecutiveSentencingIndicator, string CommunityServiceLength, string CommunityServiceToBeCompletedWithin,
                              string Assesment, string CommunityServiceLicenseRevocation, string CommunityServiceLicRevocationCompliance, string EnhancedFireArmPenalty,
                              string SentencingServicingPlan, string SpecialProbationLength, string SpecialProbationActiveTermFrame, string CustodyOf, string ImpactProgram, string ProgramName,
                              string ResidentialProgramLength, string ResidentialProgramLengthFrame, string ElectronicMonitoringHouseArrest, string ElectMontHouseArrestFrame,
                              string IntensiveProbationSupervision, string DayReportingCenter, string DayReportingCenterFrame, string SpecialConditionsSexOffender, string DrugTreatmentCourt)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
            RecordType = RecordType.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert JudgementData05(PKIdentity,RecordType,OffenseSequenceNo,ConsolidatedForJudgementFileNo," + 
                              "ConsolidatedForJudgementOffenseNo,SentenceLengthMin,SentenceLengthMinFrame,SentenceLengthMax," +
                              "SentenceLengthMaxFrame,SentenceType,ProbationLength,ProbationLengthFrame,SupervisedProbationFlag," + 
                              "CreditForTimeServed,ConsecutiveSentencingIndicator,CommunityServiceLength,CommunityServiceToBeCompletedWithin," +
                              "Assesment,CommunityServiceLicenseRevocation,CommunityServiceLicRevocationCompliance,EnhancedFireArmPenalty," +
                              "SentencingServicingPlan,SpecialProbationLength,SpecialProbationActiveTermFrame,CustodyOf,ImpactProgram,ProgramName," +
                              "ResidentialProgramLength,ResidentialProgramLengthFrame,ElectronicMonitoringHouseArrest,ElectMontHouseArrestFrame," +
                              "IntensiveProbationSupervision,DayReportingCenter,DayReportingCenterFrame,SpecialConditionsSexOffender,DrugTreatmentCourt)" +
                              "Values('" + PKIdentity + "','" + RecordType + "','" + OffenseSequenceNo + "','" + ConsolidatedForJudgementFileNo + "', " +
                              "'" + ConsolidatedForJudgementOffenseNo + "','" + SentenceLengthMin + "','" + SentenceLengthMinFrame + "','" + SentenceLengthMax + "'," +
                              "'" + SentenceLengthMaxFrame + "','" + SentenceType + "','" + ProbationLength + "','" + ProbationLengthFrame + "','" + SupervidedProbationFlag + "'," +
                              "'" + CreditForTimeServed + "','" + ConsecutiveSentencingIndicator + "','" + CommunityServiceLength + "','" + CommunityServiceToBeCompletedWithin + "'," +
                              "'" + Assesment + "','" + CommunityServiceLicenseRevocation + "','" + CommunityServiceLicRevocationCompliance + "','" + EnhancedFireArmPenalty + "'," +
                              "'" + SentencingServicingPlan + "','" + SpecialProbationLength + "','" + SpecialProbationActiveTermFrame + "','" + CustodyOf + "','" + ImpactProgram + "'," +
                              "'" + ProgramName + "','" + ResidentialProgramLength + "','" + ResidentialProgramLengthFrame + "','" + ElectronicMonitoringHouseArrest + "'," +
                              "'" + ElectMontHouseArrestFrame + "','" + IntensiveProbationSupervision + "','" + DayReportingCenter + "','" + DayReportingCenterFrame + "'," +
                              "'" + SpecialConditionsSexOffender + "','" + DrugTreatmentCourt + "')";
                              
            cmd.Connection = sqlConnection;
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
 
    //Insert Routine for SpecialConditionData06
    public bool Insert_SpecialConditionData06_Table(string PKIdentity, string RecordType,string OffenseSequenceNo,
                                          string SpecialCondition2, string SpecialCondition3, string SpecialCondition4)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
            RecordType = RecordType.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Insert SpecialConditionData06(PKIdentity,RecordType,OffenseSequenceNo,SpecialCondition2,SpecialCondition3,SpecialCondition4)" +
                              "Values('" + PKIdentity + "','" + RecordType + "','" + OffenseSequenceNo + "','" + SpecialCondition2 + "'," +
                              "'" + SpecialCondition3 + "','" + SpecialCondition4 + "')";
 
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
    public bool Delete_CaseData01_Record(string PKIdentity)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Delete From CaseData01 Where PKIdentity = '" + PKIdentity + "'";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
            GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Deleted " + PKIdentity + " from table CaseData01","S");
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
    public bool Delete_AliasData02_Record(string PKIdentity)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Delete From AliasData02 Where PKIdentity = '" + PKIdentity + "'";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
            GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Deleted " + PKIdentity + " from table AliasData02", "S");
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
    public bool Delete_OffenseData03_Record(string PKIdentity)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();            
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Delete From OffenseData03 Where PKIdentity = '" + PKIdentity + "'";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
            GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Deleted " + PKIdentity + " from table OffenseData03", "S");
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
    public bool Delete_WitnessData04_Record(string PKIdentity)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Delete From WitnessData04 Where PKIdentity = '" + PKIdentity + "'";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
            GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Deleted " + PKIdentity + " from table WitnessData04", "S");
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
    public bool Delete_JudgementData05_Record(string PKIdentity)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Delete From JudgementData05 Where PKIdentity = '" + PKIdentity + "'";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
            GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Deleted " + PKIdentity + " from table JudgementData05", "S");
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
    public bool Delete_SpecialConditionData06_Record(string PKIdentity)
    {
        bool Success = false;
        try
        {
 
            PKIdentity = PKIdentity.Trim();
 
            SqlConnection sqlConnection = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["AOCSQL08ConnectionString"].ConnectionString));
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Delete From SpecialConditionData06 Where PKIdentity = '" + PKIdentity + "'";
            cmd.Connection = sqlConnection;
 
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
            Success = true;
            GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Deleted " + PKIdentity + " from table SpecialConditionData06", "S");
 
        }
 
        catch (SqlException sqlEx)
        {
        }
        catch (Exception ex)
        {
 
        }
 
        return Success;
 
    }
}

Open in new window

0
Comment
Question by:carolinaspinner
  • 6
  • 4
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22640363
The first thing that I see is that you are reading all the file in memory.

     myFile = myFile + srRead.Read() + "\n";

1) You shouldn't do that with 2 GB of text

2) Strings are immutable in .NET, so there is significant over when concatenating strings like that.

3) You need to read the file one line at a time, and parse it that way.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22649477
I believe that you can attach a new text file to this question.  We don't need to create a new question.  I feel like my concerns need to be addressed here first.
0
 

Author Comment

by:carolinaspinner
ID: 22651029
Thanks, I just got a response the remove the other file. I am trying this out to see
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:carolinaspinner
ID: 22658909
Thanks for the response. In the code I have commented out  //myFile = myFile + srRead.ReadLine() + "\n";
which is the readline method. I tried this and still recieved the same results. Are you talking about the readline method or something else?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22658928
No, I am talking about collecting all the text into 'myFile'.  You can't read all the text into the memory.  You need to read a line at a time, and parse it that way.
0
 

Author Comment

by:carolinaspinner
ID: 22660863
Can you show me in code what you are talking about.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22661024
Can you attach a small representative portion from the top of the file that you are parsing, please (not the entire file)?
0
 

Author Comment

by:carolinaspinner
ID: 22661316
OK. As I stated the program will parse and upload a small text file 25MB< . The problem is with files 30MB and larger. This is the same sample I uploaded.  This is a small as it gets.
AOCSmallSamp.txt
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 22661586
I have plenty of time today, waiting for software testing to get started, so I wanted to show you the realm of possibilities with C# and SQL Server.

Attached is a class that shows what I mean.


using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Data.SqlClient;
 
namespace TextProcessing
{
    internal class TextFileParser
    {
 
        // Create and add columns for each table that will be processed.  The offset positions
        // for each column is based on the MaxLength for each DataColumn for a DataTable.
        // Each DataTable in the table list, will be processed to bulk insert records into the
        // corresponding SQL Server table.  The table constants define the SQL Server table names.
 
        private const string TABLE_ALIAS = "Alias";
        private const string TABLE_CASE = "Case";
        private const string TABLE_JUDGEMENT = "Judgement";
        private const string TABLE_OFFENSE = "Offense";
        private const string TABLE_SPECIAL_CONDITION = "SpecialCondition";
        private const string TABLE_SUMMARY = "Summary";
        private const string TABLE_WITNESS = "Witness";
 
        private Dictionary<string, DataTable> _TableList = null;
 
        public TextFileParser()
        {
            this.CreateAliasesDataTable();
            this.CreateCasesDataTable();
            this.CreateJudgementsDataTable();
            this.CreateOffensesDataTable();
            this.CreateSpecialConditionsDataTable();
            this.CreateSummaryDataTable();
            this.CreateWitnessDataTable();
        }
 
        public void ParseFile(string fileName)
        {
            // Open the text file to read one line at a time.
            using (StreamReader reader = File.OpenText(fileName))
            {
                while (reader.Peek() > -1)
                {
                    // Process each line as it is read.
                    this.ProcessLine(reader.ReadLine());
                }
            }
            //GetLogger.LogMessage(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " - Inserted new data to AOC tables", "");
        }
 
        public void WriteToServer(string connectionString)
        {
            // Create an instance of an SqlBulkCopy class, which is optimizer class that 
            // wraps the 'bcp' SQL Server command.
            SqlBulkCopy bulkCopier = new SqlBulkCopy(connectionString);
 
            foreach (DataTable dt in _TableList.Values)
            {
                // Write all the records to the DataTable in a single shot.
                bulkCopier.WriteToServer(dt);
            }
        }
 
        private void CreateAliasesDataTable()
        {
            DataTable dt = new DataTable(TABLE_ALIAS);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "DefendantAliasName", 28);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void CreateCasesDataTable()
        {
            DataTable dt = new DataTable(TABLE_CASE);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "CountyCode", 3);
            this.AddDataTableColumn(dt, "CountyName", 12);
            this.AddDataTableColumn(dt, "CaseYear", 4);
            this.AddDataTableColumn(dt, "CaseType", 3);
            this.AddDataTableColumn(dt, "CaseSequence", 6);
            this.AddDataTableColumn(dt, "DefendantName", 28);
            this.AddDataTableColumn(dt, "DefendantAddress1", 20);
            this.AddDataTableColumn(dt, "DefendantAddress2", 15);
            this.AddDataTableColumn(dt, "DefendantCity", 15);
            this.AddDataTableColumn(dt, "DefendantState", 2);
            this.AddDataTableColumn(dt, "DefendantZip", 2);
            this.AddDataTableColumn(dt, "RaceCode", 1);
            this.AddDataTableColumn(dt, "SexCode", 1);
            this.AddDataTableColumn(dt, "DefendantDOB", 8);
            this.AddDataTableColumn(dt, "Filler", 5);
            this.AddDataTableColumn(dt, "SSNLast4Digits", 4);
            this.AddDataTableColumn(dt, "DefendantDriversLicNo", 25);
            this.AddDataTableColumn(dt, "DefendantDriversLicState", 2);
            this.AddDataTableColumn(dt, "CitationNumber", 8);
            this.AddDataTableColumn(dt, "TrialDate", 8);
            this.AddDataTableColumn(dt, "DistrictCourtCaseProccess", 1);
            this.AddDataTableColumn(dt, "SuperiorCourtCaseProcess", 1);
            this.AddDataTableColumn(dt, "DefendantStateIdentifier", 10);
            this.AddDataTableColumn(dt, "DefendantInJailIndicator", 1);
            this.AddDataTableColumn(dt, "DateCaseServed", 8);
            this.AddDataTableColumn(dt, "CheckDigitNumber", 7);
            this.AddDataTableColumn(dt, "LID", 15);
            this.AddDataTableColumn(dt, "ORI", 9);
            this.AddDataTableColumn(dt, "CaseOfficeLocationCode", 2);
            this.AddDataTableColumn(dt, "DomesticViolenceCharge", 1);
            this.AddDataTableColumn(dt, "VictimsRightAct", 1);
            this.AddDataTableColumn(dt, "PKIdentity", 255);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void CreateJudgementsDataTable()
        {
            DataTable dt = new DataTable(TABLE_JUDGEMENT);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "JudgementOffenseSequenceNo", 2);
            this.AddDataTableColumn(dt, "ConsolidatedForJudgementFileNo", 13);
            this.AddDataTableColumn(dt, "ConsolidatedForJudgementOffenseNo", 2);
            this.AddDataTableColumn(dt, "SentenceLengthMin", 3);
            this.AddDataTableColumn(dt, "SentenceLengthMinFrame", 1);
            this.AddDataTableColumn(dt, "SentenceLengthMax", 3);
            this.AddDataTableColumn(dt, "SentenceLengthMaxFrame", 1);
            this.AddDataTableColumn(dt, "SentenceType", 1);
            this.AddDataTableColumn(dt, "ProbationLength", 3);
            this.AddDataTableColumn(dt, "ProbationLengthFrame", 1);
            this.AddDataTableColumn(dt, "SupervisedProbationFlag", 1);
            this.AddDataTableColumn(dt, "CreditForTimeServed", 4);
            this.AddDataTableColumn(dt, "ConsecutiveSentencingIndicator", 1);
            this.AddDataTableColumn(dt, "CommunityServiceLength", 4);
            this.AddDataTableColumn(dt, "CommunityServiceToBeCompleted", 3);
            this.AddDataTableColumn(dt, "Assesment", 1);
            this.AddDataTableColumn(dt, "CommunityServiceLicenseRevocation", 8);
            this.AddDataTableColumn(dt, "CommunityServiceLicenseRevocationCompliance", 8);
            this.AddDataTableColumn(dt, "EnhancedFirearmPenalty", 1);
            this.AddDataTableColumn(dt, "SentencingServicePlan", 1);
            this.AddDataTableColumn(dt, "SpecialProbationLength", 4);
            this.AddDataTableColumn(dt, "SpecialProbationActiveTermFrame", 1);
            this.AddDataTableColumn(dt, "CustodyOf", 1);
            this.AddDataTableColumn(dt, "ImpactProgram", 1);
            this.AddDataTableColumn(dt, "ProgramName", 50);
            this.AddDataTableColumn(dt, "ResidentialProgramLength", 4);
            this.AddDataTableColumn(dt, "ResidentialProgramLengthFrame", 1);
            this.AddDataTableColumn(dt, "ElectronicMonitoringHouseArrest", 4);
            this.AddDataTableColumn(dt, "ElectronicMonitoringHouseArrestFrame", 1);
            this.AddDataTableColumn(dt, "IntensiveProbationSupervision", 4);
            this.AddDataTableColumn(dt, "DayReportingCenter", 4);
            this.AddDataTableColumn(dt, "DayReportingCenterFrame", 1);
            this.AddDataTableColumn(dt, "SpecialConditionsSexOffender", 1);
            this.AddDataTableColumn(dt, "DrugTreatmentCourt", 1);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void CreateOffensesDataTable()
        {
            DataTable dt = new DataTable(TABLE_OFFENSE);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "OffenseSequenceNo", 2);
            this.AddDataTableColumn(dt, "ChargedOffenseCode", 4);
            this.AddDataTableColumn(dt, "ChargedOffenseType", 1);
            this.AddDataTableColumn(dt, "ChargedOffenseDescription", 45);
            this.AddDataTableColumn(dt, "ChargedOffenseStatuteNo", 15);
            this.AddDataTableColumn(dt, "ArraignedOffenseCode", 4);
            this.AddDataTableColumn(dt, "ArraignedOffenseType", 1);
            this.AddDataTableColumn(dt, "ArraignedOffenseDescription", 45);
            this.AddDataTableColumn(dt, "ArraignedOffenseStatuteNo", 15);
            this.AddDataTableColumn(dt, "ConvictedOffenseCode", 4);
            this.AddDataTableColumn(dt, "ConvictedOffenseType", 1);
            this.AddDataTableColumn(dt, "ConvictedOffenseDescription", 45);
            this.AddDataTableColumn(dt, "ConvictedOffenseStatuteNo", 15);
            this.AddDataTableColumn(dt, "CalledAndFailedDate", 8);
            this.AddDataTableColumn(dt, "FailureToAppearDate", 8);
            this.AddDataTableColumn(dt, "OrderForArrestDate", 8);
            this.AddDataTableColumn(dt, "ConvictedOffenseClass", 2);
            this.AddDataTableColumn(dt, "PleaCode", 2);
            this.AddDataTableColumn(dt, "VerdictCode", 2);
            this.AddDataTableColumn(dt, "MethodofDispositionCode", 2);
            this.AddDataTableColumn(dt, "OffenseDispositionDate", 8);
            this.AddDataTableColumn(dt, "FineAmount", 5);
            this.AddDataTableColumn(dt, "CourtCost", 7);
            this.AddDataTableColumn(dt, "RestitutionAmount", 9);
            this.AddDataTableColumn(dt, "MoniesPaidIndicator", 1);
            this.AddDataTableColumn(dt, "MoniesToBePaidDate", 8);
            this.AddDataTableColumn(dt, "NonMVFailureToComplyDate", 8);
            this.AddDataTableColumn(dt, "ShowCauseOrderDate", 8);
            this.AddDataTableColumn(dt, "ProbationViolationDate", 8);
            this.AddDataTableColumn(dt, "MVFailureToComplyDate", 8);
            this.AddDataTableColumn(dt, "SpecialCondition", 59);
            this.AddDataTableColumn(dt, "ChargedOffenseDate", 8);
            this.AddDataTableColumn(dt, "BloodAlcoholGreaterThan_16", 1);
            this.AddDataTableColumn(dt, "PriorPoints", 2);
            this.AddDataTableColumn(dt, "JudgeInitials", 3);
            this.AddDataTableColumn(dt, "ADAInitials", 3);
            this.AddDataTableColumn(dt, "DomesticViolenceConvictedFlag", 1);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void CreateSpecialConditionsDataTable()
        {
            DataTable dt = new DataTable(TABLE_SPECIAL_CONDITION);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "SpecialConditionsOffenseSequenceNo", 2);
            this.AddDataTableColumn(dt, "SpecialCondition2", 59);
            this.AddDataTableColumn(dt, "SpecialCondition3", 59);
            this.AddDataTableColumn(dt, "SpecialCondition4", 59);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void CreateSummaryDataTable()
        {
            DataTable dt = new DataTable(TABLE_SUMMARY);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "CountOfTotalRecords", 13);
            this.AddDataTableColumn(dt, "CountOfDeleteRecords", 13);
            this.AddDataTableColumn(dt, "CountOfCaseRecords", 13);
            this.AddDataTableColumn(dt, "CountOfAliasRecords", 13);
            this.AddDataTableColumn(dt, "CountOfOffenseRecords", 13);
            this.AddDataTableColumn(dt, "CountOfWitnessRecords", 13);
            this.AddDataTableColumn(dt, "CountOfJudgementRecords", 13);
            this.AddDataTableColumn(dt, "CountOfSpecialConditionsRecords", 13);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void CreateWitnessDataTable()
        {
            DataTable dt = new DataTable(TABLE_WITNESS);
 
            this.AddDataTableColumn(dt, "RecordType", 2);
            this.AddDataTableColumn(dt, "ComplaintantWitnessName", 28);
            this.AddDataTableColumn(dt, "AgencyCode", 3);
 
            _TableList.Add(dt.TableName, dt);
        }
 
        private void AddDataTableColumn(DataTable dt, string columnName, int maxLength)
        {
            // Add a DataColumn with a specified maximum length.  Each length
            // is used to determine the position for the next column.
            DataColumn column = dt.Columns.Add(columnName);
            column.MaxLength = maxLength;
        }
 
        private void ProcessLine(string lineText)
        {
            //Record Type 00 Case Delete
            string recordType = lineText.Substring(0, 2);
 
            switch (recordType)
            {
                // Create table maintenance records
                case "00":
                    break;
 
                // Add Case record
                case "01":
                    this.InsertDataTableRecord(TABLE_CASE, lineText);
                    break;
 
                // Add Alias record
                case "02":
                    this.InsertDataTableRecord(TABLE_ALIAS, lineText);
                    break;
 
                // Add Offense record
                case "03":
                    this.InsertDataTableRecord(TABLE_OFFENSE, lineText);
                    break;
 
                // Add Witness record
                case "04":
                    this.InsertDataTableRecord(TABLE_WITNESS, lineText);
                    break;
 
                // Add Judgement record
                case "05":
                    this.InsertDataTableRecord(TABLE_JUDGEMENT, lineText);
                    break;
 
                // Add SpecialConditions record
                case "06":
                    this.InsertDataTableRecord(TABLE_SPECIAL_CONDITION, lineText);
                    break;
 
                // Add SpecialConditions record
                case "99":
                    this.InsertDataTableRecord(TABLE_SUMMARY, lineText);
                    break;
 
            }
            //if (lineText.StartsWith("00"))
            //{
            //RecordType = lineText.Substring(0, 2);
            //CountyCode = lineText.Substring(2, 3);
            //CountyName = lineText.Substring(5, 12);
            //CaseYear = lineText.Substring(17, 4);
            //CaseType = lineText.Substring(21, 3);
            //CaseSequence = lineText.Substring(24, 6);
            //PKIdentity = CountyCode + CaseYear + CaseSequence;//Added RW
 
            ////Code to insert into SQL here
            //GetSQLCode.Insert_CaseDelete00_Table(PKIdentity, RecordType, CountyCode, CountyName, CaseYear, CaseType, CaseSequence);
            //GetSQLCode.Delete_AliasData02_Record(PKIdentity);
            //GetSQLCode.Delete_OffenseData03_Record(PKIdentity);
            //GetSQLCode.Delete_WitnessData04_Record(PKIdentity);
            //GetSQLCode.Delete_JudgementData05_Record(PKIdentity);
            //GetSQLCode.Delete_SpecialConditionData06_Record(PKIdentity);
            //GetSQLCode.Delete_CaseData01_Record(PKIdentity);
            //}
 
        }
 
        private void InsertDataTableRecord(string tableName, string lineText)
        {
            // Get the table to add a new record to, by its name.
            DataTable dt = _TableList[tableName];
 
            // Create a new row.
            DataRow dr = dt.NewRow();
 
            // Keep a running offset to each column starting position.
            int offset = 0;
 
            foreach (DataColumn dc in dt.Columns)
            {
                dr[dc] = lineText.Substring(offset, dc.MaxLength);
                // The next column is offset + current column length.
                offset += dc.MaxLength;
            }
 
            // Add the row to the working table.
            dt.Rows.Add(dr);
 
        }
 
 
    }
 
}

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22661613
I tried to stream-line the parsing process, and to optimize the SQL Server side with the System.Data.SqlClient.SqlBulkCopy class.  If it doesn't make sense, or if you have difficulties running it, or since the record type "00" is incomplete, please don't hesitate to ask.  I am not sure how much longer I am going to have plenty of time on my hands.

Bob
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question