public void ThrowExceptionOnSqlStatementBuilderCreateMergeAllIfThereAreNoPrimaryAndNoQualifiers()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
statementBuilder.CreateMergeAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: null,
batchSize: 1,
primaryField: null,
identityField: null);
}
public void TestSqlStatementBuilderCreateCountAllWithHints()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var hints = "WITH (NOLOCK)";
// Act
var actual = statementBuilder.CreateCountAll(queryBuilder: queryBuilder,
tableName: tableName,
hints: hints);
var expected = "SELECT COUNT_BIG (1) AS [Counted] FROM [Table] WITH (NOLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
protected SqlPreparationQueryModelVisitor(
ISqlPreparationContext parentContext,
ISqlPreparationStage stage,
UniqueIdentifierGenerator generator,
ResultOperatorHandlerRegistry resultOperatorHandlerRegistry)
{
ArgumentUtility.CheckNotNull("stage", stage);
ArgumentUtility.CheckNotNull("generator", generator);
ArgumentUtility.CheckNotNull("resultOperatorHandlerRegistry", resultOperatorHandlerRegistry);
_stage = stage;
_generator = generator;
_resultOperatorHandlerRegistry = resultOperatorHandlerRegistry;
_sqlStatementBuilder = new SqlStatementBuilder();
_context = new SqlPreparationContext(parentContext, _sqlStatementBuilder);
}
public override void HandleResultOperator(AnyResultOperator resultOperator, SqlStatementBuilder sqlStatementBuilder, UniqueIdentifierGenerator generator, ISqlPreparationStage stage, ISqlPreparationContext context)
{
ArgumentUtility.CheckNotNull("resultOperator", resultOperator);
ArgumentUtility.CheckNotNull("sqlStatementBuilder", sqlStatementBuilder);
ArgumentUtility.CheckNotNull("generator", generator);
ArgumentUtility.CheckNotNull("stage", stage);
ArgumentUtility.CheckNotNull("context", context);
// No name required for the select projection inside of an EXISTS expression
var sqlSubStatement = sqlStatementBuilder.GetStatementAndResetBuilder();
var subStatementExpression = new SqlSubStatementExpression(sqlSubStatement);
var sqlExistsExpression = new SqlExistsExpression(subStatementExpression);
sqlStatementBuilder.SelectProjection = stage.PrepareSelectExpression(sqlExistsExpression, context);
UpdateDataInfo(resultOperator, sqlStatementBuilder, sqlSubStatement.DataInfo);
}
public void TestWithExpression()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
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 TestSqlStatementBuilderCreateQueryAllWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
var actual = statementBuilder.CreateQueryAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
var expected = "SELECT [Field1], [Field2], [Field3] FROM [dbo].[Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
private Expression CheckAndSimplifyEntityWithinSubStatement(SqlSubStatementExpression sqlSubStatementExpression)
{
var newSelectProjection = ResolvePotentialEntity(sqlSubStatementExpression.SqlStatement.SelectProjection);
if (newSelectProjection != sqlSubStatementExpression.SqlStatement.SelectProjection)
{
var newSubStatement = new SqlStatementBuilder(sqlSubStatementExpression.SqlStatement)
{
SelectProjection = newSelectProjection
};
newSubStatement.RecalculateDataInfo(sqlSubStatementExpression.SqlStatement.SelectProjection);
return(newSubStatement.GetSqlStatement().CreateExpression());
}
return(sqlSubStatementExpression);
}
public void ThrowExceptionOnSqlStatementBuilderCreateMergeIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
var fields = Field.From("Field1", "Field2", "Field3");
var qualifiers = Field.From("Field1");
// Act
statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
primaryField: null,
identityField: null);
}
private void CheckExpressionMovedIntoSubStatement(Expression fromExpression, SqlStatement originalStatement)
{
Assert.That(fromExpression, Is.TypeOf <SqlSubStatementExpression> ());
var subStatement = ((SqlSubStatementExpression)fromExpression).SqlStatement;
// The sub-statement should have the same SelectProjection, but wrapped into a NamedExpression with default name.
Assert.That(
subStatement.SelectProjection,
Is.TypeOf <NamedExpression> ().With.Property("Name").Null.And.Property("Expression").SameAs(originalStatement.SelectProjection));
// The rest of the sub-statement should be equivalent to the original statement.
var expectedSubStatement = new SqlStatementBuilder(originalStatement)
{
SelectProjection = subStatement.SelectProjection
}.GetSqlStatement();
Assert.That(subStatement, Is.EqualTo(expectedSubStatement));
}
public void ResolveSelectProjection_ResolvesExpression()
{
var expression = new SqlTableReferenceExpression(_sqlTable);
var sqlStatementBuilder = new SqlStatementBuilder();
var fakeResult = Expression.Constant(0);
_stageMock
.Expect(mock => mock.ResolveSelectExpression(expression, sqlStatementBuilder, _mappingResolutionContext))
.Return(fakeResult);
_stageMock.Replay();
var result = _visitor.ResolveSelectProjection(expression, sqlStatementBuilder);
_stageMock.VerifyAllExpectations();
Assert.That(result, Is.SameAs(fakeResult));
}
public void ToString_SingleTable()
{
var dataInfo = new TestStreamedValueInfo(typeof(int));
var selectProjection = Expression.Constant(1);
var sqlTable = new SqlTable(new ResolvedSimpleTableInfo(typeof(Cook), "CookTable", "c"), JoinSemantics.Inner);
var builder = new SqlStatementBuilder
{
DataInfo = dataInfo,
SelectProjection = selectProjection,
SqlTables = { sqlTable },
};
var result = builder.ToString();
Assert.That(result, Is.EqualTo("SELECT 1 FROM [CookTable] [c]"));
}
void IResultOperatorHandler.HandleResultOperator(
ResultOperatorBase resultOperator,
SqlStatementBuilder sqlStatementBuilder,
UniqueIdentifierGenerator generator,
ISqlPreparationStage stage,
ISqlPreparationContext context)
{
ArgumentUtility.CheckNotNull("resultOperator", resultOperator);
ArgumentUtility.CheckNotNull("sqlStatementBuilder", sqlStatementBuilder);
ArgumentUtility.CheckNotNull("generator", generator);
ArgumentUtility.CheckNotNull("stage", stage);
ArgumentUtility.CheckNotNull("context", context);
var castOperator = ArgumentUtility.CheckNotNullAndType <T> ("resultOperator", resultOperator);
HandleResultOperator(castOperator, sqlStatementBuilder, generator, stage, context);
}
public void ThrowExceptionOnSqlStatementBuilderCreateMergeIfTheQualifiersAreNotPresentAtTheGivenFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var qualifiers = Field.From("Id");
// Act
statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
primaryField: null,
identityField: null);
}
private Expression HandlePotentialConstantExpression(Expression preparedKeySelector)
{
var constantExpression = preparedKeySelector as ConstantExpression;
if (constantExpression == null)
{
return(preparedKeySelector);
}
var subSqlStatement = new SqlStatementBuilder
{
DataInfo = new StreamedSingleValueInfo(constantExpression.Type, false),
SelectProjection = new NamedExpression(null, constantExpression)
}.GetSqlStatement();
return(new SqlSubStatementExpression(subSqlStatement));
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertIfTheIdentityIsNotReallyAnIdentity()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var qualifiers = Field.From("Field1");
var identifyField = new DbField("Field2", false, false, false, typeof(int), null, null, null);
// Act
statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: null,
identityField: identifyField);
}
public void AddJoin_WithWhere_AlsoAddWhere_AddAnotherJoin()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob WHERE that = this";
var builder = new SqlStatementBuilder(_connection, startSql);
const string expectedSql = "select DISTINCT * from (bob LEFT JOIN [bobby] ON bobs = bobbys) " +
"LEFT JOIN [bobbin] ON bobbys = bobbins WHERE that = this AND this = that";
//---------------Execute Test ----------------------
builder.AddJoin("left join", "bobby", "bobs = bobbys");
builder.AppendCriteria("this = that");
builder.AddJoin("left join", "bobbin", "bobbys = bobbins");
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void TestAddSelectFields()
{
//-------------Setup Test Pack ------------------
const string startSql = "select [FALSE FROM CLAUSE], [FALSE WHERE CLAUSE] from bob WHERE that = this";
var builder = new SqlStatementBuilder(_connection, startSql);
var fields = new List <string> {
"myField1", "myField2"
};
const string expectedSql = "select [FALSE FROM CLAUSE], [FALSE WHERE CLAUSE], [myField1], [myField2] from bob WHERE that = this";
//-------------Execute test ---------------------
builder.AddSelectFields(fields);
var actual = builder.GetStatement().Statement.ToString();
//-------------Test Result ----------------------
Assert.AreEqual(expectedSql, actual);
}
public void ThrowExceptionOnSqlStatementBuilderCreateMergeIfThePrimaryAsQualifierIsNotPresentAtTheGivenFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var primaryField = new DbField("Id", true, false, false, typeof(int), null, null, null);
// Act
statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: null,
primaryField: primaryField,
identityField: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfTheTableIsNull()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = (string)null;
var fields = Field.From("Field1", "Field2");
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: null,
where : null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateUpdateIfThePrimaryIsNotReallyAPrimary()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var primaryField = new DbField("Field1", false, false, false, typeof(int), null, null, null);
// Act
statementBuilder.CreateUpdate(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : null,
primaryField: primaryField,
identityField: null);
}
public void ConvertToSqlTable_StreamedSequenceInfo()
{
var selectProjection = Expression.Constant(new Cook());
var sqlStatement =
new SqlStatementBuilder {
DataInfo = new StreamedSequenceInfo(typeof(Cook[]), Expression.Constant(new Cook())), SelectProjection = selectProjection
}.
GetSqlStatement();
var expression = new SqlSubStatementExpression(sqlStatement);
var result = expression.ConvertToSqlTable("q0");
Assert.That(result.JoinSemantics, Is.EqualTo(JoinSemantics.Inner));
Assert.That(result.TableInfo.GetResolvedTableInfo().TableAlias, Is.EqualTo("q0"));
Assert.That(result.TableInfo, Is.TypeOf(typeof(ResolvedSubStatementTableInfo)));
Assert.That(((ResolvedSubStatementTableInfo)result.TableInfo).SqlStatement, Is.EqualTo(sqlStatement));
}
protected virtual SqlStatement ResolveSqlStatement(SqlStatement sqlStatement)
{
ArgumentUtility.CheckNotNull("sqlStatement", sqlStatement);
foreach (var sqlTable in sqlStatement.SqlTables)
{
sqlTable.Accept(this);
}
var sqlStatementBuilder = new SqlStatementBuilder(sqlStatement);
var previousSelectProjection = sqlStatementBuilder.SelectProjection;
sqlStatementBuilder.SelectProjection = ResolveSelectProjection(sqlStatementBuilder.SelectProjection, sqlStatementBuilder);
sqlStatementBuilder.RecalculateDataInfo(previousSelectProjection);
if (sqlStatementBuilder.GroupByExpression != null)
{
sqlStatementBuilder.GroupByExpression = ResolveGroupByExpression(sqlStatementBuilder.GroupByExpression);
}
if (sqlStatementBuilder.WhereCondition != null)
{
sqlStatementBuilder.WhereCondition = ResolveWhereCondition(sqlStatementBuilder.WhereCondition);
}
if (sqlStatementBuilder.TopExpression != null)
{
sqlStatementBuilder.TopExpression = ResolveTopExpression(sqlStatementBuilder.TopExpression);
}
if (sqlStatementBuilder.Orderings.Count > 0)
{
for (int i = 0; i < sqlStatementBuilder.Orderings.Count; ++i)
{
var newOrderingExpression = _stage.ResolveOrderingExpression(sqlStatementBuilder.Orderings[i].Expression, _context);
if (newOrderingExpression != sqlStatementBuilder.Orderings[i].Expression)
{
sqlStatementBuilder.Orderings[i] = new Ordering(newOrderingExpression, sqlStatementBuilder.Orderings[i].OrderingDirection);
}
}
}
return(sqlStatementBuilder.GetSqlStatement());
}
private Expression HandlePotentialSubStatementExpression(
Expression preparedKeySelector,
SqlStatementBuilder sqlStatementBuilder,
UniqueIdentifierGenerator generator)
{
var subStatementExpression = preparedKeySelector as SqlSubStatementExpression;
if (subStatementExpression == null)
{
return(preparedKeySelector);
}
var sqlTable = subStatementExpression.ConvertToSqlTable(generator.GetUniqueIdentifier("t"));
sqlStatementBuilder.SqlTables.Add(sqlTable);
return(new SqlTableReferenceExpression(sqlTable));
}
public void TestWithMappings()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithMappingsClass>();
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 TestSqlDbProviderCreateInsertWithFieldMappings()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestSqlDbProviderCreateInsertWithFieldMappingsClass>();
// Act
var actual = statementBuilder.CreateInsert(queryBuilder);
var expected = $"" +
$"INSERT INTO [TestSqlDbProviderCreateInsertWithFieldMappingsClass] " +
$"( [Field1], [Field2], [Field4] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field4 ) ; " +
$"SELECT NULL AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlDbProviderCreateInsertWithClassMappingId()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithSqlDbProviderCreateInsertClassMappingIdClass>();
// 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 BuildSelectPart_WithDistinctIsTrue()
{
var sqlStatement =
new SqlStatementBuilder
{
DataInfo = new TestStreamedValueInfo(typeof(int)), SelectProjection = _entityExpression, IsDistinctQuery = true
}.GetSqlStatement();
_stageMock.Expect(
mock => mock.GenerateTextForSelectExpression(_commandBuilder, sqlStatement.SelectProjection))
.WhenCalled(mi => ((SqlCommandBuilder)mi.Arguments[0]).Append("[t].[ID],[t].[Name],[t].[City]"));
_stageMock.Replay();
_generator.BuildSelectPart(sqlStatement, _commandBuilder, false);
Assert.That(_commandBuilder.GetCommandText(), Is.EqualTo("SELECT DISTINCT [t].[ID],[t].[Name],[t].[City]"));
_stageMock.VerifyAllExpectations();
}
public void TestWithPrimaryKeyField()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithPrimaryKeyFieldClass>();
// Act
var actual = statementBuilder.CreateInsert(queryBuilder);
var expected = $"" +
$"INSERT INTO [TestWithPrimaryKeyFieldClass] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ; " +
$"SELECT @Field1 AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void VisitSqlSubStatmentExpression_HasTopExpression_ReturnsNew()
{
var builder = new SqlStatementBuilder(SqlStatementModelObjectMother.CreateSqlStatementWithCook());
builder.Orderings.Add(new Ordering(Expression.Constant("order"), OrderingDirection.Asc));
var sqlStatement = builder.GetSqlStatement();
builder.Orderings.Clear();
var expectedStatement = builder.GetSqlStatement();
var subStatementExpression = new SqlSubStatementExpression(sqlStatement);
var result = SqlPreparationExpressionVisitor.TranslateExpression(subStatementExpression, _context, _stageMock, _methodCallTransformerProvider);
Assert.That(result, Is.Not.SameAs(subStatementExpression));
Assert.That(result, Is.TypeOf(typeof(SqlSubStatementExpression)));
Assert.That(((SqlSubStatementExpression)result).SqlStatement, Is.EqualTo(expectedStatement));
}
public void AddJoin_WithEmptyStatement()
{
//---------------Set up test pack-------------------
const string startSql = "";
var builder = new SqlStatementBuilder(_connection, startSql);
//---------------Execute Test ----------------------
try
{
builder.AddJoin("left join", "bobby", "bobs = bobbys");
Assert.Fail("Expected to throw an SqlStatementException");
}
//---------------Test Result -----------------------
catch (SqlStatementException ex)
{
StringAssert.Contains("Cannot add a join clause to a SQL statement that does not contain a from clause", ex.Message);
}
}
