public async Task DeleteWithConditionThatCapturesNothingTests(Type domainType)
{
var adoDaoInstance = GetAdoDaoInstance(domainType);
var getAllMethod = GetAdoDaoMethodInfo(
domainType,
"GetAllConditionalAsync");
var condition = new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.GreaterThan, 50000)
.Build();
var getAllParameterList = new object[] { condition };
var allCapturedDomainObjectsDynamic = await(dynamic) getAllMethod.Invoke(adoDaoInstance, getAllParameterList);
var allCapturedDomainObjects = (IEnumerable <object>)allCapturedDomainObjectsDynamic;
Assert.Empty(allCapturedDomainObjects);
var deleteMethod = GetAdoDaoMethodInfo(
domainType,
"DeleteAsync",
new Type[] { typeof(IQueryCondition) });
var deleteParameters = new object[] { condition };
var affectedRows = (int)await(dynamic) deleteMethod.Invoke(adoDaoInstance, deleteParameters);
Assert.Equal(0, affectedRows);
}
private static IQueryCondition GenerateQueryConditionsForRuntimeType(Type currentDomainType)
{
QueryConditionBuilder conditionBuilder = null;
var testableDomainObject = GenerateTestableCompareObject(currentDomainType);
foreach (var currentProperty in currentDomainType.GetProperties())
{
var newQueryConditionBuilder = new QueryConditionBuilder()
.DeclareCondition(
currentProperty.Name,
QueryConditionType.Equals,
currentProperty.GetValue(testableDomainObject));
if (conditionBuilder == null)
{
conditionBuilder = newQueryConditionBuilder;
}
else
{
conditionBuilder = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => conditionBuilder,
() => newQueryConditionBuilder);
}
}
return(conditionBuilder.Build());
}
public void QueryConditionWithDifferentTypesTests(
string columnToCheck,
QueryConditionType conditionType,
object expectedValue,
string expectedConditionStringFormat)
{
var condition = new QueryConditionBuilder()
.DeclareCondition(columnToCheck, conditionType, expectedValue)
.Build();
var actualConditionStringBuilder = new StringBuilder();
var actualQueryParameters = new List <QueryParameter>();
condition.AppendTo(actualConditionStringBuilder, actualQueryParameters);
string expectedQueryParameterName = $"{columnToCheck}0";
string expectedConditionString = string.Format(expectedConditionStringFormat, expectedQueryParameterName);
Assert.Equal(expectedConditionString, actualConditionStringBuilder.ToString());
Assert.Single(actualQueryParameters);
var actualQueryParam = actualQueryParameters.First();
Assert.Equal(expectedQueryParameterName, actualQueryParam.ParameterName);
Assert.Equal(expectedValue, actualQueryParam.Value);
}
public void ConditionFromBuilderSetTest()
{
var expectedColumn = "Id";
var numberSource = new List <int> {
1, 2, 3, 4
};
var conditionSet = numberSource
.Select(n => new QueryConditionBuilder()
.DeclareCondition(expectedColumn, QueryConditionType.Equals, n));
var condition = new QueryConditionBuilder()
.DeclareConditionFromBuilderSet(QueryConditionNodeType.And, conditionSet)
.Build();
var conditionStringBuilder = new StringBuilder();
var parameterSet = new List <QueryParameter>();
condition.AppendTo(conditionStringBuilder, parameterSet);
var conditionStr = conditionStringBuilder.ToString();
for (int i = 0; i < conditionSet.Count(); i++)
{
Assert.Contains($"[{expectedColumn}] = @{expectedColumn}{i}", conditionStr, StringComparison.Ordinal);
}
foreach (var number in numberSource)
{
Assert.True(parameterSet.First(p => (int)p.Value == number) != null);
}
}
public void GenerateDeleteWithConditionsParameterTest()
{
var condition = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.NotEquals, "Stevenie"),
() => new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder()
.DeclareCondition("LastName", QueryConditionType.NotEquals, "Zeitlhofinger"),
() => new QueryConditionBuilder()
.DeclareCondition("LastName", QueryConditionType.Like, "Halbmayr")))
.Build();
string expectedQuery = "DELETE FROM [Hurace].[Skier] " +
"WHERE ([FirstName] != @FirstName0 AND ([LastName] != @LastName0 OR [LastName] LIKE @LastName1))";
var queryGenerator = new SqlQueryGenerator <Entities.Skier>();
(var actualQuery, var actualQueryParameters) = queryGenerator.GenerateDeleteQuery(condition);
Assert.Equal(expectedQuery, actualQuery);
Assert.Equal(3, actualQueryParameters.Length);
var firstNameParameter = actualQueryParameters.First(qp => qp.ParameterName == "FirstName0");
var lastNameParameter1 = actualQueryParameters.First(qp => qp.ParameterName == "LastName0");
var lastNameParameter2 = actualQueryParameters.First(qp => qp.ParameterName == "LastName1");
Assert.Equal("Stevenie", firstNameParameter.Value);
Assert.Equal("Zeitlhofinger", lastNameParameter1.Value);
Assert.Equal("Halbmayr", lastNameParameter2.Value);
}
public void QueryConditionWithDateTimeTests(
QueryConditionType conditionType,
string expectedConditionStringFormat)
{
var columnToCheck = "DateOfBirth";
var expectedValue = new DateTime(2000, 1, 1);
var condition = new QueryConditionBuilder()
.DeclareCondition(columnToCheck, conditionType, expectedValue)
.Build();
var actualConditionStringBuilder = new StringBuilder();
var actualQueryParameters = new List <QueryParameter>();
condition.AppendTo(actualConditionStringBuilder, actualQueryParameters);
string expectedQueryParameterName = $"{columnToCheck}0";
string expectedConditionString = string.Format(expectedConditionStringFormat, expectedQueryParameterName);
Assert.Equal(expectedConditionString, actualConditionStringBuilder.ToString());
Assert.Single(actualQueryParameters);
var actualQueryParam = actualQueryParameters.First();
Assert.Equal(expectedQueryParameterName, actualQueryParam.ParameterName);
Assert.Equal(expectedValue.ToString("s"), actualQueryParam.Value);
}
public void ContractionConditionTest()
{
var queryBuilder = new StringBuilder();
var condition = new QueryConditionBuilder()
.DeclareContradictingCondition()
.Build();
condition.AppendTo(queryBuilder, null);
Assert.Equal("1 = 0", queryBuilder.ToString());
}
public void TautologyConditionTest()
{
var queryBuilder = new StringBuilder();
var condition = new QueryConditionBuilder()
.DeclareTautologyCondition()
.Build();
condition.AppendTo(queryBuilder, null);
Assert.Equal("1 = 1", queryBuilder.ToString());
}
public void GenerateSelectQueryWithConditionsTest()
{
string expectedQueryFormat =
"SELECT [FirstName], [LastName], [DateOfBirth], [ImageUrl], [CountryId], [SexId], [IsRemoved], [Id] " +
"FROM [Hurace].[Skier] " +
"WHERE ([Id] != @{0} AND ([FirstName] = @{1} OR [FirstName] = @{2}))";
var queryGenerator = new SqlQueryGenerator <Entities.Skier>();
var idExpectedValue = 15;
var firstName1ExpectedValue = "Marcel";
var firstName2ExpectedValue = "Viktoria";
var conditions = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => new QueryConditionBuilder().DeclareCondition("Id", QueryConditionType.NotEquals, idExpectedValue),
() => new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, firstName1ExpectedValue),
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, firstName2ExpectedValue)))
.Build();
(var actualQuery, var queryParameters) = queryGenerator.GenerateSelectQuery(conditions);
var idParameter =
queryParameters.First(
qp => qp.ParameterName.Contains("Id0", StringComparison.OrdinalIgnoreCase));
var firstName1Parameter =
queryParameters.First(
qp => qp.ParameterName.Contains("FirstName0", StringComparison.OrdinalIgnoreCase));
var firstName2Parameter =
queryParameters.First(
qp => qp.ParameterName.Contains("FirstName1", StringComparison.OrdinalIgnoreCase));
string expectedQuery = string.Format(
expectedQueryFormat,
idParameter.ParameterName,
firstName1Parameter.ParameterName,
firstName2Parameter.ParameterName);
Assert.Equal(expectedQuery, actualQuery);
Assert.Equal(idExpectedValue, idParameter.Value);
Assert.Equal(firstName1ExpectedValue, firstName1Parameter.Value);
Assert.Equal(firstName2ExpectedValue, firstName2Parameter.Value);
}
public async Task UpdateMultipleDomainObjectsTests()
{
var raceDao = new GenericDao <Entities.Race>(new DefaultConnectionFactory());
var comparisonRaceIds = new int[] { 10, 20, 30 };
var updateCondition = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.Equals, comparisonRaceIds[0]),
() => new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.Equals, comparisonRaceIds[1]),
() => new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.Equals, comparisonRaceIds[2])))
.Build();
var expectedNumberOfSensors = 10;
var expectedDate = DateTime.Now.Date.AddYears(1);
foreach (var raceId in comparisonRaceIds)
{
var raceEntity = await raceDao.GetByIdAsync(raceId).ConfigureAwait(false);
Assert.NotEqual(expectedNumberOfSensors, raceEntity.NumberOfSensors);
Assert.NotEqual(expectedDate, raceEntity.Date);
}
var updateColumns = new
{
NumberOfSensors = expectedNumberOfSensors,
Date = expectedDate
};
var affectedRows = await raceDao.UpdateAsync(updateColumns, updateCondition).ConfigureAwait(false);
Assert.Equal(comparisonRaceIds.Length, affectedRows);
foreach (var raceId in comparisonRaceIds)
{
var raceEntity = await raceDao.GetByIdAsync(raceId).ConfigureAwait(false);
Assert.Equal(expectedNumberOfSensors, raceEntity.NumberOfSensors);
Assert.Equal(expectedDate, raceEntity.Date);
}
}
private QueryConditionBuilder GetQueryConditionBuilder()
{
this.ConditionBuilder = new QueryConditionBuilder();
foreach (DataGridViewRow row in this.dgvFilter.Rows)
{
QueryConditionItem condition = row.Tag as QueryConditionItem;
if (condition != null)
{
this.ConditionBuilder.Add(condition);
}
}
return(this.ConditionBuilder);
}
public async Task UpdateWithIdUpdateTests(Type domainType)
{
var adoDaoInstance = GetAdoDaoInstance(domainType);
var updateMethod = GetAdoDaoMethodInfo(
domainType,
"UpdateAsync",
new Type[] { typeof(object), typeof(IQueryCondition) });
var condition = new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.Equals, 0)
.Build();
var parameterList = new object[] { new { Id = 0 }, condition };
await Assert.ThrowsAsync <InvalidOperationException>(
() => (dynamic)updateMethod.Invoke(adoDaoInstance, parameterList))
.ConfigureAwait(false);
}
public void BasicQueryConditionNodeTests(
QueryConditionNodeType combinationType,
string expectedConditionStringFormat)
{
var firstConditionBuilder = new QueryConditionBuilder()
.DeclareCondition("Id", QueryConditionType.Equals, 15);
var secondConditionBuilder = new QueryConditionBuilder()
.DeclareCondition("Name", QueryConditionType.Like, "Marcel");
var condition = new QueryConditionBuilder()
.DeclareConditionNode(
combinationType,
() => firstConditionBuilder,
() => secondConditionBuilder)
.Build();
var expectedFirstConditionStringBuilder = new StringBuilder();
var expectedSecondConditionStringBuilder = new StringBuilder();
var expectedQueryParameters = new List <QueryParameter>();
firstConditionBuilder.Build()
.AppendTo(expectedFirstConditionStringBuilder, expectedQueryParameters);
secondConditionBuilder.Build()
.AppendTo(expectedSecondConditionStringBuilder, expectedQueryParameters);
var expectedConditionString = string.Format(
expectedConditionStringFormat,
expectedFirstConditionStringBuilder.ToString(),
expectedSecondConditionStringBuilder.ToString());
var actualConditionStringBuilder = new StringBuilder();
var actualQueryParameters = new List <QueryParameter>();
condition.AppendTo(actualConditionStringBuilder, actualQueryParameters);
Assert.Equal(expectedConditionString, actualConditionStringBuilder.ToString());
Assert.Equal(expectedQueryParameters.Count, actualQueryParameters.Count);
var idParameter = actualQueryParameters.First(qp => qp.ParameterName == "Id0");
var nameParameter = actualQueryParameters.First(qp => qp.ParameterName == "Name0");
Assert.Equal(15, idParameter.Value);
Assert.Equal("Marcel", nameParameter.Value);
}
public void BalancedInterlacedQueryConditionNodeTests(
QueryConditionNodeType combinationTypeOnFirstLayer,
QueryConditionNodeType combinationInFirstInterlacedCondition,
QueryConditionNodeType combinationInSecondInterlacedCondition,
string expectedConditionString)
{
var condition = new QueryConditionBuilder()
.DeclareConditionNode(
combinationTypeOnFirstLayer,
() => new QueryConditionBuilder()
.DeclareConditionNode(
combinationInFirstInterlacedCondition,
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, "Marcel"),
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, "Viktoria")),
() => new QueryConditionBuilder()
.DeclareConditionNode(
combinationInSecondInterlacedCondition,
() => new QueryConditionBuilder().DeclareCondition("LastName", QueryConditionType.Equals, "Hirscher"),
() => new QueryConditionBuilder().DeclareCondition("LastName", QueryConditionType.NotEquals, "Mathis")))
.Build();
var actualConditionStringBuilder = new StringBuilder();
var actualQueryParameters = new List <QueryParameter>();
condition.AppendTo(actualConditionStringBuilder, actualQueryParameters);
Assert.Equal(expectedConditionString, actualConditionStringBuilder.ToString());
Assert.Equal(4, actualQueryParameters.Count);
var firstNameParameter1 = actualQueryParameters.First(qp => qp.ParameterName == "FirstName0");
var firstNameParameter2 = actualQueryParameters.First(qp => qp.ParameterName == "FirstName1");
var lastNameParameter1 = actualQueryParameters.First(qp => qp.ParameterName == "LastName0");
var lastNameParameter2 = actualQueryParameters.First(qp => qp.ParameterName == "LastName1");
Assert.Equal("Marcel", firstNameParameter1.Value);
Assert.Equal("Viktoria", firstNameParameter2.Value);
Assert.Equal("Hirscher", lastNameParameter1.Value);
Assert.Equal("Mathis", lastNameParameter2.Value);
}
public void NestedContradictionConditionTest()
{
string expectedQuery = "([Id] = @Id0 AND 1 = 0)";
var queryParameters = new List <QueryParameter>();
var queryBuilder = new StringBuilder();
var condition = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => new QueryConditionBuilder()
.DeclareCondition("Id", QueryConditionType.Equals, 5),
() => new QueryConditionBuilder().DeclareContradictingCondition())
.Build();
condition.AppendTo(queryBuilder, queryParameters);
Assert.Equal(expectedQuery, queryBuilder.ToString());
Assert.Single(queryParameters);
Assert.Equal("Id0", queryParameters.First().ParameterName);
Assert.Equal(5, queryParameters.First().Value);
}
public void UnbalancedInterlacedQueryConditionNodeTests(
QueryConditionNodeType combinationTypeOnFirstLayer,
QueryConditionNodeType combinationTypeForInterlacedCondition,
bool interlacedCombinationOnLeft,
string expectedConditionString)
{
var simpleConditionBuilder = new QueryConditionBuilder()
.DeclareCondition("Id", QueryConditionType.NotEquals, 15);
var interlacedConditionBuilder = new QueryConditionBuilder()
.DeclareConditionNode(
combinationTypeForInterlacedCondition,
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, "Marcel"),
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, "Viktoria"));
var condition = new QueryConditionBuilder()
.DeclareConditionNode(
combinationTypeOnFirstLayer,
() => interlacedCombinationOnLeft ? interlacedConditionBuilder : simpleConditionBuilder,
() => interlacedCombinationOnLeft ? simpleConditionBuilder : interlacedConditionBuilder)
.Build();
var actualConditionStringBuilder = new StringBuilder();
var actualQueryParameters = new List <QueryParameter>();
condition.AppendTo(actualConditionStringBuilder, actualQueryParameters);
Assert.Equal(expectedConditionString, actualConditionStringBuilder.ToString());
Assert.Equal(3, actualQueryParameters.Count);
var idParameter = actualQueryParameters.First(qp => qp.ParameterName == "Id0");
var firstNameParameter1 = actualQueryParameters.First(qp => qp.ParameterName == "FirstName0");
var firstNameParameter2 = actualQueryParameters.First(qp => qp.ParameterName == "FirstName1");
Assert.Equal(15, idParameter.Value);
Assert.Equal("Marcel", firstNameParameter1.Value);
Assert.Equal("Viktoria", firstNameParameter2.Value);
}
public async Task DeleteWithConditionTests(Type domainType, bool expectedSuccess)
{
var adoDaoInstance = GetAdoDaoInstance(domainType);
var deleteMethod = GetAdoDaoMethodInfo(
domainType,
"DeleteAsync",
new Type[] { typeof(IQueryCondition) });
var condition = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.Equals, 0),
() => new QueryConditionBuilder()
.DeclareCondition(nameof(Entities.EntityObjectBase.Id), QueryConditionType.Equals, 1))
.Build();
var parameterList = new object[] { condition };
if (expectedSuccess)
{
var affectedRows = (int)await(dynamic) deleteMethod.Invoke(adoDaoInstance, parameterList);
Assert.Equal(2, affectedRows);
var getAllMethod = GetAdoDaoMethodInfo(
domainType,
"GetAllConditionalAsync");
var matchingObjects = (IEnumerable <object>)await(dynamic) getAllMethod.Invoke(adoDaoInstance, parameterList);
Assert.Empty(matchingObjects);
}
else
{
await Assert.ThrowsAsync <SqlException>(
() => (dynamic)deleteMethod.Invoke(adoDaoInstance, parameterList))
.ConfigureAwait(false);
}
}
public void FilterData(QueryConditionBuilder conditionBuilder)
{
this.conditionBuilder = conditionBuilder;
this.LoadData(this.displayInfo, 1, false);
}
public void GenerateUpdateQueryWithConditionsTest()
{
var updateCondition = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => new QueryConditionBuilder()
.DeclareCondition("FirstName", QueryConditionType.NotEquals, "Marcel"),
() => new QueryConditionBuilder()
.DeclareCondition("DateOfBirth", QueryConditionType.LessThan, new DateTime(2005, 1, 1))),
() => new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder()
.DeclareCondition("LastName", QueryConditionType.Equals, "Halbmayr"),
() => new QueryConditionBuilder()
.DeclareCondition("LastName", QueryConditionType.Equals, "Fuchs")))
.Build();
var expectedUpdatedDateOfBirth = new DateTime(2001, 1, 1);
var expectedUpdatedImageUrl = "https://robohash.org/1";
var expectedUpdatedSexId = 0;
var updatedObject = new
{
DateOfBirth = expectedUpdatedDateOfBirth,
ImageUrl = expectedUpdatedImageUrl,
SexId = expectedUpdatedSexId
};
string expectedUpdateQuery =
"UPDATE [Hurace].[Skier] " +
"SET [DateOfBirth] = @DateOfBirth0, [ImageUrl] = @ImageUrl0, [SexId] = @SexId0 " +
"WHERE (([FirstName] != @FirstName0 AND [DateOfBirth] < @DateOfBirth1)" +
" AND ([LastName] = @LastName0 OR [LastName] = @LastName1))";
var queryGenerator = new SqlQueryGenerator <Entities.Skier>();
(var actualUpdateQuery, var queryParameters) = queryGenerator.GenerateUpdateQuery(updatedObject, updateCondition);
Assert.Equal(expectedUpdateQuery, actualUpdateQuery);
Assert.Equal(7, queryParameters.Length);
var updateDateOfBirthParam = queryParameters.First(qp => qp.ParameterName == "DateOfBirth0");
var updatedImageUrlParam = queryParameters.First(qp => qp.ParameterName == "ImageUrl0");
var updateSexIdParam = queryParameters.First(qp => qp.ParameterName == "SexId0");
Assert.Equal(expectedUpdatedDateOfBirth.ToString("s"), updateDateOfBirthParam.Value);
Assert.Equal(expectedUpdatedImageUrl, updatedImageUrlParam.Value);
Assert.Equal(expectedUpdatedSexId, updateSexIdParam.Value);
var conditionFirstName = queryParameters.First(qp => qp.ParameterName == "FirstName0");
var conditionDateOfBirth = queryParameters.First(qp => qp.ParameterName == "DateOfBirth1");
var conditionLastName1 = queryParameters.First(qp => qp.ParameterName == "LastName0");
var conditionLastName2 = queryParameters.First(qp => qp.ParameterName == "LastName1");
Assert.Equal("Marcel", conditionFirstName.Value);
Assert.Equal("2005-01-01T00:00:00", conditionDateOfBirth.Value);
Assert.Equal("Halbmayr", conditionLastName1.Value);
Assert.Equal("Fuchs", conditionLastName2.Value);
}
