carolinaspinner
asked on
Reading and Parsing >2GB FixedWidth Text Files with the StreamReader in a WebForm
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;
}
}
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.
ASKER
Thanks, I just got a response the remove the other file. I am trying this out to see
ASKER
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?
which is the readline method. I tried this and still recieved the same results. Are you talking about the readline method or something else?
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.
ASKER
Can you show me in code what you are talking about.
Can you attach a small representative portion from the top of the file that you are parsing, please (not the entire file)?
ASKER
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
AOCSmallSamp.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I tried to stream-line the parsing process, and to optimize the SQL Server side with the System.Data.SqlClient.SqlB
Bob
Bob
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.