public override int RunJob()
{
try
{
FileLock(true);
_deleteParticipantDataTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("DeleteParticipantDataTimeout"));
_deleteInputFileErrorsOver1YearOldTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("DeleteOldInputFileErrorsTimeout"));
int jobReturnCode = ClearUpload();
InputFileHistory ifh = inputFileHistoryRepository.Find(x => x.Code == _inputFileHistoryCode).First();
ifh.Status = "Cancelled";
inputFileHistoryRepository.Update(ifh);
//unlock the File provider for future uploads
FileLock(false);
return(jobReturnCode);
}
catch (Exception e)
{
if (CurrentStep != null)
{
CurrentStep.Finish(false);
}
UnhandledException = e;
spRunner2.PublishError("Clear Participant Data", 3, e.Message, _inputFileHistoryCode, null, null);
//Log error to db for debug purposes.
//unlock the File provider for future uploads
FileLock(false);
return(1);
}
}
public override int RunJob()
{
try
{
FileLock(true);
_deleteParticipantDataTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("DeleteParticipantDataTimeout"));
_deleteInputFileErrorsOver1YearOldTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("DeleteOldInputFileErrorsTimeout"));
_deleteValidatedParticipantDataTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("DeleteValidatedParticipantDataTimeout"));
_copyValidatedParticipantsTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("CopyValidatedParticipantsTimeout"));
int jobReturnCode = ValidatedUpload(_decisionLevel);
//unlock the File provider for future uploads
FileLock(false);
return(jobReturnCode);
}
catch (Exception e)
{
if (CurrentStep != null)
{
CurrentStep.Finish(false);
}
UnhandledException = e;
spRunner2.PublishError("Copy Valid Records", 3, e.Message, _inputFileHistoryCode, null, null);
//Log error to db for debug purposes.
//unlock the File provider for future uploads
FileLock(false);
return(1);
}
}
/// <summary>
/// Removes old data for the current file from the validated area.
/// </summary>
private void CopyValidatedParticipants(SqlTransaction transact)
{
CurrentStep = this["CopyValidatedParticipants"];
CurrentStep.Timeout = _copyValidatedParticipantsTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Copying records to validated area");
spRunner.CopyValidatedParticipants(ProviderKey, _decisionLevel, transact, _copyValidatedParticipantsTimeout);
CurrentStep.Finish(true);
}
/// <summary>
/// Removes old data for the current file from the loading area.
/// </summary>
private void DeleteInputFileErrorsOver1YearOld()
{
CurrentStep = this["DeleteInputFileErrorsOver1YearOld"];
CurrentStep.Timeout = _deleteInputFileErrorsOver1YearOldTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Deleting data over 1 year old from error message table");
spRunner.DeleteInputFileErrorsOver1YearOld(_deleteInputFileErrorsOver1YearOldTimeout);
CurrentStep.Finish(true);
}
/// <summary>
/// Removes old data for the current file from the loading area.
/// </summary>
private void DeleteParticipantData()
{
CurrentStep = this["DeleteParticipantData"];
CurrentStep.Timeout = _deleteParticipantDataTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Deleting previous file from loading area");
spRunner.DeleteParticipantData(ProviderKey, _deleteParticipantDataTimeout);
CurrentStep.Finish(true);
}
/// <summary>
/// Removes old data for the current file from the validated area.
/// </summary>
private void DeleteValidatedParticipantData(SqlTransaction transact)
{
CurrentStep = this["DeleteValidatedParticipantData"];
CurrentStep.Timeout = _deleteValidatedParticipantDataTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Deleting previous file from validated area");
spRunner.DeleteValidatedParticipantData(ProviderKey, transact, _deleteValidatedParticipantDataTimeout);
CurrentStep.Finish(true);
}
private void PopulatePostCodes()
{
CurrentStep = this["CopyToMI"];
CurrentStep.Timeout = _populatePostCodesTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "copy post code data to MI");
MIStagingStoredProcedures spRunner = new MIStagingStoredProcedures();
spRunner.PopulatePostCodes(_populatePostCodesTimeout);
CurrentStep.Finish(true);
}
private void TruncateMIPostCodeData()
{
CurrentStep = this["DeletePreviousFromMI"];
CurrentStep.Timeout = _truncateMIPostCodeDataTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Deleting previous post code files from MI");
MIStagingStoredProcedures spRunner = new MIStagingStoredProcedures();
spRunner.TruncateMIPostCodeData(_truncateMIPostCodeDataTimeout);
CurrentStep.Finish(true);
}
/// <summary>
/// Removes old data for the current file from the loading area.
/// </summary>
private void DeleteFromLoadingArea()
{
CurrentStep = this["DeleteFromLoadingArea"];
CurrentStep.Timeout = _deleteParticipantDataTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Deleting previous file from loading area");
if (_fileType == "Participant")
{
MIStagingStoredProcedures spRunner = new MIStagingStoredProcedures();
spRunner.DeleteParticipantData(ProviderKey, _deleteParticipantDataTimeout);
}
CurrentStep.Finish(true);
}
private void CopyBulkLoadedData()
{
CurrentStep = this["CopyBulkLoadedData"];
CurrentStep.Timeout = _copyParticipantBulkLoadedFlatTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Copying bulk data");
if (_fileType == "Participant")
{
MIStagingStoredProcedures spRunner = new MIStagingStoredProcedures();
spRunner.CopyParticipantBulkLoadedFlat(ProviderKey, _copyParticipantBulkLoadedFlatTimeout);
}
UpdateHistoryStatus("Loaded");
CurrentStep.Finish(true);
}
public override int RunJob()
{
try
{
FileLock(true);
//Insert of the file upload history record
InsertHistoryRecord();
//set up timeouts for job
_deleteParticipantDataTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("DeleteParticipantDataTimeout"));
_validateParticipantDuplicatesTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ValidateParticipantDuplicatesTimeout"));
_validateAgreementRegionTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ValidateAgreementRegionTimeout"));
_validateParticipantPostCodeTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ValidateParticipantPostCodeTimeout"));
_validateLeaverTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ValidateLeaverTimeout"));
_validateLeavingDateTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ValidateLeavingDateTimeout"));
_validateStartDateTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ValidateStartDateTimeout"));
_participantSelectValidCountTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ParticipantSelectValidCountTimeout"));
_participantSelectErrorCountTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ParticipantSelectErrorCountTimeout"));
_participantSelectWarningCountTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ParticipantSelectWarningCountTimeout"));
_participantSelectInformationalCountTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("ParticipantSelectInformationalCountTimeout"));
_loadParticipantMessagesTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("LoadParticipantMessagesTimeout"));
_copyParticipantBulkLoadedFlatTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("CopyParticipantBulkLoadedFlatTimeout"));
Guid historyFileGuid = _inputFileHistoryRow.Code;
int jobReturnCode = UploadFile();
//leave locked until a decision has been made
//FileLock(false);
return(jobReturnCode);
}
catch (Exception e)
{
if (CurrentStep != null)
{
CurrentStep.Finish(false);
}
UnhandledException = e;
spRunner2.PublishError("File Upload", 3, e.Message, _inputFileHistoryCode, null, null);
//Log error to db for debug purposes.
//unlock the File provider for future uploads
FileLock(false);
return(1);
}
}
private void LoadSql(string xMLFilename, string connectionString)
{
CurrentStep = this["BulkUpload"];
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Bulk uploading");
IDataReader reader = null;
//new ParticipantDataReader(xMLFilename);
SqlBulkCopy Copier = new SqlBulkCopy(connectionString);
if (_fileType == "Participant")
{
Copier.DestinationTableName = "dbo.ParticipantBulkLoadedFlat";
reader = new ParticipantDataReader(xMLFilename, _inputFileHistoryCode, _providingOrganistaionCode);
}
Copier.BatchSize = 5000;
Copier.NotifyAfter = 1000;
Copier.BulkCopyTimeout = 60;
Copier.SqlRowsCopied += new SqlRowsCopiedEventHandler(Copier_SqlRowsCopied);
Copier.WriteToServer(reader);
CurrentStep.Finish(true);
}
public override int RunJob()
{
try
{
_truncatePostCodeDataTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("TruncatePostCodeDataTimeout"));
_truncateMIPostCodeDataTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("TruncateMIPostCodeDataTimeout"));
_populatePostCodesTimeout = Convert.ToInt32(DataAccessUtilities.GetSystemParameterByName("PopulatePostCodesTimeout"));
int jobReturnCode = UploadFiles();
return(jobReturnCode);
}
catch (Exception e)
{
if (CurrentStep != null)
{
CurrentStep.Finish(false);
}
UnhandledException = e;
//Log error to db for debug purposes.
return(1);
}
}
private void Validate(string datafile, string schema)
{
CurrentStep = this["SchemaValidation"];
CurrentStep.Start();
//Ensure all required parameters are available
if (_schema == string.Empty || UniqueFileName == string.Empty || _connectionString == string.Empty ||
_mappingSchema == string.Empty || _schema == null || UniqueFileName == null ||
_connectionString == null ||
_mappingSchema == null)
{
//PublishError("Bulk Upload", 3, "Missing Schema, Mapping Schema, Data File or Connection String.", _inputFileHistoryCode, null, null);
_errorCode = 1;
_isSchemaValid = false;
throw new ApplicationException("Missing Schema, Mapping Schema, Data File or Connection String.");
}
else
{
//Consider making the XmlReaderSettings instance static if it uses the same settings
//for all xml validation
XmlReaderSettings xmlReaderSettings = new XmlReaderSettings();
xmlReaderSettings.CheckCharacters = true;
xmlReaderSettings.CloseInput = true;
//if you have a finite number of schemas, consider caching each schema in
//a static XmlSchemaSet instance
xmlReaderSettings.Schemas.Add(null, schema);
xmlReaderSettings.ValidationType = ValidationType.Schema;
xmlReaderSettings.ValidationFlags |= XmlSchemaValidationFlags.ReportValidationWarnings;
//...add any other ValidationFlags necessary here...
xmlReaderSettings.ValidationEventHandler += new ValidationEventHandler(ShowCompileErrors);
try
{
//Wrap the creation of the XmlReader in a 'using' block since
//it implements IDisposable
string SchemaVersion = "";
int MaxChunksToReadForSchemaVersionCountdown = 10; // Max number of xml chunks to read to try and get schema version
int PercentThroughOld = 0;
int PercentThroughInt = 0;
FileStream DataFileStream = new FileStream(datafile, FileMode.Open, FileAccess.Read);
using (XmlReader xmlReader = XmlReader.Create(DataFileStream, xmlReaderSettings))
{
if (xmlReader != null)
{
// Definitions for reading extra info surrounding DOB
string LastRegion = string.Empty;
string LastAgreement = string.Empty;
string LastParticipantReferenceID = string.Empty;
string LastStartDate = string.Empty;
while (xmlReader.Read())
{
if (xmlReader.LocalName == "ParticipantInformation" && xmlReader.IsStartElement())
{
SchemaVersion = xmlReader.GetAttribute("SchemaVersion");
}
if (MaxChunksToReadForSchemaVersionCountdown < 1 && SchemaVersion != "1.0")
{
// Set the error and exit immediately as this must be an invalid xml file, e.g.,
// saved from excel!!
_errorCode = 5;
_isSchemaValid = false;
spRunner2.PublishError("Schema Validation", 3, "This xml file is in an unrecognisable format.",
_inputFileHistoryCode, null, null);
break;
}
// decrement countdown used for trying to get the schema version
if (MaxChunksToReadForSchemaVersionCountdown >= 1)
{
MaxChunksToReadForSchemaVersionCountdown--;
}
//Ensure the providing organisation is the one selected on the ui
if (xmlReader.LocalName == "ProvidingOrganisation" && xmlReader.IsStartElement())
{
string ProvidingOrganisationString = xmlReader.ReadString();
if (ProvidingOrganisationString != ProviderKey.Substring(0, ProviderKey.Length - 2))
{
_errorCode = 5;
//InputFileErrors.PublishError("The file providing organisation: " + ProvidingOrganisationString +
// " does not match the selected value of " +
// ProviderKey.Substring(0, ProviderKey.Length - 2), 3, "Schema Validation", _inputFileHistoryCode);
// set the error and exit immediatley as the file is not fit for purpose
_isSchemaValid = false;
spRunner2.PublishError("Schema Validation", 3, "The xml file Providing Organisation does not match selection", _inputFileHistoryCode
, null, null);
break;
}
}
//Ensure the providing organisation is the one selected on the ui
if (xmlReader.LocalName == "FileRunDate" && xmlReader.IsStartElement())
{
string FileRunDate = xmlReader.ReadString();
_inputFileHistoryRow.TransferDate = DateTime.Parse(FileRunDate);
inputFileHistoryRepository.Update(_inputFileHistoryRow);
inputFileHistoryRepository.SaveChanges();
}
//Ensure the region is the one selected on the ui
if (ProviderKey.Substring(ProviderKey.Length - 2, 2) != "XX")
{
if (xmlReader.LocalName == "RegionCode" && xmlReader.IsStartElement())
{
string RegionCodeString = xmlReader.ReadString();
// Used to store for reporting schema errors, e.g., DOB errors
LastRegion = RegionCodeString;
if (RegionCodeString != ProviderKey.Substring(ProviderKey.Length - 2, 2))
{
_errorCode = 5;
//InputFileErrors.PublishError("The file RegionCode: " + RegionCodeString +
// " does not match the selected value of " +
// ProviderKey.Substring(ProviderKey.Length - 2, 2), 3, "Schema Validation", _inputFileHistoryCode);
// set the error and exit immediatley as the file is not fit for purpose
_isSchemaValid = false;
spRunner2.PublishError("Schema Validation", 3, "The xml file Region does not match selection", _inputFileHistoryCode,
null, null);
break;
}
}
}
if (this._fileType == "Participant")
{
#region Store info for reporting schema errors, e.g., DOB errors
if (ProviderKey.Substring(ProviderKey.Length - 2, 2) == "XX")
{
if (xmlReader.LocalName == "RegionCode" && xmlReader.IsStartElement())
{
// Set last region if All region, i.e., XX otherwise Last region is already set elsewhere in
// this code
LastRegion = xmlReader.ReadString();
}
}
// Set AgreementID
if (xmlReader.LocalName == "AgreementID" && xmlReader.IsStartElement())
{
LastAgreement = xmlReader.ReadString();
}
// Set Participant Ref ID
if (xmlReader.LocalName == "ParticipantReferenceID" && xmlReader.IsStartElement())
{
LastParticipantReferenceID = xmlReader.ReadString();
}
// Set Start Date
if (xmlReader.LocalName == "StartDate" && xmlReader.IsStartElement())
{
LastStartDate = xmlReader.ReadString();
}
#endregion
#region DOB Error Check
if (xmlReader.LocalName == "DOB" && xmlReader.IsStartElement())
{
try
{
string DOBDateTimeString = xmlReader.ReadString();
DateTime SqlMinDateTime = new DateTime(1753, 1, 1);
DateTime DOBDateTime = Convert.ToDateTime(DOBDateTimeString);
if (DOBDateTime < SqlMinDateTime)
{
_errorCode = 5;
// Don't process the file
_isSchemaValid = false;
throw new ApplicationException("The DOB is prior to 1 Jan 1753 on record: "
+ "KeyValue: " + ProviderKey
+ " Region: " + LastRegion + " Agreement: " + LastAgreement +
" Participant Ref ID: " + LastParticipantReferenceID
+ " Start Date: " + LastStartDate + " DOB: " + DOBDateTime.ToString("dd/MM/yyyy")
);
}
}
catch (FormatException ex)
{
_errorCode = 5;
// Don't process the file
_isSchemaValid = false;
throw new ApplicationException("The DOB is incorrect on record: " + "KeyValue: " + ProviderKey
+ " Region: " + LastRegion + " Agreement: " + LastAgreement +
" Participant Ref ID: " + LastParticipantReferenceID
+ " Start Date: " + LastStartDate + " DOB: Invalid Date or Non Date Format"
, ex);
}
}
#endregion
}
long CurrentPosition = DataFileStream.Position;
decimal PercentThrough = Convert.ToDecimal(CurrentPosition) / Convert.ToDecimal(_fileLength) * 100;
PercentThroughInt = Convert.ToInt32(decimal.Truncate(PercentThrough));
if (PercentThroughInt > PercentThroughOld)
{
CurrentStep.UpdateProgress(PercentThroughInt);
//OnProgressEvent(new ProgressEventArgs("Schema", PercentThroughInt, PercentThroughInt - PercentThroughOld, "Validating file content"));
PercentThroughOld = PercentThroughInt;
}
if (this._fileType == "Participant")
{
if (xmlReader.LocalName == "Participant" && xmlReader.IsStartElement())
{
_recordCount++;
_flatRecordCount++;
}
if (xmlReader.LocalName == "ESFP1P4Qualification" && xmlReader.IsStartElement())
{
_flatRecordCount++;
}
if (xmlReader.LocalName == "ESFP2P5Qualification" && xmlReader.IsStartElement())
{
_flatRecordCount++;
}
}
}
//explicitly call Close on the XmlReader to reduce strain on the GC
xmlReader.Close();
}
}
//Update status to 'Schema Validated'
UpdateHistoryStatus("Schema Validated");
}
catch (Exception ex)//..various types of Exceptions can be thrown by the XmlReader.Create() method)
{
_errorCode = 5;
throw new ApplicationException(ex.Message, ex);
}
}
if (!_isSchemaValid)
{
throw new ApplicationException("Schema Invalid");
}
CurrentStep.Finish(true);
}
/// <summary>
/// Validates participant type files for business rules
/// </summary>
private void ParticipantBusinessValidate()
{
try
{
//currentStep = this["DeleteParticipantData"];
//currentStep.Start();
//currentStep.UpdateProgress(0, "Deleting Participant Data");
//spRunner.DeleteParticipantData(ProviderKey);
//currentStep.Finish(true);
CurrentStep = this["ValidateParticipantDuplicates"];
CurrentStep.Timeout = _validateParticipantDuplicatesTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Checking for duplicate participants");
spRunner.ValidateParticipantDuplicates(ProviderKey, _validateParticipantDuplicatesTimeout);
CurrentStep.Finish(true);
CurrentStep = this["ValidateAgreementRegion"];
CurrentStep.Timeout = _validateAgreementRegionTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Checking agreement regions");
spRunner.ValidateAgreementRegion(ProviderKey, _validateAgreementRegionTimeout);
CurrentStep.Finish(true);
CurrentStep = this["ValidateParticipantPostCode"];
CurrentStep.Timeout = _validateParticipantPostCodeTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Checking participant post codes");
spRunner.ValidateParticipantPostCode(ProviderKey, _validateParticipantPostCodeTimeout);
CurrentStep.Finish(true);
// ---------------------------- Remove check may have to put back, client dependant ---------
//OnProgressEvent(new ProgressEventArgs("Business", 40, 25,"Checking participant projects"));
//ParticipantBulkLoaded.ValidateParticipantProject(ProviderKey);
// -----------------------------------------------------------------------------------------
CurrentStep = this["ValidateLeaver"];
CurrentStep.Timeout = _validateLeaverTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Checking participant leaver information");
spRunner.ValidateLeaver(ProviderKey, _validateLeaverTimeout);
CurrentStep.Finish(true);
CurrentStep = this["ValidateLeavingDate"];
CurrentStep.Timeout = _validateLeavingDateTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Checking participant leaving dates are within range");
spRunner.ValidateLeavingDate(ProviderKey, _validateLeavingDateTimeout);
CurrentStep.Finish(true);
CurrentStep = this["ValidateStartDate"];
CurrentStep.Timeout = _validateStartDateTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Checking participant start dates are in range");
spRunner.ValidateStartDate(ProviderKey, _validateStartDateTimeout);
CurrentStep.Finish(true);
CurrentStep = this["LoadParticipantMessages"];
CurrentStep.Timeout = _loadParticipantMessagesTimeout;
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Loading participant error messages");
spRunner2.LoadParticipantMessages(_inputFileHistoryCode, _loadParticipantMessagesTimeout);//first validation rule completed
CurrentStep.Finish(true);
CurrentStep = this["ValidatePreviousLoad"];
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Comparing previous load");
ValidatePreviousLoad(_inputFileHistoryCode, ProviderKey, _fileType, _recordCount, _inputFileHistoryRow.TransferDate);
CurrentStep.Finish(true);
CurrentStep = this["UpdateParticipantCounts"];
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Counting Valid Participants");
_noOfValidRecords = spRunner.ParticipantSelectValidCount(ProviderKey, _participantSelectValidCountTimeout);
CurrentStep.UpdateProgress(25, "Counting Error Participants");
_noOfErrorRecords = spRunner.ParticipantSelectErrorCount(ProviderKey, _participantSelectErrorCountTimeout);
CurrentStep.UpdateProgress(50, "Counting Warning Participants");
_noOfWarningRecords = spRunner.ParticipantSelectWarningCount(ProviderKey, _participantSelectWarningCountTimeout);
CurrentStep.UpdateProgress(75, "Counting Informational Participants");
_noOfInformationalRecords = spRunner.ParticipantSelectInformationalCount(ProviderKey, _participantSelectInformationalCountTimeout);
CurrentStep.UpdateProgress(100, "Business validation complete");
//finished so raise update event
_inputFileHistoryRow.NumberOfErrorRecords = _noOfErrorRecords;
_inputFileHistoryRow.NumberOfInformationalRecords = _noOfInformationalRecords;
_inputFileHistoryRow.NumberOfValidRecords = _noOfValidRecords;
_inputFileHistoryRow.NumberOfWarningRecords = _noOfWarningRecords;
_inputFileHistoryRow.NumberOfRecords = _recordCount;
inputFileHistoryRepository.Update(_inputFileHistoryRow);
inputFileHistoryRepository.SaveChanges();
CurrentStep.Finish(true);
}
catch (Exception ex)
{
_errorCode = 6;
throw new Exception(ex.Message, ex);
//_isBusinessValid = false;
//InputFileErrors.PublishError(ex.Message + Environment.NewLine + ex.StackTrace, 3, "System", _inputFileHistoryCode);
}
}
private void LoadSql(string category, string uniqueFilename, string connectionString)
{
List <MCSystemParameter> spList = new List <MCSystemParameter>(SystemParameterRepository.GetAll());
int ConsFileKeyColumn = Convert.ToInt32(spList.Find(x => x.Name == "ConsFileKeyColumn").ParameterValue);
int ConsFileNameColumn = Convert.ToInt32(spList.Find(x => x.Name == "ConsFileNameColumn").ParameterValue);
int WardFileKeyColumn = Convert.ToInt32(spList.Find(x => x.Name == "WardFileKeyColumn").ParameterValue);
int WardFileNameColumn = Convert.ToInt32(spList.Find(x => x.Name == "WardFileNameColumn").ParameterValue);
int LAFileKeyColumn = Convert.ToInt32(spList.Find(x => x.Name == "LAFileKeyColumn").ParameterValue);
int LAFileNameColumn = Convert.ToInt32(spList.Find(x => x.Name == "LAFileNameColumn").ParameterValue);
int LEAFileKeyColumn = Convert.ToInt32(spList.Find(x => x.Name == "LEAFileKeyColumn").ParameterValue);
int LEAFileNameColumn = Convert.ToInt32(spList.Find(x => x.Name == "LEAFileNameColumn").ParameterValue);
int RegionFileKeyColumn = Convert.ToInt32(spList.Find(x => x.Name == "RegionFileKeyColumn").ParameterValue);
int RegionFileNameColumn = Convert.ToInt32(spList.Find(x => x.Name == "RegionFileNameColumn").ParameterValue);
int CountyFileKeyColumn = Convert.ToInt32(spList.Find(x => x.Name == "CountyFileKeyColumn").ParameterValue);
int CountyFileNameColumn = Convert.ToInt32(spList.Find(x => x.Name == "CountyFileNameColumn").ParameterValue);
int PostCode7Column = Convert.ToInt32(spList.Find(x => x.Name == "PostCode7Column").ParameterValue);
int PostCode8Column = Convert.ToInt32(spList.Find(x => x.Name == "PostCode8Column").ParameterValue);
int PostCodeCountyColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeCountyColumn").ParameterValue);
int PostCodeLAColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeLAColumn").ParameterValue);
int PostCodeWardColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeWardColumn").ParameterValue);
int PostCodeRegionColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeRegionColumn").ParameterValue);
int PostCodeConsColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeConsColumn").ParameterValue);
int PostCodeLEAColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeLEAColumn").ParameterValue);
int PostCodeEastingColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeEastingColumn").ParameterValue);
int PostCodeNorthingColumn = Convert.ToInt32(spList.Find(x => x.Name == "PostCodeNorthingColumn").ParameterValue);
reader = null;
SqlBulkCopy Copier = new SqlBulkCopy(connectionString);
string prefix = @"c:\MyUploads\";
switch (category)
{
//case "LEA":
// CurrentStep = this["BulkUploadLEA"];
// Copier.DestinationTableName = "dbo.ImportLookUp";
// reader = new LookUpDataReader(category,uniqueFilename,0,1);
// break;
//case "LA":
// CurrentStep = this["BulkUploadLA"];
// Copier.DestinationTableName = "dbo.ImportLookUp";
// reader = new LookUpDataReader(category,uniqueFilename,0,2);
// break;
//case "Ward":
// CurrentStep = this["BulkUploadWARD"];
// Copier.DestinationTableName = "dbo.ImportLookUp";
// reader = new LookUpDataReader(category,uniqueFilename,0,2);
// break;
//case "Constituency":
// CurrentStep = this["BulkUploadWPC"];
// Copier.DestinationTableName = "dbo.ImportLookUp";
// reader = new LookUpDataReader(category,uniqueFilename,0,2);
// break;
//case "Region":
// CurrentStep = this["BulkUploadRegion"];
// Copier.DestinationTableName = "dbo.ImportLookUp";
// reader = new LookUpDataReader(category,uniqueFilename,0,2);
// break;
//case "County":
// CurrentStep = this["BulkUploadCty"];
// Copier.DestinationTableName = "dbo.ImportLookUp";
// reader = new LookUpDataReader(category,uniqueFilename,0,2);
// break;
case "PostCode":
CurrentStep = this["BulkUploadPC"];
Copier.DestinationTableName = "dbo.ImportPostCode";
reader = new PostCodeDataReader(category, uniqueFilename, PostCodeLEAColumn, PostCodeWardColumn, PostCodeRegionColumn, PostCodeConsColumn,
PostCodeLAColumn, PostCodeCountyColumn, PostCode7Column, PostCode8Column, PostCodeEastingColumn, PostCodeNorthingColumn,
prefix + _uniqueFilename[1], LAFileKeyColumn, LAFileNameColumn, //LA
prefix + _uniqueFilename[3], LEAFileKeyColumn, LEAFileNameColumn, //LEA
prefix + _uniqueFilename[2], CountyFileKeyColumn, CountyFileNameColumn, //County
prefix + _uniqueFilename[5], WardFileKeyColumn, WardFileNameColumn, //Ward
prefix + _uniqueFilename[4], RegionFileKeyColumn, RegionFileNameColumn, //Region
prefix + _uniqueFilename[0], ConsFileKeyColumn, ConsFileNameColumn //Constituency
);
break;
}
CurrentStep.Start();
CurrentStep.UpdateProgress(0, "Bulk uploading");
Copier.BatchSize = 5000;
Copier.NotifyAfter = 1000;
Copier.BulkCopyTimeout = 60;
Copier.SqlRowsCopied += new SqlRowsCopiedEventHandler(Copier_SqlRowsCopied);
Copier.WriteToServer(reader);
CurrentStep.Finish(true);
}
