public string BuildQuery()
{
string queryString = "DELETE FROM " + _table;
if (_whereStatement.ClauseLevels > 0)
{
queryString += " WHERE " + _whereStatement.BuildWhereStatement();
}
queryString += ";";
return queryString;
}
public string BuildQuery()
{
string queryString = "UPDATE " + _table + " SET";
for (int i = 0; i < _set.Count; i++)
{
queryString += " " + _set.Keys[i] + "=" + Escape.EscapeString(_set[_set.Keys[i]]) + (i + 1 == _set.Count ? ("") : (","));
}
if (_whereStatement.ClauseLevels > 0)
{
queryString += " WHERE " + _whereStatement.BuildWhereStatement();
}
queryString += ";";
return(queryString);
}
/// <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 (_limitClause.Limit > 0)
{
Query += "LIMIT " + _limitClause.Limit;
if (_limitClause.Offset > 0)
{
Query += " OFFSET " + _limitClause.Offset;
}
Query += " ";
}
if (_limitClause.Offset > 0 && _limitClause.Limit <= 0)
{
Query += " OFFSET " + _limitClause.Offset;
Query += " ";
}
if (buildCommand)
{
// Return the build command
command.CommandText = Query;
return(command);
}
else
{
// Return the built query
return(Query);
}
}