private void ProcessQueuedItem()
{
Exception errorState = null;
try
{
var settings = new ImportFileSettings()
{
SkipFirstXRows = ConfigurationSettings.GetInteger("SkipFirstXRowsInImportFile")
};
if (!(CurrentQueuedItem is EmptyImportQueue))
{
Result = DataContext.Import.ProcessImportQueue(CurrentQueuedItem, settings);
}
}
catch (Exception ex)
{
errorState = ex;
throw ex;
}
finally
{
if (errorState != null)
{
DataContext.Import.UpdateStatus(new ImportQueueFilter()
{
ImportQueueId = CurrentQueuedItem.ImportQueueId,
ImportStatus = enums.ImportStatus.Error,
ErrorMessage = errorState.Message
});
}
}
}
private void tsbExport_Click(object sender, EventArgs e)
{
var sfd = new SaveFileDialog {
Title = "Select where to save the file", Filter = "Csv file|*.csv"
};
if (sfd.ShowDialog(ParentForm) != DialogResult.OK)
{
return;
}
infoPanel = InformationPanel.GetInformationPanel(this, "Exporting many to many relationship records...", 340, 150);
listLog.Items.Clear();
var settings = new ImportFileSettings
{
FirstEntity = ((EntityInfo)cbbFirstEntity.SelectedItem).Metadata.LogicalName,
FirstAttributeIsGuid = rdbFirstGuid.Checked,
FirstAttributeName = ((AttributeInfo)cbbFirstEntityAttribute.SelectedItem).Metadata.LogicalName,
Relationship = ((RelationshipInfo)cbbRelationship.SelectedItem).Metadata.IntersectEntityName,
SecondEntity = ((EntityInfo)cbbSecondEntity.SelectedItem).Metadata.LogicalName,
SecondAttributeIsGuid = rdbSecondGuid.Checked,
SecondAttributeName = ((AttributeInfo)cbbSecondEntityAttribute.SelectedItem).Metadata.LogicalName,
};
var exportWorker = new BackgroundWorker();
exportWorker.DoWork += exportWorker_DoWork;
exportWorker.RunWorkerCompleted += exportWorker_RunWorkerCompleted;
exportWorker.RunWorkerAsync(new object[] { settings, sfd.FileName });
}
private void tsbImportNN_Click(object sender, EventArgs e)
{
listLog.Items.Clear();
var settings = new ImportFileSettings
{
FirstEntity = ((EntityInfo)cbbFirstEntity.SelectedItem).Metadata.LogicalName,
FirstAttributeIsGuid = rdbFirstGuid.Checked,
FirstAttributeName = ((AttributeInfo)cbbFirstEntityAttribute.SelectedItem).Metadata.LogicalName,
Relationship = ((RelationshipInfo)cbbRelationship.SelectedItem).Metadata.SchemaName,
SecondEntity = ((EntityInfo)cbbSecondEntity.SelectedItem).Metadata.LogicalName,
SecondAttributeIsGuid = rdbSecondGuid.Checked,
SecondAttributeName = ((AttributeInfo)cbbSecondEntityAttribute.SelectedItem).Metadata.LogicalName,
};
WorkAsync("Importing many to many relationships...",
evt =>
{
var innerSettings = (ImportFileSettings)((object[])evt.Argument)[0];
var filePath = ((object[])evt.Argument)[1].ToString();
var ie = new ImportEngine(filePath, this.Service, innerSettings);
ie.RaiseError += ie_RaiseError;
ie.RaiseSuccess += ie_RaiseSuccess;
ie.Import();
},
evt => {},
new object[] { settings, txtFilePath.Text });
}
private void tsbDelete_Click(object sender, EventArgs e)
{
if (txtFilePath.Text.Length == 0)
{
return;
}
listLog.Items.Clear();
var settings = new ImportFileSettings
{
FirstEntity = ((EntityInfo)cbbFirstEntity.SelectedItem).Metadata.LogicalName,
FirstAttributeIsGuid = rdbFirstGuid.Checked,
FirstAttributeName = ((AttributeInfo)cbbFirstEntityAttribute.SelectedItem).Metadata.LogicalName,
Relationship = ((RelationshipInfo)cbbRelationship.SelectedItem).Metadata.SchemaName,
SecondEntity = ((EntityInfo)cbbSecondEntity.SelectedItem).Metadata.LogicalName,
SecondAttributeIsGuid = rdbSecondGuid.Checked,
SecondAttributeName = ((AttributeInfo)cbbSecondEntityAttribute.SelectedItem).Metadata.LogicalName,
};
WorkAsync(new WorkAsyncInfo
{
Message = "Deleting many to many relationships...",
AsyncArgument = new object[] { settings, txtFilePath.Text },
Work = (bw, evt) =>
{
var innerSettings = (ImportFileSettings)((object[])evt.Argument)[0];
var filePath = ((object[])evt.Argument)[1].ToString();
var ie = new DeleteEngine(filePath, this.Service, innerSettings);
ie.RaiseError += ie_RaiseError;
ie.RaiseSuccess += ie_RaiseSuccess;
ie.Delete();
}
});
}
public ImportResult ProcessImportQueue(ImportQueue queuedItem, ImportFileSettings fileSettings)
{
var result = new ImportResult();
queuedItem.ImportData = GetImportFileAsDataTable(queuedItem, fileSettings);
// Validate the import data with some quick sanity checks
var validator = new ImportDataValidator();
var validationResult = validator.Validate(queuedItem);
if (!validationResult.IsValid)
{
throw new ValidationException(validationResult.Errors);
}
queuedItem = BulkImportDataTableToDataStore(queuedItem);
queuedItem = ProcessImportData(queuedItem);
//File.Delete(queuedItem.FilePath);
result.Status = queuedItem.ImportStatus;
result.TakeRateId = queuedItem.TakeRateId;
return(result);
}
public static DataTable ReadCsv(string filePath, ImportFileSettings settings)
{
var reader = new StreamReader(new FileStream(filePath, FileMode.Open));
var csvHelper = new CsvReader(reader);
var result = new DataTable();
// If the input file contains columns headings and such like, we can skip them
var skipRows = settings.SkipFirstXRows.GetValueOrDefault();
var hasRows = false;
for (var i = 0; i < skipRows; i++)
{
hasRows = csvHelper.Read();
}
var firstRow = true;
while (hasRows)
{
// Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (var cell in csvHelper.CurrentRecord)
{
result.Columns.Add(cell);
}
firstRow = false;
}
else
{
if (!string.IsNullOrEmpty(csvHelper.CurrentRecord.First()))
{
result.Rows.Add();
var i = 0;
foreach (var cell in csvHelper.CurrentRecord)
{
result.Rows[result.Rows.Count - 1][i] = cell;
i++;
}
}
}
hasRows = csvHelper.Read();
}
return result;
}
private void tsbExport_Click(object sender, EventArgs e)
{
var sfd = new SaveFileDialog {
Title = "Select where to save the file", Filter = "Csv file|*.csv"
};
if (sfd.ShowDialog(ParentForm) != DialogResult.OK)
{
return;
}
listLog.Items.Clear();
var settings = new ImportFileSettings
{
FirstEntity = ((EntityInfo)cbbFirstEntity.SelectedItem).Metadata.LogicalName,
FirstAttributeIsGuid = rdbFirstGuid.Checked,
FirstAttributeName = ((AttributeInfo)cbbFirstEntityAttribute.SelectedItem).Metadata.LogicalName,
Relationship = ((RelationshipInfo)cbbRelationship.SelectedItem).Metadata.IntersectEntityName,
SecondEntity = ((EntityInfo)cbbSecondEntity.SelectedItem).Metadata.LogicalName,
SecondAttributeIsGuid = rdbSecondGuid.Checked,
SecondAttributeName = ((AttributeInfo)cbbSecondEntityAttribute.SelectedItem).Metadata.LogicalName,
};
WorkAsync(new WorkAsyncInfo
{
Message = "Exporting many to many relationship records...",
AsyncArgument = new object[] { settings, sfd.FileName },
Work = (bw, evt) =>
{
var innerSettings = (ImportFileSettings)((object[])evt.Argument)[0];
var filePath = ((object[])evt.Argument)[1].ToString();
var ee = new ExportEngine(filePath, this.Service, innerSettings);
ee.RaiseError += ee_RaiseError;
ee.Export();
},
PostWorkCallBack = evt => { }
});
}
private void tsbImportNN_Click(object sender, EventArgs e)
{
infoPanel = InformationPanel.GetInformationPanel(this, "Importing many to many relationships...", 340, 150);
listLog.Items.Clear();
var settings = new ImportFileSettings
{
FirstEntity = ((EntityInfo)cbbFirstEntity.SelectedItem).Metadata.LogicalName,
FirstAttributeIsGuid = rdbFirstGuid.Checked,
FirstAttributeName = ((AttributeInfo)cbbFirstEntityAttribute.SelectedItem).Metadata.LogicalName,
Relationship = ((RelationshipInfo)cbbRelationship.SelectedItem).Metadata.SchemaName,
SecondEntity = ((EntityInfo)cbbSecondEntity.SelectedItem).Metadata.LogicalName,
SecondAttributeIsGuid = rdbSecondGuid.Checked,
SecondAttributeName = ((AttributeInfo)cbbSecondEntityAttribute.SelectedItem).Metadata.LogicalName,
};
var importWorker = new BackgroundWorker();
importWorker.DoWork += importWorker_DoWork;
importWorker.RunWorkerCompleted += importWorker_RunWorkerCompleted;
importWorker.RunWorkerAsync(new object[] { settings, txtFilePath.Text });
}
public static DataTable ReadExcelAsDataTable(string filePath, ImportFileSettings settings)
{
DataTable retVal = null;
var extension = Path.GetExtension(filePath);
if (string.IsNullOrEmpty(extension))
{
return retVal;
}
switch (extension.ToUpper())
{
case ".CSV":
retVal = ReadCsv(filePath, settings);
break;
case ".XLSX":
retVal = ReadExcel(filePath, settings);
break;
}
if (retVal == null) return retVal;
// Remove blank rows imported from the end of the file. Check the first two columns
//for (var i = retVal.Rows.Count - 1; i >= 0; i--)
//{
// if (retVal.Rows[i][0] == DBNull.Value && retVal.Rows[i][0] == DBNull.Value)
// {
// retVal.Rows[i].Delete();
// }
// else
// {
// break;
// }
//}
return retVal;
}
private static DataTable ReadExcel(string filePath, ImportFileSettings settings)
{
var result = new DataTable();
var firstRow = true;
var rowCount = 0;
using (var workBook = new XLWorkbook(filePath))
{
foreach (var row in workBook.Worksheets
.SelectMany(workSheet => workSheet.Rows())
.Skip(settings.SkipFirstXRows.GetValueOrDefault()))
{
// Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (var cell in row.Cells())
{
result.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
result.Rows.Add();
rowCount++;
var i = 0;
foreach (var cell in row.Cells())
{
result.Rows[rowCount - 1][i] = cell.Value.ToString();
i++;
}
}
}
}
rowCount = result.Rows.Count;
if (rowCount > 0 && string.IsNullOrEmpty(result.Rows[rowCount - 1][0].ToString()))
{
result.Rows.RemoveAt(rowCount - 1);
}
return result;
}
private static DataTable GetImportFileAsDataTable(ImportQueue queuedItem, ImportFileSettings settings)
{
return(ExcelReader.ReadExcelAsDataTable(queuedItem.FilePath, settings));
}
