// Extensions
/// <summary>
/// Adds filters to the specified database query considering the specified filter query string.
/// </summary>
/// <param name="dbQuery">The database query to consider.</param>
/// <param name="filterQuery">The filter query string to consider.</param>
/// <param name="definition">The clause statement to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>The built query.</returns>
public static IDbSingleQuery Filter(
this IDbSingleQuery dbQuery,
string filterQuery,
DbApiFilterDefinition definition = null,
IBdoLog log = null)
{
if (dbQuery != null && !string.IsNullOrEmpty(filterQuery))
{
string scriptText = filterQuery.ConvertToExtensionScript(log, definition);
if (scriptText?.Length > 0)
{
if (dbQuery.WhereClause?.Expression != null && !string.IsNullOrEmpty(dbQuery.WhereClause?.Expression?.Text))
{
dbQuery.WhereClause.Expression.Text = "$sqlAnd(" + dbQuery.WhereClause.Expression.Text + "," + scriptText + ")";
}
else
{
if (dbQuery.WhereClause == null)
{
dbQuery.WhereClause = new DbQueryWhereClause();
}
dbQuery.WhereClause.Expression = scriptText.CreateExpAsScript();
}
}
}
return(dbQuery);
}
// GroupBy -------------------------------------
private string GetSqlText_GroupByClause(
IDbQueryGroupByClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (clause != null)
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
else if (clause.Fields?.Count > 0)
{
foreach (DbField field in clause.Fields)
{
if (!string.IsNullOrEmpty(queryString))
{
queryString += ", ";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log);
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " group by " + queryString);
}
return(queryString);
}
/// <summary>
/// Builds the following query: Get the server instances.
/// </summary>
/// <param name="query">The database query to consider.</param>
/// <param name="pageSize">The page size to consider.</param>
/// <param name="pageToken">The page token text to consider.</param>
/// <param name="log">The log to consider.</param>
/// <param name="clauseStatement">The clause statement to consider.</param>
/// <returns>The built query.</returns>
public static IDbSingleQuery Paginate(
this IDbSingleQuery query,
int?pageSize,
string pageToken,
IBdoLog log = null,
DbApiSortDefinition clauseStatement = null)
{
return(query);
}
// OrderBy -------------------------------------
private string GetSqlText_OrderByClause(
IDbQueryOrderByClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (clause != null)
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
else if (clause.Statements?.Count > 0)
{
foreach (var statement in clause.Statements)
{
if (!string.IsNullOrEmpty(queryString))
{
queryString += ", ";
}
if (statement.Sorting == DataSortingModes.Random)
{
queryString += "newid()";
}
else
{
queryString += GetSqlText_Field(
statement.Field, query,
parameterSet,
DbQueryFieldMode.OnlyName,
scriptVariableSet: scriptVariableSet, log: log);
switch (statement.Sorting)
{
case DataSortingModes.Ascending:
queryString += " asc";
break;
case DataSortingModes.Descending:
queryString += " desc";
break;
}
}
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " order by " + queryString);
}
return(queryString);
}
// Union -------------------------------------
/// <summary>
///
/// </summary>
/// <param name="unionClause">The union clause to consider.</param>
public IDbSingleQuery Union(DbQueryUnionKind kind, IDbSingleQuery query)
{
if (UnionClauses == null)
{
UnionClauses = new List <DbQueryUnionClause>();
}
UnionClauses.Add(new DbQueryUnionClause()
{
Kind = kind, Query = query
});
return(this);
}
/// <summary>
/// Sorts the specified query considering the specified query script.
/// </summary>
/// <param name="query">The database query to consider.</param>
/// <param name="sortQuery">The sort query text to consider.</param>
/// <param name="definition">The definition to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>The built query.</returns>
public static IDbSingleQuery Sort(
this IDbSingleQuery query,
string sortQuery,
DbApiSortDefinition definition = null,
IBdoLog log = null)
{
if (query != null && !string.IsNullOrEmpty(sortQuery))
{
query.OrderByClause = new DbQueryOrderByClause();
foreach (string fieldItem in sortQuery.Split(','))
{
var statement = new DbQueryOrderByStatement();
var fieldItemParams = fieldItem?.Trim().Split(' ');
if (fieldItemParams.Length > 0)
{
string fieldName = fieldItemParams[0]?.Trim();
if (!definition.ContainsKey(fieldName))
{
log?.AddError("Undefined field '" + fieldName + "' in order statement", resultCode: "user");
}
else
{
statement.Field = definition?[fieldName]?.Field ?? DbFluent.Field(fieldName);
statement.Sorting = DataSortingModes.Ascending;
if (fieldItemParams.Length > 1)
{
string direction = fieldItemParams[1]?.Trim();
if (string.Equals(direction, "desc"))
{
statement.Sorting = DataSortingModes.Descending;
query.OrderByClause.Statements.Add(statement);
}
else if (!string.Equals(direction, "asc"))
{
log?.AddError("Invalid order direction '" + direction + "'", resultCode: "user");
}
else
{
query.OrderByClause.Statements.Add(statement);
}
}
}
}
}
}
return(query);
}
// Union -------------------------------------
private string GetSqlText_UnionClause(
IDbQueryUnionClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (query != null && clause != null)
{
string subQuery = BuildQuery(clause.Query, DbQueryParameterMode.Scripted, parameterSet, scriptVariableSet, log);
UpdateParameterSet(query.ParameterSet, clause.Query);
queryString += "(" + subQuery + ")";
queryString = queryString.If(!string.IsNullOrEmpty(queryString), "union " + queryString);
}
return(queryString);
}
// Having -------------------------------------
private string GetSqlText_HavingClause(
IDbQueryHavingClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (clause != null)
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " having " + queryString);
}
return(queryString);
}
// Where -------------------------------------
private string GetSqlText_WhereClause(
IDbQueryWhereClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (clause != null)
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
if (clause.IdFields?.Count > 0)
{
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " (" + queryString + ") ");
foreach (DbField field in clause.IdFields)
{
if (!string.IsNullOrEmpty(queryString))
{
queryString += " and ";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.NameEqualsValue,
scriptVariableSet: scriptVariableSet, log: log);
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " where " + queryString);
}
return(queryString);
}
// Builds single query ----------------------
/// <summary>
/// Builds the SQL text of the specified basic query.
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterSet">The parameter set to consider.</param>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the built query text.</returns>
protected abstract string GetSqlText_Query(
IDbSingleQuery query,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null);
/// <summary>
/// Builds the specified simple database data query and put the result
/// into the specified string MS Sql Server query.
/// <remarks>We assume the query already exits.</remarks>
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterSet">The parameter set to consider.</param>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the built query text.</returns>
protected override string GetSqlText_Query(
IDbSingleQuery query,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
var queryString = "";
if (query == null)
{
return(null);
}
if (query.CTETables?.Count > 0)
{
queryString += "with ";
if (query?.IsCTERecursive == true)
{
queryString += "recursive ";
}
queryString += string.Join(", ", query.CTETables.Select(table => GetSqlText_Table(
table, query, parameterSet, DbQueryTableMode.AliasAsCompleteName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log)))
.ConcatenateIfFirstNotEmpty(" ");
}
// we build the query
switch (query.Kind)
{
// Select
case DbQueryKind.Select:
{
queryString += "select ";
if (query.IsDistinct)
{
queryString += "distinct ";
}
if (query.Fields?.Count > 0)
{
queryString += string.Join(", ", query.Fields.Select(field => GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log)))
.ConcatenateIfFirstNotEmpty(" ");
}
else
{
queryString += "* ";
}
queryString += GetSqlText_FromClause(query.FromClause, query, DbQueryFromClauseKind.FromPreffix, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_WhereClause(query.WhereClause, query, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_GroupByClause(query.GroupByClause, query, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_HavingClause(query.HavingClause, query, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_OrderByClause(query.OrderByClause, query, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
if (query.Limit > -1)
{
queryString += "limit " + query.Limit.ToString();
}
if (query.UnionClauses?.Count > 0)
{
foreach (var clause in query.UnionClauses)
{
queryString += GetSqlText_UnionClause(clause, query, parameterSet, scriptVariableSet, log);
}
}
}
break;
// Update
case DbQueryKind.Update:
{
queryString += "update ";
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteNameAsAlias, query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += "set ";
queryString += string.Join(", ", query.Fields.Select(field =>
GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.NameEqualsValue,
scriptVariableSet: scriptVariableSet, log: log)))
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_FromClause(query.FromClause, query, DbQueryFromClauseKind.FromPreffix, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_WhereClause(query.WhereClause, query, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
if (query.ReturnedIdFields?.Count > 0)
{
queryString += "returning ";
queryString += string.Join(", ", query.ReturnedIdFields.Select(field =>
GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log)));
}
}
break;
// Delete
case DbQueryKind.Delete:
{
queryString += "delete ";
queryString += GetSqlText_FromClause(query.FromClause, query, DbQueryFromClauseKind.FromPreffix, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += GetSqlText_WhereClause(query.WhereClause, query, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
if (query.ReturnedIdFields?.Count > 0)
{
queryString += "returning ";
queryString += string.Join(", ", query.ReturnedIdFields.Select(field =>
GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log)));
}
}
break;
// Insert
case DbQueryKind.Insert:
{
queryString += "insert into ";
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteName, query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log)
.ConcatenateIfFirstNotEmpty(" ");
queryString += "(";
queryString += string.Join(", ", query.Fields.Select(field =>
GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.OnlyName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log)));
queryString += ") ";
queryString += GetSqlText_FromClause(query.FromClause, query, DbQueryFromClauseKind.NoPreffix, parameterSet, scriptVariableSet, log)
.ConcatenateIfFirstNotEmpty(" ");
if (query.ReturnedIdFields?.Count > 0)
{
queryString += "returning ";
queryString += string.Join(", ", query.ReturnedIdFields.Select(field =>
GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log)));
}
}
break;
}
return(queryString);
}
// From -------------------------------------
private string GetSqlText_FromClause(
IDbQueryFromClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (clause == null)
{
// we add the query's default table
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
query, parameterSet, DbQueryFieldMode.CompleteName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
else if (!(clause?.Statements?.Count > 0))
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else
{
foreach (var statement in clause.Statements)
{
// if the first table is not a joined one then we add first the query's default table
if (statement.Tables == null || statement.Tables.Count == 0 || statement.Tables[0] is DbJoinedTable)
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else if (statement.Tables?.Count > 0)
{
if (query?.Kind == DbQueryKind.Delete)
{
foreach (var table in statement.Tables)
{
queryString += GetSqlText_Table(
table,
query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
}
else
{
foreach (var table in statement.Tables)
{
queryString += GetSqlText_Table(
table,
query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
}
}
}
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " from " + queryString);
return(queryString);
}
// From -------------------------------------
private string GetSqlText_FromClause(
IDbQueryFromClause clause,
IDbSingleQuery query = null,
DbQueryFromClauseKind kind = DbQueryFromClauseKind.FromPreffix,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (query != null)
{
if (query.Kind == DbQueryKind.Insert)
{
if ((clause?.Statements?.Count == 1 &&
clause?.Statements[0]?.Tables.Any(p => p is DbDerivedTable) != true) ||
(query.WhereClause != null))
{
var subQuery = DbFluent.SelectQuery(null)
.WithFields(query.Fields?.ToArray());
subQuery.FromClause = query.FromClause;
subQuery.WhereClause = query.WhereClause;
clause = new DbQueryFromClause
{
Statements = new List <DbQueryFromStatement>()
};
clause.Statements.Add(new DbQueryFromStatement()
{
Tables = new List <DbTable>()
{
DbFluent.TableAsQuery(subQuery)
}
});
}
}
if (clause == null)
{
if (query.Kind == DbQueryKind.Insert)
{
var table = DbFluent.TableAsTuples(
DbFluent.Tuple(query.Fields?.ToArray()));
queryString += GetSqlText_Table(
table, query, parameterSet,
DbQueryTableMode.CompleteName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else if (query.Kind != DbQueryKind.Update)
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
}
else
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
else if (!(clause?.Statements?.Count > 0))
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else
{
foreach (var statement in clause.Statements)
{
// if the first table is not a joined one then we add first the query's default table
if (statement.Tables == null || statement.Tables.Count == 0 || statement.Tables[0] is DbJoinedTable)
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
if (statement.Tables?.Count > 0)
{
var existingTbale = false;
foreach (var table in statement.Tables)
{
if (query?.Kind == DbQueryKind.Delete && table is DbJoinedTable joinedTable)
{
queryString += !existingTbale ? " using " : ", ";
queryString += GetSqlText_Table(
joinedTable.Table,
query, parameterSet, DbQueryTableMode.CompleteNameAsAlias,
scriptVariableSet: scriptVariableSet, log: log);
existingTbale = true;
// we update the where clause to add relationships
if (query?.WhereClause == null)
{
query.WhereClause = new DbQueryWhereClause();
}
if (query.WhereClause.Expression == null)
{
query.WhereClause.Expression = joinedTable.Condition;
}
else
{
query.WhereClause.Expression = DbFluent.And(query.WhereClause.Expression, joinedTable.Condition);
}
}
else
{
queryString += GetSqlText_Table(
table,
query, parameterSet, DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
}
}
}
}
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString),
(kind == DbQueryFromClauseKind.FromPreffix ? "from " : "") + queryString);
return(queryString);
}
/// <summary>
/// Builds the specified simple database data query and put the result
/// into the specified string MS Sql Server query.
/// <remarks>We assume the query already exits.</remarks>
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterSet">The parameter set to consider.</param>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the built query text.</returns>
protected override string GetSqlText_Query(
IDbSingleQuery query,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
var queryString = "";
int index;
// we build the query
switch (query.Kind)
{
// Select
case DbQueryKind.Select:
{
queryString = "select ";
if (query.IsDistinct)
{
queryString += " distinct ";
}
index = 0;
if (query.Fields?.Count > 0)
{
foreach (DbField field in query.Fields)
{
if (index > 0)
{
queryString += ",";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
}
else
{
queryString += " * ";
}
queryString += GetSqlText_FromClause(query.FromClause, query, parameterSet, scriptVariableSet, log);
queryString += GetSqlText_WhereClause(query.WhereClause, query, parameterSet, scriptVariableSet, log);
queryString += GetSqlText_GroupByClause(query.GroupByClause, query, parameterSet, scriptVariableSet, log);
queryString += GetSqlText_HavingClause(query.HavingClause, query, parameterSet, scriptVariableSet, log);
queryString += GetSqlText_OrderByClause(query.OrderByClause, query, parameterSet, scriptVariableSet, log);
if (query.Limit > -1)
{
queryString += " limit " + query.Limit.ToString() + " ";
}
}
break;
// Update
case DbQueryKind.Update:
{
queryString = "update ";
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias, query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
queryString += " set ";
index = 0;
foreach (DbField field in query.Fields)
{
if (index > 0)
{
queryString += ",";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.NameEqualsValue,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
if (query.FromClause != null)
{
queryString += GetSqlText_FromClause(query.FromClause, query, parameterSet, scriptVariableSet, log);
}
queryString += GetSqlText_WhereClause(query.WhereClause, query, parameterSet, scriptVariableSet, log);
if (query.ReturnedIdFields?.Count > 0)
{
queryString += " returning ";
index = 0;
foreach (DbField field in query.ReturnedIdFields)
{
if (index > 0)
{
queryString += ", ";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
}
}
break;
// Delete
case DbQueryKind.Delete:
{
queryString = "delete";
queryString += GetSqlText_FromClause(query.FromClause, query, parameterSet, scriptVariableSet, log);
queryString += GetSqlText_WhereClause(query.WhereClause, query, parameterSet, scriptVariableSet, log);
if (query.ReturnedIdFields?.Count > 0)
{
queryString += " returning ";
index = 0;
foreach (DbField field in query.ReturnedIdFields)
{
if (index > 0)
{
queryString += ", ";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
}
}
break;
// Insert
case DbQueryKind.Insert:
{
queryString = "insert into ";
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
query, parameterSet, DbQueryFieldMode.CompleteName, query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
queryString += " (";
index = 0;
foreach (DbField field in query.Fields)
{
if (index > 0)
{
queryString += ",";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.OnlyName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
queryString += ") values (";
if (query.Fields?.Count > 0)
{
index = 0;
foreach (DbField field in query.Fields)
{
if (index > 0)
{
queryString += ",";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.OnlyValue,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
}
queryString += ")";
if (query.ReturnedIdFields?.Count > 0)
{
queryString += " returning ";
index = 0;
foreach (DbField field in query.ReturnedIdFields)
{
if (index > 0)
{
queryString += ", ";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.CompleteNameOrValueAsAlias,
query.DataModule, query.Schema, query.DataTable,
scriptVariableSet: scriptVariableSet, log: log);
index++;
}
}
}
break;
}
return(queryString);
}
