public static WhereStatement Copy(WhereStatement statement)
{
int currentLevel = 0;
var result = new WhereStatement();
foreach (var level in statement)
{
currentLevel++;
result.Add(new List <WhereClause>());
foreach (var clause in statement[currentLevel - 1])
{
var clauseCopy = new WhereClause(clause.FieldName, clause.ComparisonOperator, clause.Value);
foreach (var subClause in clause.SubClauses)
{
clauseCopy.SubClauses.Add(new WhereClause.SubClause(subClause.LogicOperator, subClause.ComparisonOperator, subClause.Value));
}
result[currentLevel - 1].Add(clauseCopy);
}
}
return(result);
}
public static WhereStatement CombineStatements(WhereStatement statement1, WhereStatement statement2)
{
var result = WhereStatement.Copy(statement1);
for (int i = 0; i < statement2.ClauseLevels; i++)
{
var level = statement2[i];
foreach (var clause in level)
{
for (int j = 0; j < result.ClauseLevels; j++)
{
result.AddWhereClauseToLevel(clause, j);
}
}
}
return(result);
}
public string BuildQuery()
{
string Query = "SELECT ";
if (_distinct)
{
Query += "DISTINCT ";
}
if (!(_topClause.Quantity == 100 & _topClause.Unit == TopUnit.Percent))
{
Query += "TOP " + _topClause.Quantity;
if (_topClause.Unit == TopUnit.Percent)
{
Query += " PERCENT";
}
Query += " ";
}
if (_selectedColumns.Count == 0)
{
if (_selectedTables.Count == 1)
{
Query += _selectedTables[0] + ".";
}
Query += "*";
}
else
{
foreach (var CurrentColumn in _selectedColumns)
{
if (CurrentColumn.Alias == null)
{
Query += CurrentColumn.Name + ',';
}
else
{
Query += CurrentColumn.Name + " AS " + CurrentColumn.Alias + ',';
}
}
Query = Query.TrimEnd(',');
Query += ' ';
}
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (string TableName in _selectedTables)
{
Query += TableName + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
if (_joins.Count > 0)
{
foreach (JoinClause Clause in _joins)
{
string JoinString = "";
switch (Clause.JoinType)
{
case JoinType.InnerJoin:
JoinString = "INNER JOIN";
break;
case JoinType.OuterJoin:
JoinString = "OUTER JOIN";
break;
case JoinType.LeftJoin:
JoinString = "LEFT JOIN";
break;
case JoinType.RightJoin:
JoinString = "RIGHT JOIN";
break;
}
JoinString += " " + Clause.ToTable + " ON ";
JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable + '.' + Clause.FromColumn, Clause.ComparisonOperator, new SqlLiteral(Clause.ToTable + '.' + Clause.ToColumn));
Query += JoinString + ' ';
}
}
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (string Column in _groupByColumns)
{
Query += Column + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
if (_havingStatement.ClauseLevels > 0)
{
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
if (_orderByStatement.Count > 0)
{
Query += " ORDER BY ";
foreach (OrderByClause Clause in _orderByStatement)
{
string OrderByClause = "";
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause = Clause.FieldName + " ASC";
break;
case Sorting.Descending:
OrderByClause = Clause.FieldName + " DESC";
break;
}
Query += OrderByClause + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
return(Query);
}
