public void GenerateUpdateQuery_AllColumnsHasNoSetter_Throws()
{
// Arrange
var generator = new SqlQueryGenerator <AllColumnsHasMissingSetterEntity>("dbo", "Users");
// Act && Assert
Assert.Throws <InvalidOperationException>(() => generator.GenerateUpdateQuery());
}
public void GenerateUpdateQuery_NoPrimaryKey_Throws()
{
// Arrange
var generator = new SqlQueryGenerator <HeapEntity>("dbo", "Users");
// Act && Assert
Assert.Throws <InvalidOperationException>(() => generator.GenerateUpdateQuery());
}
public void GenerateUpdateQuery_ColumnHasNoSetter_ColumnIsExcluded()
{
// Arrange
var generator = new SqlQueryGenerator <ColumnHasMissingSetterEntity>("dbo", "Users");
// Act
var query = generator.GenerateUpdateQuery();
// Assert
Assert.Equal("UPDATE [dbo].[Users] SET [dbo].[Users].[Age] = @Age OUTPUT [inserted].[Id], [inserted].[Age], [inserted].[DateCreated] WHERE [dbo].[Users].[Id] = @Id;", query);
}
public void GenerateUpdateQuery_CustomColumnNames_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <CustomColumnNamesEntity>("dbo", "Orders");
// Act
var updateQuery = generator.GenerateUpdateQuery();
// Assert
Assert.Equal($"UPDATE [dbo].[Orders] SET [dbo].[Orders].[DateCreated] = @Date OUTPUT [inserted].[OrderId] AS [Id], [inserted].[DateCreated] AS [Date] WHERE [dbo].[Orders].[OrderId] = @Id;", updateQuery);
}
public void GenerateUpdateQuery_CompositePrimaryKey_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <CompositePrimaryKeyEntity>("dbo", "Users");
// Act
var updateQuery = generator.GenerateUpdateQuery();
// Assert
Assert.Equal($"UPDATE [dbo].[Users] SET [dbo].[Users].[DateCreated] = @DateCreated OUTPUT [inserted].[Username], [inserted].[Password], [inserted].[DateCreated] WHERE [dbo].[Users].[Username] = @Username AND [dbo].[Users].[Password] = @Password;", updateQuery);
}
public static void GenerateUpdateQueryWithInvalidParameterTest2()
{
var queryGenerator = new SqlQueryGenerator <Entities.Country>();
Assert.Throws <ArgumentOutOfRangeException>(
() => queryGenerator.GenerateUpdateQuery(
new Entities.Country
{
Name = "AUS",
Id = -3
}));
}
public async Task <bool> UpdateAsync(T updatedInstance)
{
if (updatedInstance is null)
{
throw new ArgumentNullException(nameof(updatedInstance));
}
(string query, QueryParameter[] parameters) = SqlQueryGenerator.GenerateUpdateQuery(updatedInstance);
int affectedRows = await template.ExecuteAsync(query, parameters)
.ConfigureAwait(false);
return(affectedRows == 1);
}
public async Task <int> UpdateAsync(object updatedValues, IQueryCondition condition)
{
if (updatedValues is null)
{
throw new ArgumentNullException(nameof(updatedValues));
}
else if (condition is null)
{
throw new ArgumentNullException(nameof(condition));
}
else if (updatedValues.GetType().GetProperties().Length == 0)
{
throw new InvalidOperationException(
$"Passed {nameof(updatedValues)} anonymous object does not contain any updated values");
}
(string query, QueryParameter[] parameters) = SqlQueryGenerator.GenerateUpdateQuery(updatedValues, condition);
return(await template.ExecuteAsync(query, parameters)
.ConfigureAwait(false));
}
public void GenerateUpdateQueryTest()
{
string expectedQuery = "UPDATE [Hurace].[StartPosition] " +
"SET [StartListId] = @StartListId0, [SkierId] = @SkierId0, [Position] = @Position0 " +
"WHERE [Id] = @Id0";
var queryGenerator = new SqlQueryGenerator <Entities.StartPosition>();
(var generatedQuery, var generatedParameters) =
queryGenerator.GenerateUpdateQuery(
new Entities.StartPosition
{
StartListId = 35,
SkierId = 18,
Position = 25,
Id = 135
});
Assert.Equal(expectedQuery, generatedQuery);
Assert.Equal(35, generatedParameters[0].Value);
Assert.Equal(18, generatedParameters[1].Value);
Assert.Equal(25, generatedParameters[2].Value);
Assert.Equal(135, generatedParameters[3].Value);
}
public static void GenerateUpdateQueryWithInvalidParameterTest4()
{
var queryGenerator = new SqlQueryGenerator <Entities.Skier>();
Assert.Throws <ArgumentNullException>(() => queryGenerator.GenerateUpdateQuery(new { }, null));
}
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);
}
