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