public void ExecuteSqlNonQueryCommand_UpdateWithInputParameters_ShouldUpdateTheDatabase()
{
// Arrange
int sampleTableId = 1;
Guid newGuid = Guid.NewGuid();
Guid oldGuid = ExecuteScalar <Guid>($"SELECT [SampleUniqueIdentifier] FROM [dbo].[SampleTable] WHERE [SampleTableID] = {sampleTableId}");
// Act
SqlNonQueryResult result = SUT.BuildCommand()
.ForSqlNonQuery("UPDATE [dbo].[SampleTable] SET [SampleUniqueIdentifier] = @NewGuid, [ModifiedBy] = @ModifiedBy, [ModifiedDate] = @ModifiedDate WHERE [SampleTableID] = @SampleTableID")
.AddInputParameter("SampleTableID", sampleTableId)
.AddInputParameter("NewGuid", newGuid)
.AddInputParameter("ModifiedBy", TestUsername)
.AddInputParameter("ModifiedDate", Timestamp)
.Execute();
// Assert
result.ShouldNotBeNull();
Guid actualGuid = ExecuteScalar <Guid>($"SELECT [SampleUniqueIdentifier] FROM [dbo].[SampleTable] WHERE [SampleTableID] = {sampleTableId}");
actualGuid.ShouldBe(newGuid);
actualGuid.ShouldNotBe(oldGuid);
// Print result
WriteLine("Old GUID: {0}", oldGuid);
WriteLine("New GUID: {0}", newGuid);
}
public void ExecuteSqlNonQueryCommand_InsertDeleteWithInputParameters_ShouldInsertDeleteToTheDatabase()
{
// Arrange
string sampleVarChar = "Temporary Row";
// Act
string insertSql =
@"INSERT INTO [dbo].[SampleTable]
([SampleInt]
,[SampleSmallInt]
,[SampleTinyInt]
,[SampleBit]
,[SampleDecimal]
,[SampleFloat]
,[SampleVarChar]
,[CreatedBy]
,[CreatedDate])
VALUES
(1
,1
,1
,1
,1
,1
,@SampleVarChar
,@CreatedBy
,@CreatedDate)";
SqlNonQueryResult insertResult = SUT.BuildCommand()
.ForSqlNonQuery(insertSql)
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleVarChar", sampleVarChar)
.AddInputParameter("CreatedBy", TestUsername)
.AddInputParameter("CreatedDate", Timestamp)
.Execute();
ExecuteScalar <int>($"SELECT COUNT(1) FROM [dbo].[SampleTable] WHERE [SampleVarChar] = '{sampleVarChar}'").ShouldBe(1);
SqlNonQueryResult deleteResult = SUT.BuildCommand()
.ForSqlNonQuery("DELETE FROM [dbo].[SampleTable] WHERE [SampleVarChar] = @SampleVarChar")
.AddInputParameter("SampleVarChar", sampleVarChar)
.Execute();
// Assert
insertResult.ShouldNotBeNull();
insertResult.RowCountAffected.ShouldBe(1);
deleteResult.ShouldNotBeNull();
deleteResult.RowCountAffected.ShouldBe(1);
ExecuteScalar <int>($"SELECT COUNT(1) FROM [dbo].[SampleTable] WHERE [SampleVarChar] = '{sampleVarChar}'").ShouldBe(0);
// Print result
WriteLine("Insert Row Count Affected: {0}", insertResult.RowCountAffected);
WriteLine("Delete Row Count Affected: {0}", deleteResult.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 ExecuteParameterizedInsertDeleteSql()
{
string sampleVarChar = "Temporary Row";
string createdBy = "FluentCommander";
DateTime createdDate = DateTime.UtcNow;
string insertSql =
@"INSERT INTO [dbo].[SampleTable]
([SampleInt]
,[SampleSmallInt]
,[SampleTinyInt]
,[SampleBit]
,[SampleDecimal]
,[SampleFloat]
,[SampleVarChar]
,[CreatedBy]
,[CreatedDate])
VALUES
(1
,1
,1
,1
,1
,1
,@SampleVarChar
,@CreatedBy
,@CreatedDate)";
SqlNonQueryResult insertResult = await _databaseCommander.BuildCommand()
.ForSqlNonQuery(insertSql)
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleVarChar", sampleVarChar)
.AddInputParameter("CreatedBy", createdBy)
.AddInputParameter("CreatedDate", createdDate)
.ExecuteAsync(new CancellationToken());
SqlNonQueryResult deleteResult = await _databaseCommander.BuildCommand()
.ForSqlNonQuery("DELETE FROM [dbo].[SampleTable] WHERE [SampleVarChar] = @SampleVarChar")
.AddInputParameter("SampleVarChar", sampleVarChar)
.ExecuteAsync(new CancellationToken());
int rowCountAffectedFromInsert = insertResult.RowCountAffected;
int rowCountAffectedFromDelete = deleteResult.RowCountAffected;
Console.WriteLine("Row count affected from Insert: {0}", rowCountAffectedFromInsert);
Console.WriteLine("Row count affected from Delete: {0}", rowCountAffectedFromDelete);
}
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);
}
