// ------------------------------------------
// ACCESSORS
// ------------------------------------------
#region Accessors
// SQL commands
/// <summary>
/// Gets the SQL text of the specified query.
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterMode">Indicates whether parameters are replaced.</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 SQL text of the specified query.</returns>
public string CreateCommandText(
IDbQuery query,
DbQueryParameterMode parameterMode = DbQueryParameterMode.ValueInjected,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string sqlText = "";
if (QueryBuilder == null)
{
log?.AddError("Data builder missing");
}
else
{
var subLog = new BdoLog();
sqlText = QueryBuilder.BuildQuery(query, parameterMode, parameterSet, scriptVariableSet, subLog);
log?.AddEvents(subLog);
if (subLog.HasErrorsOrExceptions())
{
return(StringHelper.__NoneString);
}
}
return(sqlText);
}
/// <summary>
/// Creates a command from the specified query.
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterMode">Indicates whether parameters are replaced.</param>
/// <param name="parameterSet">The parameter elements to consider.</param>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the database command.</returns>
public override IDbCommand CreateCommand(
IDbQuery query,
DbQueryParameterMode parameterMode,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
var command = new NpgsqlCommand(CreateCommandText(query, parameterMode, parameterSet, scriptVariableSet, log));
if (query.ParameterSet != null)
{
foreach (var parameter in query.ParameterSet.Items)
{
command.Parameters.AddWithValue(parameter.Name, parameter.GetValue());
}
}
return(command);
}
/// <summary>
/// Builds the SQL text from the specified database query.
/// </summary>
/// <param name="query">The database data query to build.</param>
/// <param name="parameterMode">The display mode of parameters to consider.</param>
/// <param name="parameterSet">The parameter set to consider.</param>
/// <param name="scriptVariableSet">The interpretation variables to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the built query text.</returns>
public string BuildQuery(
IDbQuery query,
DbQueryParameterMode parameterMode = DbQueryParameterMode.ValueInjected,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
var queryString = "";
if (query != null)
{
try
{
if (query is DbSingleQuery singleDbQuery)
{
(scriptVariableSet ??= new ScriptVariableSet()).SetDbBuilder(this);
queryString = GetSqlText_Query(singleDbQuery, parameterSet, scriptVariableSet, log);
}
else if (query is DbCompositeQuery compositeDbQuery)
{
(scriptVariableSet ??= new ScriptVariableSet()).SetDbBuilder(this);
queryString = GetSqlText_Query(compositeDbQuery, parameterSet, scriptVariableSet, log);
}
else if (query is DbStoredQuery storedDbQuery)
{
if (!storedDbQuery.QueryTexts.TryGetValue(Id, out queryString))
{
queryString = BuildQuery(storedDbQuery.Query, DbQueryParameterMode.Scripted, parameterSet, scriptVariableSet, log);
storedDbQuery.QueryTexts.Add(Id, queryString);
}
}
if (query.SubQueries != null)
{
for (int i = 0; i < query.SubQueries.Count; i++)
{
var subQuery = query.SubQueries[i];
var subQueryString = BuildQuery(subQuery, parameterMode, parameterSet, scriptVariableSet, log);
queryString = queryString.Replace((i + 1).ToString().AsQueryWildString(), subQueryString);
}
}
if (parameterMode != DbQueryParameterMode.Scripted)
{
parameterSet ??= new DataElementSet();
UpdateParameterSet(parameterSet, query);
if (query is DbStoredQuery storedDbQuery)
{
UpdateParameterSet(parameterSet, storedDbQuery.Query);
}
if (parameterSet?.Items != null)
{
foreach (var parameter in parameterSet.Items)
{
if (parameterMode == DbQueryParameterMode.ValueInjected)
{
queryString = queryString.Replace((parameter?.Name ?? parameter.Index.ToString())?.AsParameterWildString(),
GetSqlText_Value(parameter?.GetValue(Scope, scriptVariableSet, log), parameter.ValueType));
}
else
{
queryString = queryString.Replace((parameter?.Name ?? parameter.Index.ToString())?.AsParameterWildString(),
"@" + parameter?.Name ?? parameter.Index.ToString());
}
}
}
}
}
catch (Exception ex)
{
log?.AddError(
"Error trying to build query '" + (query?.Name ?? "(Undefinied)") + "'",
description: ex.ToString() + ". Built query is : '" + queryString + "'.");
}
}
return(queryString);
}
/// <summary>
/// Creates a command from the specified query.
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterMode">Indicates whether parameters are replaced.</param>
/// <param name="parameterSet">The parameter elements to consider.</param>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the database command.</returns>
public abstract IDbCommand CreateCommand(
IDbQuery query,
DbQueryParameterMode parameterMode,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null);
