public string BuildQuery()
{
string Query = "DELETE ";
Query += _selectedTables[0] + " ";
//// set value..
//if (_updateColumns.Count > 0)
//{
// Query += "SET ";
// foreach (KeyValuePair<string, string> pair in _updateColumns)
// {
// Query += pair.Key.ToString() +" = "+ pair.Value.ToString() + ',';
// }
// Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
// Query += ' ';
//}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(_contains);
}
return(Query);
}
private string UpdateQuery(string table, Dictionary <string, string> column)
{
var keyValue = string.Empty;
string Query = "UPDATE " + table;
Query += " SET ";
if (column.Count > 0)
{
foreach (KeyValuePair <string, string> pair in column)
{
if (!string.IsNullOrEmpty(pair.Value))
{
keyValue += pair.Key.ToString() + " = " + "'" + pair.Value.ToString() + "'" + ",";
}
}
keyValue = keyValue.TrimEnd(',');
Query += keyValue;
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(_contains, table);
}
return(Query + " ");
}
public void Test_WhereStatement()
{
//WhereStatement 是Where从句
//{Level1}((Age<15 OR Age>=20) AND (strEmail LIKE 'e%')) OR {Level2}(Age BETWEEN 15 AND 20)，同一个LEVEL用AND连接，不同LEVEL用OR连接
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserEntity entity = new UserEntity();
List <DbParameter> parameters = new List <DbParameter>();
parameters.Clear();
WhereStatement statement = new WhereStatement(dbHelper);
statement.Add(new WhereClause(entity.FieldF1, Comparison.Equals, 1)); //and them together
statement.Add(new WhereClause(entity.FieldF2, Comparison.Equals, 2)); //and them together
string whereSql = statement.BuildWhereStatement();
string whereSqlWithParm = statement.BuildWhereStatement(true, out parameters);
Console.WriteLine(whereSql + "，" + whereSqlWithParm);
parameters.Clear();
WhereStatement statement1 = new WhereStatement(dbHelper);
statement1.Add(new WhereClause(entity.FieldF1, Comparison.In, new string[] { "1", "2" }), 1); //or them together
statement1.Add(new WhereClause(entity.FieldF1, Comparison.Equals, 2), 2); //or them together
string whereSql1 = statement1.BuildWhereStatement();
string whereSqlWithParm1 = statement1.BuildWhereStatement(true, out parameters);
Console.WriteLine(whereSql1 + "，" + whereSqlWithParm1);
parameters.Clear();
WhereStatement statement2 = new WhereStatement(dbHelper);
statement2.Add(entity.FieldF1, Comparison.Like, "1%'", 1); //same as new WhereClause
statement2.Add(entity.FieldF2, Comparison.NotLike, "1%'--", 1); //same as new WhereClause
statement2.Add(entity.FieldF2, Comparison.Equals, "1", 2); //same as new WhereClause
string whereSql2 = statement2.BuildWhereStatement();
string whereSqlWithParm2 = statement2.BuildWhereStatement(true, out parameters);
Console.WriteLine(whereSql2 + "，" + whereSqlWithParm2);
}
public void Test_WhereClause()
{
//WhereClause 是where从句的子句，
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserEntity entity = new UserEntity();
WhereClause clause = new WhereClause(entity.FieldF1, Comparison.Equals, 1);
clause.AddClause(LogicOperator.Or, Comparison.In, new int[] { 1, 2, 3 });// TODO
WhereStatement statement = new WhereStatement(dbHelper);
statement.Add(clause);
string where = statement.BuildWhereStatement();
Assert.AreEqual(@"(F1 = 1 OR F1 IN ('1', '2', '3'))", where.Trim());
Console.WriteLine(where);
WhereClause clause1 = new WhereClause(entity.FieldF1, Comparison.Equals, 1);
clause1.AddClause(LogicOperator.Or, Comparison.In, 1);// TODO
WhereStatement statement1 = new WhereStatement(dbHelper);
statement1.Add(clause1);
string where1 = statement1.BuildWhereStatement();
Assert.AreEqual(@"(F1 = 1 OR F1 IN (1))", where1.Trim());
Console.WriteLine(where1);
WhereClause clause2 = new WhereClause(entity.FieldF1, Comparison.Equals, 1);
clause2.AddClause(LogicOperator.Or, Comparison.In, new string[] { "1", "2", "3" });// TODO
WhereStatement statement2 = new WhereStatement(dbHelper);
statement2.Add(clause2);
string where2 = statement2.BuildWhereStatement();
Assert.AreEqual(@"(F1 = 1 OR F1 IN ('1', '2', '3'))", where2.Trim());
Console.WriteLine(where2);
WhereClause clause3 = new WhereClause(entity.FieldF1, Comparison.Equals, DBNull.Value);
clause3.AddClause(LogicOperator.Or, Comparison.In, new string[] { "1", "2", "3" });// TODO
WhereStatement statement3 = new WhereStatement(dbHelper);
statement3.Add(clause3);
string where3 = statement3.BuildWhereStatement();
Assert.AreEqual(@"(F1 IS NULL OR F1 IN ('1', '2', '3'))", where3.Trim());
Console.WriteLine(where3);
}
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 usedDbCommand = null;
if (buildCommand)
{
usedDbCommand = _dbProviderFactory.CreateCommand();
}
const string starter = "DELETE FROM ";
if (Table.Length == 0)
{
throw new Exception("Table to delete from was not set.");
}
var starterCleaned = starter + AddBrakets(Table);
if (_whereStatement.ClauseLevels > 0)
{
starterCleaned = starterCleaned + " WHERE " +
_whereStatement.BuildWhereStatement(buildCommand, ref usedDbCommand);
}
else if (!EnableClear)
{
throw new Exception("Statement would delete all records in table" + Table +
". If this is want you want, set the EnableClear property to true.");
}
if (!buildCommand)
{
return(starterCleaned);
}
Debug.Assert(usedDbCommand != null, "usedDbCommand != null");
usedDbCommand.CommandText = starterCleaned;
return(usedDbCommand);
}
private string SelectInsertQuery(string table2, string table1, Dictionary <string, string> columns)
{
string Query = "INSERT INTO " + table2;
var keys = "";
var values = "";
foreach (KeyValuePair <string, string> pair in columns)
{
keys += pair.Key.ToString() + ',';
values += (string.IsNullOrEmpty(pair.Value)) ? pair.Key.ToString() + ',' : "'" + pair.Value.ToString() + "'" + ',';
}
keys = keys.TrimEnd(','); // Trim de last comma inserted by foreach loop
values = values.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += "(" + keys + ") ";
Query += "SELECT " + values;
Query += " FROM " + table1;
// if (column.Count > 0)
// {
// var keys = "";
// var values = "";
// foreach (KeyValuePair<string, string> pair in column)
// {
// keys += pair.Key.ToString() + ',';
// values += "'" + pair.Value.ToString() + "'" + ',';
// }
// keys = keys.TrimEnd(',');
// values = values.TrimEnd(',');
// Query += "(" + keys + ") " + " VALUES (" + values + ") ";
// }
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(_contains);
}
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.Trim());
}
}
public string getQueryPartWithoutSelect(string sortColumn, bool ascending)
{
string Query = "";
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (Table Tab in _selectedTables)
{
if (Tab is DerivedTable)
{
Query += "(" + ((DerivedTable)Tab).Query + ") " + Tab.AliasName + ",";
}
else if (Tab is Table)
{
if (Tab.SchemaName == null || Tab.SchemaName.Trim().Equals(""))
{
Query += Tab.Name + " " + Tab.AliasName + ",";
}
else
{
Query += Tab.SchemaName + "." + Tab.Name + " " + Tab.AliasName + ",";
}
}
}
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;
}
string toTableStr = "";
if (Clause.ToTable is DerivedTable)
{
toTableStr += "(" + ((DerivedTable)Clause.ToTable).Query + ")";
}
else if (Clause.ToTable is Table)
{
if (Clause.ToTable.SchemaName == null || Clause.ToTable.SchemaName.Trim().Equals(""))
{
toTableStr += Clause.ToTable.Name;
}
else
{
toTableStr += Clause.ToTable.SchemaName + "." + Clause.ToTable.Name;
} //toTableStr += Clause.ToTable.Name;
}
JoinString += " " + toTableStr + " " + Clause.ToTable.AliasName + " ON ";
//JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable.AliasName + '.' + "`"+Clause.FromColumn+"`", Clause.ComparisonOperator, Clause.ToTable.AliasName + '.' + "`"+Clause.ToColumn+"`");
JoinString += Clause.JoinCondition.BuildWhereStatement();
Query += JoinString + ' ';
}
}
// Check the computed column and add the joins according ly for GROUP_FIRST, GROUP_LAST etc
Query += getComputeColumnsFromClauseQuery();
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (Column Col in _groupByColumns)
{
Query += SelectQueryBuilder.getColumnPartQuery(Col.Name) + ',';
}
// Add cross Tabulation column as group by in query
if (CrossTabClause.Col != null)
{
Query += SelectQueryBuilder.getColumnPartQuery(CrossTabClause.Col.Name) + ',';
}
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");
}
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
// Output OrderBy statement
if (_orderByStatement.Count > 0 || sortColumn != null)
{
Query += " ORDER BY ";
string OrderByClause = "";
if (sortColumn != null)
{
if (ascending)
{
OrderByClause = SelectQueryBuilder.getColumnPartQuery(sortColumn) + " ASC,";
}
else
{
OrderByClause = SelectQueryBuilder.getColumnPartQuery(sortColumn) + " DESC,";
}
}
foreach (OrderByClause Clause in _orderByStatement)
{
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause += SelectQueryBuilder.getColumnPartQuery(Clause.FieldName) + " ASC,"; break;
case Sorting.Descending:
OrderByClause += SelectQueryBuilder.getColumnPartQuery(Clause.FieldName) + " DESC,"; break;
}
}
Query += OrderByClause;
Query = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
// Return the built query
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 System.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)
{
var alias = string.Empty;
if (_selectedColumnsAndAlise.Count > 0)
{
var matching = _selectedColumnsAndAlise.FirstOrDefault(t => t.Column == ColumnName).Alias;
if (matching != null)
{
alias = matching.Replace(@".", "_");
}
}
else
{
// alias = ColumnName.Replace(@".", "");
}
if (alias != string.Empty)
{
Query += ColumnName + " as [" + alias + "],";
}
else
{
Query += ColumnName + ",";
}
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output Row Number clause
if (_rowNumberClause.FieldName != "")
{
var sortOrderName = "";
var sortOrderValue = "";
if (_orderByStatement != null && _orderByStatement.Count > 0)
{
OrderByClause clause = _orderByStatement[0];
switch (clause.SortOrder)
{
case Sorting.Ascending:
sortOrderValue = " ASC"; break;
case Sorting.Descending:
sortOrderValue = " DESC"; break;
}
sortOrderName = clause.FieldName;
}
else
{
switch (_rowNumberClause.SortOrder)
{
case Sorting.Ascending:
sortOrderValue = " ASC"; break;
case Sorting.Descending:
sortOrderValue = " DESC"; break;
}
sortOrderName = _rowNumberClause.FieldName;
}
Query += ", ROW_NUMBER() OVER(ORDER BY " + sortOrderName + " " + sortOrderValue + ") AS RowNumber ";
// var sorOrderValue = String.Empty;
// switch (_rowNumberClause.SortOrder)
// {
// case Sorting.Ascending:
// sorOrderValue = " ASC"; break;
// case Sorting.Descending:
// sorOrderValue = " DESC"; break;
// }
// Query += ", ROW_NUMBER() OVER(ORDER BY " + _rowNumberClause.FieldName + " " + sorOrderValue + ") AS RowNumber ";
}
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
if (_selectedTablesAlias.Count > 0)
{
Query += _selectedTables[0] + " as " + _selectedTablesAlias[0] + " ";
}
else
{
Query += _selectedTables[0];
}
}
// 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 + " AS " + Clause.ToAlias + " ON ";
//JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable + '.' + Clause.FromColumn, Clause.ComparisonOperator, new SqlLiteral(Clause.ToTable + '.' + Clause.ToColumn));
JoinString += WhereStatement.CreateComparisonClause(Clause.FromAlias + '.' + Clause.FromColumn, Clause.ComparisonOperator, new SqlLiteral(Clause.ToAlias + '.' + Clause.ToColumn));
Query += JoinString + ' ';
}
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
if (buildCommand)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(true, ref command, _contains);
}
else
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(_contains);
}
// Query += "AND CONTAINS((Name, Color), 'Red')";
}
// 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 System.Exception("Having statement was set without Group By");
}
if (buildCommand)
{
Query += " HAVING " + _havingStatement.BuildWhereStatement(true, ref command, _contains);
}
else
{
Query += " HAVING " + _havingStatement.BuildWhereStatement(_contains);
}
}
// Output OrderBy statement
if (_orderByStatement.Count > 0 && _rowNumberClause.FieldName == null)
{
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);
}
}
