public void ThrowExceptionAtCreateInlineMergeIfAQualifierFieldIsNotPresentAtDataEntityFieldMapping()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionAtCreateInlineMergeIfAQualifierFieldIsNotPresentAtDataEntityFieldMappingClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field4" });
var qualifiers = Field.From(new[] { "Field1", "Field3" });
// Act/Assert
Assert.Throws <InvalidOperationException>(() => statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers));
}
public void ThrowExceptionIfFieldIsIgnoreInlineMerge()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfFieldIsIgnoreInlineMergeClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From(new[] { "Field1" });
// Act/Assert
statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers);
}
public void ThrowExceptionIfTheFieldsAreNull()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfTheFieldsAreNullClass>();
var fields = (IEnumerable <Field>)null;
var qualifiers = Field.From(new[] { "Field1" });
// Act/Assert
statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers);
}
public void ThrowExceptionIfAQualifierFieldIsNotPresentAtDataEntity()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfAQualifierFieldIsNotPresentAtDataEntityClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From(new[] { "Field1", "Field4" });
// Act/Assert
statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers);
}
public void ThrowExceptionIfNoQualifiersAndNoPrimaryFieldDefined()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfNoQualifiersAndNoPrimaryFieldDefinedClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = (IEnumerable <Field>)null;
// Act/Assert
statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers);
}
public void ThrowExceptionAtCreateInlineMergeIfTheFieldsContainsAnIgnoreInlineMergeFields()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionAtCreateInlineMergeIfTheFieldsContainsAnIgnoreInlineMergeFieldsClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From(new[] { "Field1", "Field2" });
// Act/Assert
Assert.Throws <InvalidOperationException>(() => statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers));
}
public void ThrowExceptionAtCreateInlineMergeIfTheQualifiersAreNullWithoutPrimaryKey()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionAtCreateInlineMergeIfTheQualifiersAreNullWithoutPrimaryKeyClass>();
var fields = Field.From(new[] { "Field1" });
var qualifiers = (IEnumerable <Field>)null;
// Act/Assert
Assert.Throws <NullReferenceException>(() => statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers));
}
public void TestWithClassMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithClassMappingsClass>();
// Act
var actual = statementBuilder.CreateTruncate(queryBuilder);
var expected = $"" +
$"TRUNCATE TABLE [ClassName] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCreateCountWitClassMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateCountWitClassMappingsClass>();
// Act
var actual = statementBuilder.CreateCount(queryBuilder, null);
var expected = $"" +
$"SELECT COUNT_BIG (1) AS [Counted] " +
$"FROM [ClassName] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCreateDeleteWithoutMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateDeleteWithoutMappingsClass>();
// Act
var actual = statementBuilder.CreateDelete(queryBuilder, null);
var expected = $"" +
$"DELETE " +
$"FROM [TestCreateDeleteWithoutMappingsClass] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMapping()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingClass>();
var queryGroup = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateUpdate(queryBuilder, queryGroup);
var expected = $"" +
$"UPDATE [TestWithFieldMappingClass] " +
$"SET [Field1] = @Field1, [Field2] = @Field2, [Field4] = @Field4 ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMapping()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingClass>();
var queryGroup = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field4] " +
$"FROM [TestWithFieldMappingClass] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithExpression()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithExpressionClass>();
var queryGroup = QueryGroup.Parse(new { Field1 = 1, Field4 = new { Operation = Operation.GreaterThan, Value = 2 } });
// Act
var actual = statementBuilder.CreateUpdate(queryBuilder, queryGroup);
var expected = $"" +
$"UPDATE [TestWithExpressionClass] " +
$"SET [Field2] = @Field2, [Field3] = @Field3 " +
$"WHERE ([Field1] = @_Field1 AND [Field4] > @_Field4) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithAnyFieldsAtExpression()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithAnyFieldsAtExpressionClass>();
var expression = new { AnyField = 1 };
var queryGroup = QueryGroup.Parse(expression);
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestWithAnyFieldsAtExpressionClass] " +
$"WHERE ([AnyField] = @AnyField) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCreateDeleteWithMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateDeleteWithMappingsClass>();
var expression = new { Field1 = 1 };
// Act
var queryGroup = QueryGroup.Parse(expression);
var actual = statementBuilder.CreateDelete(queryBuilder, queryGroup);
var expected = $"" +
$"DELETE " +
$"FROM [ClassName] " +
$"WHERE ([Field1] = @Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithClassMappingId()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithClassMappingIdClass>();
// Act
var actual = statementBuilder.CreateInsert(queryBuilder);
var expected = $"" +
$"INSERT INTO [ClassName] " +
$"( [ClassNameId], [Field2], [Field3] ) " +
$"VALUES " +
$"( @ClassNameId, @Field2, @Field3 ) ; " +
$"SELECT @ClassNameId AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingsClass>();
// Act
var actual = statementBuilder.CreateInsert(queryBuilder);
var expected = $"" +
$"INSERT INTO [TestWithFieldMappingsClass] " +
$"( [Field1], [Field2], [Field4] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field4 ) ; " +
$"SELECT NULL AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithDescendingOrderFields()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithDescendingOrderFieldsClass>();
var queryGroup = (QueryGroup)null;
var orderBy = OrderField.Parse(new { OrderField = Order.Descending });
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup, orderBy);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestWithDescendingOrderFieldsClass] " +
$"ORDER BY [OrderField] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCountWithExpressions()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateCountWithExpressionsClass>();
var expression = new { Field1 = 1 };
// Act
var queryGroup = QueryGroup.Parse(expression);
var actual = statementBuilder.CreateCount(queryBuilder, queryGroup);
var expected = $"" +
$"SELECT COUNT_BIG (1) AS [Counted] " +
$"FROM [TestCreateCountWithExpressionsClass] " +
$"WHERE ([Field1] = @Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingsClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field4" });
// Act
var actual = statementBuilder.CreateInlineUpdate(queryBuilder, fields, null);
var expected = $"" +
$"UPDATE [ClassName] " +
$"SET [Field1] = @Field1, " +
$"[Field2] = @Field2, " +
$"[Field4] = @Field4 ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCreateInlineInsertReturnPrimaryKeyFromAttribute()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateInlineInsertReturnPrimaryKeyFromAttributeClass>();
var fields = Field.From(new[] { "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateInlineInsert(queryBuilder, fields);
var expected = $"" +
$"INSERT INTO [ClassName] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2, @Field3 ) ; " +
$"SELECT @Field1 AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingsClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field4" });
// Act
var actual = statementBuilder.CreateInlineInsert(queryBuilder, fields, false);
var expected = $"" +
$"INSERT INTO [ClassName] " +
$"( [Field1], [Field2], [Field4] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field4 ) ; " +
$"SELECT NULL AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestOverrideIgnoreFromInlineInsertOperation()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestOverrideIgnoreFromInlineInsertOperationClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateInlineInsert(queryBuilder, fields, true);
var expected = $"" +
$"INSERT INTO [ClassName] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ; " +
$"SELECT NULL AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithPrimaryIdentity()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithPrimaryIdentityClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateInlineInsert(queryBuilder, fields, false);
var expected = $"" +
$"INSERT INTO [TestWithPrimaryIdentityClass] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2, @Field3 ) ; " +
$"SELECT SCOPE_IDENTITY() AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCreateInlineUpdateWithFieldMappingsAndExpressions()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateInlineUpdateWithFieldMappingsAndExpressionsClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field4" });
var expression = new { Field1 = 1 };
// Act
var queryGroup = QueryGroup.Parse(expression);
var actual = statementBuilder.CreateInlineUpdate(queryBuilder, fields, queryGroup);
var expected = $"" +
$"UPDATE [TestCreateInlineUpdateWithFieldMappingsAndExpressionsClass] " +
$"SET [Field1] = @Field1, " +
$"[Field2] = @Field2, " +
$"[Field4] = @Field4 " +
$"WHERE ([Field1] = @_Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestOverrideIgnoreForUpdate()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestOverrideIgnoreForUpdateClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var expression = new { Field1 = 1 };
var queryGroup = QueryGroup.Parse(expression);
// Act
var actual = statementBuilder.CreateInlineUpdate(queryBuilder, fields, queryGroup, true);
var expected = $"" +
$"UPDATE [TestOverrideIgnoreForUpdateClass] " +
$"SET [Field1] = @Field1, " +
$"[Field2] = @Field2, " +
$"[Field3] = @Field3 " +
$"WHERE ([Field1] = @_Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithClassId()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithClassIdClass>();
var qualifiers = (IEnumerable <Field>)null;
// Act
var actual = statementBuilder.CreateMerge(queryBuilder, qualifiers);
var expected = $"" +
$"MERGE [TestWithClassIdClass] AS T " +
$"USING ( SELECT @TestWithClassIdClassId AS [TestWithClassIdClassId], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[TestWithClassIdClassId] = T.[TestWithClassIdClassId] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [TestWithClassIdClassId], [Field2], [Field3] ) " +
$"VALUES ( S.[TestWithClassIdClassId], S.[Field2], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field2] = S.[Field2], [Field3] = S.[Field3] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithClassMappings()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithClassMappingsClass>();
var qualifiers = Field.From("Field1");
// Act
var actual = statementBuilder.CreateMerge(queryBuilder, qualifiers);
var expected = $"" +
$"MERGE [ClassName] AS T " +
$"USING ( SELECT @Field1 AS [Field1], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[Field1] = T.[Field1] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [Field1], [Field2], [Field3] ) " +
$"VALUES ( S.[Field1], S.[Field2], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field2] = S.[Field2], [Field3] = S.[Field3] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithClassMappingIdField()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithClassMappingIdFieldClass>();
var fields = Field.From(new[] { "ClassNameId", "Field2", "Field3" });
var qualifiers = (IEnumerable <Field>)null;
// Act
var actual = statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers);
var expected = $"" +
$"MERGE [ClassName] AS T " +
$"USING ( SELECT @ClassNameId AS [ClassNameId], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[ClassNameId] = T.[ClassNameId] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [ClassNameId], [Field2], [Field3] ) " +
$"VALUES ( S.[ClassNameId], S.[Field2], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field2] = S.[Field2], [Field3] = S.[Field3] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithOverrideIgnoreFromUpdate()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithOverrideIgnoreFromUpdateClass>();
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From(new[] { "Field1" });
// Act
var actual = statementBuilder.CreateInlineMerge(queryBuilder, fields, qualifiers, true);
var expected = $"" +
$"MERGE [TestWithOverrideIgnoreFromUpdateClass] AS T " +
$"USING ( SELECT @Field1 AS [Field1], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[Field1] = T.[Field1] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [Field1], [Field2], [Field3] ) " +
$"VALUES ( S.[Field1], S.[Field2], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field1] = S.[Field1], [Field2] = S.[Field2], [Field3] = S.[Field3] ;";
// Assert
Assert.AreEqual(expected, actual);
}
