public override ImportRowSource CreateRowSource(IProgressObserver progress)
{
if (_options == null)
{
throw new Exception("Null or incorrect options type received!");
}
ImportRowSource rowsource = null;
using (var parser = new GenericParserAdapter(_options.Filename)) {
parser.ColumnDelimiter = _options.Delimiter[0];
parser.FirstRowHasHeader = _options.FirstRowContainsNames;
parser.TextQualifier = '\"';
parser.FirstRowSetsExpectedColumnCount = true;
var service = new ImportStagingService();
var columnNames = new List <String>();
int rowCount = 0;
service.BeginTransaction();
var values = new List <string>();
while (parser.Read())
{
if (rowCount == 0)
{
for (int i = 0; i < parser.ColumnCount; ++i)
{
if (_options.FirstRowContainsNames)
{
columnNames.Add(parser.GetColumnName(i));
}
else
{
columnNames.Add("Column" + i);
}
}
service.CreateImportTable(columnNames);
}
values.Clear();
for (int i = 0; i < parser.ColumnCount; ++i)
{
values.Add(parser[i]);
}
service.InsertImportRow(values);
rowCount++;
}
service.CommitTransaction();
rowsource = new ImportRowSource(service, rowCount);
}
return(rowsource);
}
private bool Validate()
{
if (string.IsNullOrWhiteSpace(txtFilename.Text))
{
ErrorMessage.Show("You must specify an input file!");
return(false);
}
if (!File.Exists(txtFilename.Text))
{
ErrorMessage.Show("File does not exist!");
return(false);
}
// Extract Column Headers...
ColumnNames = new List <String>();
GenericParser parser = null;
try {
using (parser = new GenericParserAdapter(Filename)) {
parser.ColumnDelimiter = Delimiter.ToCharArray()[0];
parser.FirstRowHasHeader = IsFirstRowContainNames;
parser.MaxRows = 2;
parser.TextQualifier = _textQualifier;
if (parser.Read())
{
for (int i = 0; i < parser.ColumnCount; ++i)
{
if (IsFirstRowContainNames)
{
ColumnNames.Add(parser.GetColumnName(i));
}
else
{
ColumnNames.Add("Column" + i);
}
}
}
else
{
ErrorMessage.Show("Failed to extract column names from data source!");
return(false);
}
}
} finally {
if (parser != null)
{
System.GC.SuppressFinalize(parser);
}
}
return(true);
}
private void btnBrowseForDataFile_Click(object sender, EventArgs e)
{
diagOpenFile.ShowDialog();
if (!String.IsNullOrEmpty(diagOpenFile.FileName))
{
txtDataFile.Text = diagOpenFile.FileName;
using (GenericParserAdapter parser = new GenericParserAdapter(diagOpenFile.FileName))
{
parser.FirstRowHasHeader = true;
parser.Read();
sourceData = parser.GetDataTable();
}
foreach (DataColumn column in sourceData.Columns)
{
cboColumnNames.Items.Add(column.ColumnName);
}
}
}
protected void IterateOverCSVZipEntry(ZipEntry entry, Func <GenericParserAdapter, bool> func)
{
using (var reader = new StreamReader(entry.OpenReader())) {
using (var parser = new GenericParserAdapter(reader)) {
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
parser.TextQualifier = '\"';
parser.FirstRowSetsExpectedColumnCount = true;
while (parser.Read())
{
if (func != null)
{
if (!func(parser))
{
return;
}
}
}
}
}
}
public async Task BulkLoadFile(string databaseName, string tableName, string csvFilePath)
{
// Take care of the SQL Connection
SqlConnectionStringBuilder sqlStringBuilder =
new SqlConnectionStringBuilder(_sqlConnectionString)
{
InitialCatalog = databaseName
};
var sqlCnn = new SqlConnection(sqlStringBuilder.ConnectionString);
sqlCnn.Open();
// Create a data table based on the structure of the table we want to bulk insert into
// Use 1=2 as a quick way to keep the table empty
var thisAdapter = new SqlDataAdapter();
var tableSelectSqlCmd = "Select * from {0} where 1=2";
thisAdapter.SelectCommand = new SqlCommand(String.Format(tableSelectSqlCmd, tableName), sqlCnn);
// Use a DataTable as a layer in between reading the CSV file and Bulk Inserting
// Bulk insert does not support quoted CSV etc
// Using a CSV parser + a DataTable to provide a streaming interface to SqlBulkCopy is a way around this
var thisDt = new DataTable();
thisAdapter.Fill(thisDt);
// keep a copy of the data table structure
var templateDt = thisDt.Clone();
var batchTrack = 0;
try
{
using (var csvParser = new GenericParserAdapter(csvFilePath))
{
csvParser.FirstRowHasHeader = false;
csvParser.MaxBufferSize = CSV_PARSER_BUFFER_SIZE;
// iterate through the CSV file
while (csvParser.Read())
{
// write out a batch from the data table
if (batchTrack == DATATABLE_BATCH_SIZE)
{
await BulkInsertDataTable(sqlCnn, thisDt, tableName);
thisDt.Dispose();
thisDt = null;
// reset the data table to the template
thisDt = templateDt.Clone();
batchTrack = 0;
}
// Parse each column of the row based on tables meta data
// Assumes the table structure of the Target DB and incoming CSV files match
// Ensure Null values are converted to DBNull
var thisDr = thisDt.NewRow();
for (var i = 2; i < csvParser.ColumnCount; i++)
{
var targetCol = i - 2;
thisDr[targetCol] = ProcessColumn(csvParser[i], thisDt.Columns[targetCol].DataType);
}
thisDt.Rows.Add(thisDr);
batchTrack++;
}
// Write out the final batch
await BulkInsertDataTable(sqlCnn, thisDt, tableName);
// Cleanup
thisAdapter.Dispose();
thisDt.Dispose();
thisDt = null;
}
sqlCnn.Close();
sqlCnn.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public async Task BulkLoadAndUpsertFile(string databaseName, string tableName, string csvFilePath)
{
// Take care of the SQL Connection
SqlConnectionStringBuilder sqlStringBuilder =
new SqlConnectionStringBuilder(_sqlConnectionString)
{
InitialCatalog = databaseName
};
var sqlCnn = new SqlConnection(sqlStringBuilder.ConnectionString);
sqlCnn.Open();
CreateCtrlTbl(sqlCnn, databaseName);
var columnList = CreateTempTable(sqlCnn, databaseName, tableName);
// Create a data table based on the structure of the tmp table we want to bulk insert into
// Use 1=2 as a quick way to keep the table empty
var thisAdapter = new SqlDataAdapter();
var thisSql = "Select * from {0} where 1=2";
thisAdapter.SelectCommand = new SqlCommand(String.Format(thisSql, "sqlTmp"), sqlCnn);
// Use a DataTable as a layer in between reading the CSV file and Bulk Inserting
// Bulk insert does not support quoted CSV etc
// Using a CSV parser + a DataTable to provide a streaming interface to SqlBulkCopy is a way around this
var thisDt = new DataTable();
thisAdapter.Fill(thisDt);
// keep a copy of the data table structure
var templateDt = thisDt.Clone();
var batchTrack = 0;
var tmpIndexCnt = 0;
var cntDelLines = 0;
var cntInsLines = 0;
var cntUpdLines = 0;
try
{
using (var parser = new GenericParserAdapter(csvFilePath))
{
parser.FirstRowHasHeader = false;
parser.MaxBufferSize = CSV_PARSER_BUFFER_SIZE;
// iterate through the CSV file
while (parser.Read())
{
// write out a batch from the data table
if (batchTrack == DATATABLE_BATCH_SIZE)
{
//Console.WriteLine("Task ID:::" + taskId + "(" + tableName + ")" + "Writing out a batch of " + batchTrack);
await BulkInsertDataTable(sqlCnn, thisDt, "sqlTmp");
thisDt.Dispose();
thisDt = null;
// reset the data table to the template
thisDt = templateDt.Clone();
batchTrack = 0;
}
// Parse each column of the row based on tables meta data
// Assumes the table structure of the Target DB and incoming CSV files match
// Ensure Null values are converted to DBNull
var thisDr = thisDt.NewRow();
// populate additional key to track records uniquely in the sqlTmp table
thisDr[0] = tmpIndexCnt;
for (var i = 0; i < parser.ColumnCount; i++)
{
thisDr[i + 1] = ProcessColumn(parser[i], thisDt.Columns[i + 1].DataType);
}
thisDt.Rows.Add(thisDr);
batchTrack++;
tmpIndexCnt++;
// keep track of record counts by operation
if (thisDr[1].Equals("I"))
{
cntInsLines++;
}
else if (thisDr[1].Equals("U"))
{
cntUpdLines++;
}
else if (thisDr[1].Equals("D"))
{
cntDelLines++;
}
else
{
//Should never get here?!
}
}
// Write out the final batch
await BulkInsertDataTable(sqlCnn, thisDt, "sqlTmp");
// Cleanup
thisAdapter.Dispose();
thisDt.Dispose();
thisDt = null;
}
// Do a form of UPSERT based on the incoming file having
// I = insert
// U = update
// D = delete
// first delete records matching any operation in target table
var delCmd =
"DELETE TARGET FROM dbo.{0} as TARGET INNER JOIN dbo.sqlTmp SOURCE on TARGET.{1} = SOURCE.{2} and SOURCE.[oper] in ('I','U',\'D\')";
await using (var cmd = new SqlCommand(String.Format(delCmd, tableName, columnList[0], columnList[0]),
sqlCnn))
{
cmd.CommandTimeout = 120;
cmd.ExecuteNonQuery();
}
// now insert U and I rows only
var insertCmd =
"INSERT INTO dbo.{0} ({1}) SELECT {2} FROM sqlTmp SOURCE where SOURCE.oper in ('I', 'U') and SOURCE.[index] = (select Max(SOURCE2.[index]) from sqlTmp SOURCE2 where SOURCE2.id = SOURCE.id)";
await using (var cmd =
new SqlCommand(
String.Format(insertCmd, tableName, string.Join(",", columnList), string.Join(",", columnList)),
sqlCnn))
{
cmd.CommandTimeout = 120;
cmd.ExecuteNonQuery();
}
// finally insert a control line
var ctrlInsertCmd =
"INSERT INTO dbo.repLog ([file],[table],[delCnt],[insCnt],[updCnt]) VALUES ('{0}','{1}','{2}','{3}','{4}')";
await using (var cmd = new SqlCommand(String.Format(ctrlInsertCmd, Path.GetFileName(csvFilePath), tableName, cntDelLines, cntInsLines, cntUpdLines), sqlCnn))
{
cmd.CommandTimeout = 120;
cmd.ExecuteNonQuery();
}
sqlCnn.Close();
sqlCnn.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e);
}
//var endDttm = DateTime.Now;
//var span = endDttm.Subtract(startDttm);
//Console.WriteLine(endDttm + ":: Finished Task ID:::" + taskId + "(" + tableName + ")::: " + "Duration (minutes): " + span.TotalMinutes);
}
/// <summary>
/// Read Data from selected csv file
/// </summary>
public void Parse()
{
if (_data != null)
{
return;
}
try
{
_data = new DataTable();
var csv = FromK2File(_settings.File);
using (var textReader = new StringReader(csv))
using (var parser = new GenericParserAdapter(textReader))
{
parser.ColumnDelimiter = _settings.ColumnDelimiter;
parser.TextQualifier = _settings.TextQualifier;
parser.FirstRowHasHeader = true;
parser.StripControlChars = true;
var isFirstRow = true;
while (parser.Read())
{
if (isFirstRow)
{
for (var i = 0; i < parser.ColumnCount; i++)
{
var columnName = parser.GetColumnName(i).FormatColumnName(_settings.HeaderRowSpaces);
_data.Columns.Add(columnName);
}
isFirstRow = false;
}
var newRow = _data.NewRow();
for (var i = 0; i < parser.ColumnCount; i++)
{
newRow[i] = parser[i];
}
_data.Rows.Add(newRow);
}
}
if (_data.Columns.Count == 0)
{
Status = ImportStatus.NoColumnsFound;
Message = "No columns found.";
}
else if (_data.Rows.Count == 0)
{
Status = ImportStatus.NoRowsFound;
Message = "No rows found.";
}
else
{
Status = ImportStatus.Complete;
Message =
$"{_data.Columns.Count} columns found: " +
$"'{string.Join("', '", _data.Columns.Cast<DataColumn>().Select(c => c.ColumnName))}'. " +
$"{_data.Rows.Count} rows parsed for import. ";
}
}
catch (Exception ex)
{
Status = ImportStatus.Error;
Message = ex.Message;
}
}
private void DoImport()
{
if (String.IsNullOrEmpty(txtFilename.Text))
{
ErrorMessage.Show("You must select a file before proceeding!");
return;
}
int rowCount = 0;
var service = new SupportService(User);
using (var parser = new GenericParserAdapter(txtFilename.Text)) {
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = chkFirstRowHeaders.IsChecked.GetValueOrDefault(false);
parser.TextQualifier = '\"';
parser.FirstRowSetsExpectedColumnCount = true;
var columnNames = new List <String>();
var values = new List <string>();
while (parser.Read())
{
if (rowCount == 0)
{
for (int i = 0; i < parser.ColumnCount; ++i)
{
if (parser.FirstRowHasHeader)
{
columnNames.Add(parser.GetColumnName(i));
}
else
{
columnNames.Add("Column" + i);
}
}
}
values.Clear();
for (int i = 0; i < parser.ColumnCount; ++i)
{
values.Add(parser[i]);
}
String strFullPath = null;
if (values.Count == 0)
{
strFullPath = values[0];
}
else
{
strFullPath = values.Join("\\");
}
if (!String.IsNullOrWhiteSpace(strFullPath))
{
service.GetDistributionIdFromPath(strFullPath);
lblProgress.InvokeIfRequired(() => {
lblProgress.Content = strFullPath;
lblProgress.UpdateLayout();
});
rowCount++;
}
}
}
lblProgress.InvokeIfRequired(() => {
lblProgress.Content = String.Format("{0} rows processed.", rowCount);
});
btnCancel.Content = "_Close";
}
