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