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); } }