public async Task ExecuteScalarWithInput()
{
bool result = await _databaseCommander.BuildCommand()
.ForScalar <bool>("SELECT [SampleBit] FROM [dbo].[SampleTable] WHERE [SampleTableID] = @SampleTableID AND [SampleVarChar] = @SampleVarChar")
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleVarChar", "Row 1")
.ExecuteAsync(new CancellationToken());
Console.WriteLine("Data: {0}", result);
}
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 = GetDataTableFromAccess();
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 ExecuteSqlWithInput()
{
SqlQueryResult result = await _databaseCommander.BuildCommand()
.ForSqlQuery("SELECT * FROM [dbo].[SampleTable] WHERE [SampleTableID] = @SampleTableID AND [SampleVarChar] = @SampleVarChar")
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleVarChar", "Row 1")
.ExecuteAsync(new CancellationToken());
int count = result.Count;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}
public async Task ExecuteParameterizedUpdateSql()
{
Guid newGuid = Guid.NewGuid();
string modifiedBy = "FluentCommander";
DateTime modifiedDate = DateTime.UtcNow;
SqlNonQueryResult result = await _databaseCommander.BuildCommand()
.ForSqlNonQuery("UPDATE [dbo].[SampleTable] SET [SampleUniqueIdentifier] = @NewGuid, [ModifiedBy] = @ModifiedBy, [ModifiedDate] = @ModifiedDate WHERE [SampleTableID] = @SampleTableID")
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("NewGuid", newGuid)
.AddInputParameter("ModifiedBy", modifiedBy)
.AddInputParameter("ModifiedDate", modifiedDate)
.ExecuteAsync(new CancellationToken());
int rowCountAffected = result.RowCountAffected;
Console.WriteLine("Row count affected: {0}", rowCountAffected);
}
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 BulkCopyUsingAutoMapping()
{
DataTable dataTable = GetDataToInsert();
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UseAutoMap())
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public async Task ExecuteStoredProcedureWithAllInputTypesAndTableResult()
{
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_AllInputTypes_NoOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleInt", 0)
.AddInputParameter("SampleSmallInt", 0)
.AddInputParameter("SampleTinyInt", 0)
.AddInputParameter("SampleBit", 0)
.AddInputParameter("SampleDecimal", 0)
.AddInputParameter("SampleFloat", 0)
.AddInputParameter("SampleDateTime", DateTime.Now)
.AddInputParameter("SampleUniqueIdentifier", Guid.NewGuid())
.AddInputParameter("SampleVarChar", "Row 1")
.ExecuteAsync(new CancellationToken());
int count = result.Count;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}
public async Task ExecutePaginationUsingMinimalInput()
{
PaginationResult result = await _databaseCommander.BuildCommand()
.ForPagination()
.From("[dbo].[SampleTable]")
.ExecuteAsync(new CancellationToken());
int count = result.Count;
int totalCount = result.TotalCount;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Total Row count: {0}", totalCount);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}