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 QueryBuilderParms GetQueryParmFromCountView(string selectedTable)
{
var queryBuilderParms = new QueryBuilderParms {
PrimaryTable = selectedTable
};
return(queryBuilderParms);
}
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 string BuildSqlFromQuery(string queryId)
{
var connection = ConfigurationManager.ConnectionStrings["O2DataMart"].ConnectionString;
var schemaRepository = new SchemaRepository(connection);
var criteriaRepository = new CriteriaRepository(_dbConnectionString);
var criteriaForQuery =
criteriaRepository.GetCriteriaForQuery(Convert.ToInt64(queryId))
.OrderBy(x => x.TableName)
.ThenBy(x => x.Sequence)
.ToList();
var primaryTable = criteriaForQuery[0].TableName;
var queryBuilderParms = new QueryBuilderParms();
queryBuilderParms.PrimaryTable = primaryTable;
var groupByTable = criteriaForQuery.GroupBy(b => b.TableName);
var groupedTables = groupByTable as IGrouping <string, CriteriaDto>[] ?? groupByTable.ToArray();
if (groupedTables.Count() > 1)
{
BuildJoinsForQuery(queryBuilderParms, criteriaForQuery, schemaRepository, groupedTables);
}
var criteriaSeqLastProcessed = 1;
if (!criteriaForQuery.Any())
{
return(Empty);
}
{
var currentTableName = criteriaForQuery[0].TableName;
QueryBuilderParmsForThisCritera(criteriaForQuery[0], schemaRepository, queryBuilderParms);
criteriaForQuery.RemoveAll(x => x.Sequence == criteriaSeqLastProcessed);
foreach (var criteriaItem in criteriaForQuery)
{
AddWhereClauseToCurrentQuery(criteriaItem, queryBuilderParms, schemaRepository);
criteriaSeqLastProcessed = criteriaItem.Sequence;
}
var queryBuilderConvertModelToSql = new QueryBuilderConvertModelToSql();
return(queryBuilderConvertModelToSql.ConvertSimpleTableQuery(queryBuilderParms));
}
}
public string ConvertSimpleTableQuery(QueryBuilderParms queryBuilderParms)
{
try
{
var query = new SelectQueryBuilder();
query.SelectFromTable(queryBuilderParms.PrimaryTable);
if (queryBuilderParms.IncludeColumns.Count < 1)
{
query.SelectAllColumns();
}
else
{
if (queryBuilderParms.IncludeColumns.Count > 0)
{
IncludeColumns(queryBuilderParms.IncludeColumns, query, queryBuilderParms.PrimaryTable);
}
}
if (queryBuilderParms.MaxRowsToReturn > 0)
{
query.TopRecords = queryBuilderParms.MaxRowsToReturn;
}
if (queryBuilderParms.JoinConditionsList.Count > 0)
{
AddJoinsClauses(queryBuilderParms.JoinConditionsList, query, queryBuilderParms.PrimaryTable);
}
if (queryBuilderParms.WhereConditionsList.Count > 0)
{
AddWhereClauses(queryBuilderParms.WhereConditionsList, query);
}
if (queryBuilderParms.ColumnSortAscDesc.Count > 0)
{
AddOrderByClause(queryBuilderParms.ColumnSortAscDesc, query);
}
string statement = $"{query.BuildQuery()}";
var finalStatement = PostProcessStatement(statement, queryBuilderParms);
return(finalStatement);
}
catch (Exception ex)
{
throw ex;
}
}
private void AddWhereClauseToCurrentQuery(CriteriaDto criteriaDto, QueryBuilderParms queryBuilderParms,
SchemaRepository schemaRepository)
{
var colDataType =
schemaRepository.GetSchemaTableColumns(criteriaDto.TableColumn)
.MetaData.Select(x => x.DbType)
.ToString();
queryBuilderParms.WhereConditionsList.Add(new WhereCondition
{
WhereLeftColumn = criteriaDto.TableColumn.Replace($"{criteriaDto.TableName}.", ""),
WhereLeftTable = $"dbo.{criteriaDto.TableName}",
WhereOperator = GetComparison(criteriaDto.CompareOperator),
WhereRightColumn = FixQuotes(colDataType, criteriaDto.CompareValue)
});
}
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));
}
private QueryBuilderParms QueryBuilderParmsForThisCritera(CriteriaDto criteriaDto,
SchemaRepository schemaRepository, QueryBuilderParms queryBuilderParms)
{
queryBuilderParms.PrimaryTable = criteriaDto.TableName;
if (IsNullOrEmpty(criteriaDto.CompareOperator))
{
return(queryBuilderParms);
}
var colDataType =
schemaRepository.GetSchemaTableColumns(criteriaDto.TableColumn)
.MetaData.Select(x => x.DbType)
.ToString();
queryBuilderParms.WhereConditionsList = new List <WhereCondition>
{
new WhereCondition
{
WhereLeftColumn = criteriaDto.TableColumn.Replace($"{criteriaDto.TableName}.", ""),
WhereLeftTable = $"dbo.{criteriaDto.TableName}",
WhereOperator = GetComparison(criteriaDto.CompareOperator), // Comparison.Equals,
WhereRightColumn = FixQuotes(colDataType, criteriaDto.CompareValue)
}
};
return(queryBuilderParms);
}
private static string PostProcessStatement(string statement, QueryBuilderParms queryBuilderParms)
{
return(statement);
}
private void BuildJoinsForQuery(QueryBuilderParms queryBuilderParms, List <CriteriaDto> criteriaForQuery,
SchemaRepository schemaRepository, IEnumerable <IGrouping <string, CriteriaDto> > groupedTables)
{
var criteriaArray = criteriaForQuery.ToArray();
var currentCriteria = 0;
var totalCriter = criteriaForQuery.Count;
if (criteriaArray[currentCriteria].TableName.ToLower().Contains("mortgage"))
{
queryBuilderParms.JoinConditionsList.Add(
new JoinCondition
{
JoinLeftTable = "BackBone",
JoinCompareType = Comparison.Equals,
JoinRightTable = "BackBone",
JoinOnLeftColumn = "MortgageId",
JoinOnRightColumn = "MortgageId",
TypeOfJoin = JoinType.InnerJoin
});
}
if (criteriaArray[currentCriteria].TableName.ToLower().Contains("persons"))
{
queryBuilderParms.JoinConditionsList.Add(
new JoinCondition
{
JoinLeftTable = "BackBone",
JoinCompareType = Comparison.Equals,
JoinRightTable = "BackBone",
JoinOnLeftColumn = "PersonId",
JoinOnRightColumn = "PersonId",
TypeOfJoin = JoinType.InnerJoin
});
}
if (criteriaArray[currentCriteria].TableName.ToLower().Contains("persons"))
{
queryBuilderParms.JoinConditionsList.Add(
new JoinCondition
{
JoinLeftTable = "BackBone",
JoinCompareType = Comparison.Equals,
JoinRightTable = "BackBone",
JoinOnLeftColumn = "PropertyId",
JoinOnRightColumn = "PropertyId",
TypeOfJoin = JoinType.InnerJoin
});
}
currentCriteria += 1;
for (var i = currentCriteria; i <= totalCriter - 1; i++)
{
if (criteriaArray[i].TableName.ToLower().Contains("persons"))
{
queryBuilderParms.JoinConditionsList.Add(
new JoinCondition
{
JoinLeftTable = "BackBone",
JoinCompareType = Comparison.Equals,
JoinRightTable = criteriaArray[i].TableName,
JoinOnLeftColumn = "PersonId",
JoinOnRightColumn = "PersonId",
TypeOfJoin = JoinType.InnerJoin
});
}
if (criteriaArray[i].TableName.ToLower().Contains("property"))
{
queryBuilderParms.JoinConditionsList.Add(
new JoinCondition
{
JoinLeftTable = "BackBone",
JoinCompareType = Comparison.Equals,
JoinRightTable = criteriaArray[i].TableName,
JoinOnLeftColumn = "PropertyId",
JoinOnRightColumn = "PropertyId",
TypeOfJoin = JoinType.InnerJoin
});
}
}
}
