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