public void SingleTableTwoWhereClauses() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereLiteral = "WELLS FARGO BK NA", SubClauses = new List <WhereSubConditions>() }, new WhereCondition { WhereLeftColumn = "LoanType", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereLiteral = "SE" } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.IsTrue(sqlFromQueryBuilder.Contains(@"SELECT Mortgages.* FROM Mortgages WHERE ((Mortgages.LenderName = 'WELLS FARGO BK NA') AND (Mortgages.LoanType = 'SE'))")); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }
public void SingleTableOneWhereWithSubClauseUsingOrCondition() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereRightColumn = "TD BK NA", SubClauses = new List <WhereSubConditions> { new WhereSubConditions { Connector = LogicOperator.Or, WhereLeftColumn = "LoanType", WhereLeftTable = "Mortgages", CompareOperator = Comparison.Equals, WhereRightColumn = "SE" } } } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.AreEqual(@"SELECT Mortgages.* FROM Mortgages WHERE (LenderName = 'TD BK NA' OR LenderName = 'SE') ", sqlFromQueryBuilder); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }
public void SingleTableOneWhereClauseOrderByOneValue() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereLiteral = "TD BK NA", SubClauses = new List <WhereSubConditions>() } }, ColumnSortAscDesc = new List <QueryBuilderOrderByClause> { new QueryBuilderOrderByClause { ColumnName = "LoanAmountRange", ColumnOrderbyDirection = Sorting.Descending } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.AreEqual(@"SELECT Mortgages.* FROM Mortgages WHERE (Mortgages.LenderName = 'TD BK NA') ORDER BY LoanAmountRange DESC ", sqlFromQueryBuilder); }
public void JoinTwoTablesWithOneWhereClauses() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", JoinConditionsList = new List <JoinCondition> { new JoinCondition { JoinCompareType = Comparison.Equals, JoinRightTable = "MortgateTypes", JoinOnLeftColumn = "MortgageType", JoinOnRightColumn = "MortgageType", TypeOfJoin = JoinType.InnerJoin } }, WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereLiteral = "WELLS FARGO BK NA", SubClauses = new List <WhereSubConditions>() } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.AreEqual(@"SELECT Mortgages.* FROM Mortgages INNER JOIN MortgateTypes ON Mortgages.MortgageType = MortgateTypes.MortgageType WHERE (Mortgages.LenderName = 'WELLS FARGO BK NA')", sqlFromQueryBuilder.Trim()); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }
public void SingleTableSimpleTestAllColumns() { var queryBuilderParms = new QueryBuilderParms(); queryBuilderParms.PrimaryTable = "Mortgages"; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.IsTrue(sqlFromQueryBuilder.Contains("SELECT Mortgages.* FROM Mortgages")); }
public void JoinTwoTablesUseWhereWithSubClauseUsingOrCondition() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", JoinConditionsList = new List <JoinCondition> { new JoinCondition { JoinCompareType = Comparison.Equals, JoinRightTable = "MortgateTypes", JoinOnLeftColumn = "MortgageType", JoinOnRightColumn = "MortgageType", TypeOfJoin = JoinType.InnerJoin } }, WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereRightColumn = "TD BK NA", SubClauses = new List <WhereSubConditions> { new WhereSubConditions { Connector = LogicOperator.Or, WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", CompareOperator = Comparison.Equals, WhereRightColumn = "FROST BK" } } } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.AreEqual(@"SELECT Mortgages.* FROM Mortgages INNER JOIN MortgateTypes ON Mortgages.MortgageType = MortgateTypes.MortgageType WHERE (Mortgages.LenderName = '.TD BK NA' OR Mortgages.LenderName = 'FROST BK')", sqlFromQueryBuilder.Trim()); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }
public void SingleTableOneWhereClause() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "LenderName", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereLiteral = "TD BK NA", SubClauses = new List <WhereSubConditions>() }, } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.IsTrue(sqlFromQueryBuilder.Contains(@"SELECT Mortgages.* FROM Mortgages WHERE (Mortgages.LenderName = 'TD BK NA')")); }
public void SingleTableSimpleTestSpecificColumns() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", IncludeColumns = new List <QueryBuilderColumnsToInclude> { new QueryBuilderColumnsToInclude { ColumnName = "MortgageId" }, new QueryBuilderColumnsToInclude { ColumnName = "LenderName" } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.AreEqual("SELECT Mortgages.MortgageId, Mortgages.LenderName FROM Mortgages ", sqlFromQueryBuilder); }
public void InnerJoinTwoTablesGetSpecificColumnsFromOneTable() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", JoinConditionsList = new List <JoinCondition> { new JoinCondition { JoinCompareType = Comparison.Equals, JoinRightTable = "MortgateTypes", JoinOnLeftColumn = "MortgageType", JoinOnRightColumn = "MortgageType", TypeOfJoin = JoinType.InnerJoin } }, IncludeColumns = new List <QueryBuilderColumnsToInclude> { new QueryBuilderColumnsToInclude { ColumnName = "MortgageId" }, new QueryBuilderColumnsToInclude { ColumnName = "LenderName" } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.AreEqual("SELECT Mortgages.MortgageId, Mortgages.LenderName FROM Mortgages INNER JOIN MortgateTypes ON Mortgages.MortgageType = MortgateTypes.MortgageType", sqlFromQueryBuilder.Trim()); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }
public void InnerJoinTwoTablesGetAllColumnsFromOneTable() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", JoinConditionsList = new List <JoinCondition> { new JoinCondition { JoinCompareType = Comparison.Equals, JoinRightTable = "MortgateTypes", JoinOnLeftColumn = "MortgageType", JoinOnRightColumn = "MortgageType", TypeOfJoin = JoinType.InnerJoin } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.IsTrue(sqlFromQueryBuilder.Contains("SELECT Mortgages.* FROM Mortgages INNER JOIN MortgateTypes ON Mortgages.MortgageType = MortgateTypes.MortgageType ")); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }
public void InnerJoinPropertyAndPersonsAndMorgageOredWheres() { var queryBuilderParms = new QueryBuilderParms { PrimaryTable = "Mortgages", JoinConditionsList = new List <JoinCondition> { new JoinCondition { JoinLeftTable = "BackBone", JoinCompareType = Comparison.Equals, JoinRightTable = "BackBone", JoinOnLeftColumn = "MortgageId", JoinOnRightColumn = "MortgageId", TypeOfJoin = JoinType.InnerJoin }, new JoinCondition { JoinLeftTable = "BackBone", JoinCompareType = Comparison.Equals, JoinRightTable = "Property", JoinOnLeftColumn = "PropertyId", JoinOnRightColumn = "PropertyId", TypeOfJoin = JoinType.InnerJoin }, new JoinCondition { JoinLeftTable = "BackBone", JoinCompareType = Comparison.Equals, JoinRightTable = "Persons", JoinOnLeftColumn = "PersonId", JoinOnRightColumn = "PersonId", TypeOfJoin = JoinType.InnerJoin } }, IncludeColumns = new List <QueryBuilderColumnsToInclude> { new QueryBuilderColumnsToInclude { TableName = "Mortgages", ColumnName = "MortgageId" }, new QueryBuilderColumnsToInclude { TableName = "Mortgages", ColumnName = "LenderName" }, new QueryBuilderColumnsToInclude { TableName = "Mortgages", ColumnName = "MortgageId" }, new QueryBuilderColumnsToInclude { TableName = "Property", ColumnName = "State" }, new QueryBuilderColumnsToInclude { TableName = "Persons", ColumnName = "LastName" } }, WhereConditionsList = new List <WhereCondition> { new WhereCondition { WhereLeftColumn = "State", WhereLeftTable = "Persons", WhereOperator = Comparison.Equals, WhereRightColumn = "WA", WhereAndOr = LogicOperator.Or }, new WhereCondition { WhereLeftColumn = "State", WhereLeftTable = "Property", WhereOperator = Comparison.Like, WhereRightColumn = "%a%", WhereAndOr = LogicOperator.Or }, new WhereCondition { WhereLeftColumn = "LoanToValueRange", WhereLeftTable = "Mortgages", WhereOperator = Comparison.Equals, WhereRightColumn = "100% - 119.99%", } } }; var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql(); var sqlFromQueryBuilder = queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms); Assert.IsTrue(sqlFromQueryBuilder.Trim().Contains(@"SELECT Mortgages.MortgageId, Mortgages.LenderName, Mortgages.MortgageId, Property.State, Persons.LastName FROM Mortgages INNER JOIN BackBone ON BackBone.MortgageId = BackBone.MortgageId INNER JOIN Property ON BackBone.PropertyId = Property.PropertyId INNER JOIN Persons ON BackBone.PersonId = Persons.PersonId WHERE ((Persons.State = 'WA') OR (Property.State LIKE '%a%') OR (Mortgages.LoanToValueRange = '100% - 119.99%'))")); Assert.IsTrue(ExecuteQuery(sqlFromQueryBuilder)); }