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 async Task ProcessAsync(CancellationToken cancellationToken)
{
DataTable dataTable = _delimitedFileHandler.GetFileAsDataTable(_filePath);
var errorList = new List <string>();
foreach (DataRow dataRow in dataTable.Rows)
{
string inputAddress = dataRow.GetStringOrNull("Address");
string inputCity = dataRow.GetStringOrNull("City");
string inputState = dataRow.GetStringOrNull("State");
string inputZip = dataRow.GetStringOrNull("Zip");
if (inputAddress.Split(',').Count() > 2)
{
errorList.Add($"Could not resolve inputAddress of {inputAddress} due to too many commas");
}
else
{
string inputAddressLine2 = string.Empty;
string[] addressArray = inputAddress.Split(',');
if (addressArray.Count() == 2)
{
inputAddress = addressArray[0].Trim();
inputAddressLine2 = addressArray[1].Trim();
}
string sql = GetSql(inputAddressLine2);
SqlNonQueryResult result = await _databaseCommander.BuildCommand()
.ForSqlNonQuery(sql)
.AddInputParameter("inputAddress", inputAddress)
.AddInputParameter("inputAddressLine2", inputAddressLine2)
.AddInputParameter("inputCity", inputCity)
.AddInputParameter("inputState", inputState)
.ExecuteAsync(cancellationToken);
if (result.RowCountAffected == 0)
{
errorList.Add($"The following address was not found: inputAddress: {inputAddress}, inputAddressLine2: {inputAddressLine2}, inputCity: {inputCity}, inputState: {inputState}, inputZip: {inputZip}");
}
}
}
if (errorList.Any())
{
throw new Exception("Could not process: " + string.Join(Environment.NewLine, errorList));
}
}
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");
}
}