Solved

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

Posted on 2008-10-03
12
431 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, I just got a response the remove the other file. I am trying this out to see
0
 

Author Comment

by:carolinaspinner
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

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

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now