public static WhereStatement Copy(WhereStatement statement)
{
WhereStatement result = new WhereStatement();
int currentLevel = 0;
foreach (List <WhereClause> level in statement)
{
currentLevel++;
result.Add(new List <WhereClause>());
foreach (WhereClause clause in statement[currentLevel - 1])
{
WhereClause clauseCopy = new WhereClause(clause.FieldName, clause.ComparisonOperator, clause.Value);
foreach (WhereClause.SubClause subClause in clause.SubClauses)
{
WhereClause.SubClause subClauseCopy = new WhereClause.SubClause(subClause.LogicOperator, subClause.ComparisonOperator, subClause.Value);
clauseCopy.SubClauses.Add(subClauseCopy);
}
result[currentLevel - 1].Add(clauseCopy);
}
}
return(result);
}
/// <summary>
/// This static method combines 2 where statements with eachother to form a new statement， 两个 WhereStatement 联合
/// </summary>
/// <param name="statement1"></param>
/// <param name="statement2"></param>
/// <returns></returns>
public static WhereStatement CombineStatements(WhereStatement statement1, WhereStatement statement2)
{
// statement1: {Level1}((Age<15 OR Age>=20) AND (strEmail LIKE 'e%') OR {Level2}(Age BETWEEN 15 AND 20))
// Statement2: {Level1}((Name = 'Peter'))
// Return statement: {Level1}((Age<15 or Age>=20) AND (strEmail like 'e%') AND (Name = 'Peter'))
// Make a copy of statement1
WhereStatement result = WhereStatement.Copy(statement1);
// Add all clauses of statement2 to result
for (int i = 0; i < statement2.ClauseLevels; i++) // for each clause level in statement2
{
List <WhereClause> level = statement2[i];
foreach (WhereClause clause in level) // for each clause in level i
{
for (int j = 0; j < result.ClauseLevels; j++) // for each level in result, add the clause
{
result.AddWhereClauseToLevel(clause, j);
}
}
}
return(result);
}
/// <summary>
/// 创建 查询语句
/// </summary>
/// <returns>Returns a string containing the query, or a DbCommand containing a command with parameters</returns>
private string BuildQuery(bool buildCommand, out List <DbParameter> dbParameters)
{
List <DbParameter> parameters = new List <DbParameter>();
string Query = "SELECT ";
// Output Distinct
if (_distinct)
{
Query += "DISTINCT ";
}
// Output Top clause
if (!(_topClause.Quantity == 100 & _topClause.Unit == TopUnit.Percent))
{
Query += "TOP " + _topClause.Quantity;
if (_topClause.Unit == TopUnit.Percent)
{
Query += " PERCENT";
}
Query += " ";
}
// Output column names
if (_selectedColumns.Count == 0)
{
if (_fromTables.Count == 1)
{
Query += _fromTables[0] + "."; // By default only select * from the table that was selected. If there are any joins, it is the responsibility of the user to select the needed columns.
}
Query += "*";
}
else
{
foreach (string ColumnName in _selectedColumns)
{
Query += ColumnName + ',';
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output table names
if (_fromTables.Count > 0)
{
Query += " FROM ";
foreach (string TableName in _fromTables)
{
Query += TableName + ',';
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output joins
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 + ' ';
}
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
if (buildCommand)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(true, out parameters);
this._parameters.AddRange(parameters);
}
else
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
}
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (string Column in _groupByColumns)
{
Query += Column + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
if (buildCommand)
{
Query += " HAVING " + _havingStatement.BuildWhereStatement(true, out parameters);
this._parameters.AddRange(parameters);
}
else
{
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
}
// Output OrderBy statement
Query += _orderByStatement.BuildOrderByStatement();
// Return the build command
dbParameters = this._parameters;
return(Query);
}
protected WhereStatement _havingStatement = null; // HAVING 从句
public SelectQueryBuilder(IDbHelper dbHelper)
: base(dbHelper)
{
_havingStatement = new WhereStatement(dbHelper);// HAVING 从句
}
/// <summary>
/// Builds the select query
/// </summary>
/// <returns>Returns a string containing the query, or a DbCommand containing a command with parameters</returns>
private object BuildQuery(bool buildCommand)
{
if (buildCommand && _dbProviderFactory == null)
{
throw new Exception("Cannot build a command when the Db Factory hasn't been specified. Call SetDbProviderFactory first.");
}
DbCommand command = null;
if (buildCommand)
{
command = _dbProviderFactory.CreateCommand();
}
string Query = "SELECT ";
// Output Distinct
if (_distinct)
{
Query += "DISTINCT ";
}
// Output Top clause
if (!(_topClause.Quantity == 100 & _topClause.Unit == TopUnit.Percent))
{
Query += "TOP " + _topClause.Quantity;
if (_topClause.Unit == TopUnit.Percent)
{
Query += " PERCENT";
}
Query += " ";
}
// Output column names
if (_selectedColumns.Count == 0)
{
if (_selectedTables.Count == 1)
{
Query += _selectedTables[0] + "."; // By default only select * from the table that was selected. If there are any joins, it is the responsibility of the user to select the needed columns.
}
Query += "*";
}
else
{
foreach (string ColumnName in _selectedColumns)
{
Query += ColumnName + ',';
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (string TableName in _selectedTables)
{
Query += TableName + ',';
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output joins
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 + ' ';
}
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
if (buildCommand)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(true, ref command);
}
else
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
}
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (string Column in _groupByColumns)
{
Query += Column + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
if (buildCommand)
{
Query += " HAVING " + _havingStatement.BuildWhereStatement(true, ref command);
}
else
{
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
}
// Output OrderBy statement
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(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
if (buildCommand)
{
// Return the build command
command.CommandText = Query;
return(command);
}
else
{
// Return the built query
return(Query);
}
}