public async Task ProcessAsync(CancellationToken cancellationToken)
{
bool tableExists = DatabaseCommander.ExecuteScalar <int>($"SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{Settings.SchemaName}' AND TABLE_NAME = '{Settings.TableName}'") > 0;
if (tableExists)
{
throw new Exception($"Table {Settings.TableNameQualified} already exists");
}
DataTable dataTable = _delimitedFileHandler.GetFileAsDataTable(Settings.FilePath);
PrependPrimaryKeyIfNeeded(dataTable);
AppendAuditFieldsIfNeeded(dataTable);
SetColumnsToCreate(dataTable);
CreateTable();
await DatabaseCommander.BuildCommand()
.ForBulkCopy()
.Into(Settings.TableNameQualified)
.From(dataTable)
.Mapping(mapping => mapping.UseAutoMap())
.ExecuteAsync(cancellationToken);
}
public override void Run()
{
base.Run();
var connection = new NpgsqlConnection(_ConnectionString);
_Database = new DatabaseCommander(connection);
}
void Disconnect()
{
if (_Database != null)
{
_Database.Dispose();
_Database = null;
}
}
public override void Dispose()
{
if (_Database != null)
{
_Database.Dispose();
_Database = null;
}
base.Dispose();
}
public void Run()
{
while (true)
{
try
{
var connection = new NpgsqlConnection(_ConnectionString);
_Database = new DatabaseCommander(connection);
var files = Directory.GetFiles(_LogPath);
foreach (var file in files)
{
ProcessLog(file);
}
}
catch (Exception exception)
{
Console.WriteLine("Failed to update database: {0} ({1})", exception.Message, exception.GetType());
}
Disconnect();
Thread.Sleep(UpdateInterval);
}
}
public async Task ProcessAsync(CancellationToken cancellationToken)
{
//await DatabaseCommander.ExecuteNonQueryAsync($"DELETE FROM {Settings.TableNameQualified}", cancellationToken);
DataTable dataTable = _delimitedFileHandler.GetFileAsDataTable(Settings.FilePath, '~');
await DatabaseCommander.BuildCommand()
.ForBulkCopy()
.Into(Settings.TableNameQualified)
.From(dataTable)
.Mapping(mapping => mapping.UseAutoMap())
.ExecuteAsync(cancellationToken);
StoredProcedureResult result = await DatabaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[LoadData]")
.AddInputParameter("DataFile", Settings.FilePath)
.ExecuteAsync(cancellationToken);
if (result.GetReturnParameter <string>() == "-1")
{
throw new Exception("[dbo].[LoadData] encountered error");
}
}
public ColumnMapping MapDataTableToTable(string tableName, DataTable dataTable)
{
DataTable emptyDataTable = DatabaseCommander.ExecuteSql($"SELECT * FROM {tableName} WHERE 1 = 0");
List <string> databaseColumnNames = emptyDataTable.Columns.Cast <DataColumn>().Select(dc => dc.ColumnName).ToList();
List <string> fileColumnNames = dataTable.Columns.Cast <DataColumn>().Select(dc => dc.ColumnName).ToList();
var columnMapping = new ColumnMapping();
// Ensure proper casing of source columns
foreach (string fileColumnName in fileColumnNames)
{
ColumnMap columnMap = columnMapping.ColumnMaps.FirstOrDefault(cm => cm.Source.Equals(fileColumnName, StringComparison.InvariantCultureIgnoreCase));
if (columnMap != null && columnMap.Source != fileColumnName)
{
columnMap.Source = fileColumnName;
}
}
// Ensure proper casing of destination columns
foreach (string databaseColumnName in databaseColumnNames)
{
ColumnMap columnMap = columnMapping.ColumnMaps.FirstOrDefault(cm => cm.Destination.Equals(databaseColumnName, StringComparison.InvariantCultureIgnoreCase));
if (columnMap != null && columnMap.Destination != databaseColumnName)
{
columnMap.Destination = databaseColumnName;
}
}
// Ensure existence of source columns
foreach (string sourceColumnName in columnMapping.ColumnMaps.Select(cm => cm.Source))
{
if (!fileColumnNames.Contains(sourceColumnName))
{
throw new InvalidOperationException($"The source column '{sourceColumnName}' does not exist");
}
}
// Ensure existence of destination columns
foreach (string destinationColumnName in columnMapping.ColumnMaps.Select(cm => cm.Destination))
{
if (!databaseColumnNames.Contains(destinationColumnName))
{
throw new InvalidOperationException($"The destination column '{destinationColumnName}' does not exist");
}
}
// AutoMap any columns that are not already mapped from the source to the destination
foreach (string fileColumnName in fileColumnNames)
{
string databaseColumnName = databaseColumnNames.FirstOrDefault(s => s.Equals(fileColumnName, StringComparison.InvariantCultureIgnoreCase));
if (databaseColumnName != null && columnMapping.ColumnMaps.All(cm => cm.Source != fileColumnName) && columnMapping.ColumnMaps.All(cm => cm.Destination != databaseColumnName))
{
columnMapping.ColumnMaps.Add(new ColumnMap(fileColumnName, databaseColumnName));
}
}
return(columnMapping);
}
