Is there any way in code to access, and then alter the SQL that LLBLGen Pro generates before its executed?

Thanks!

Yes you could. If you want to do it on a general level, you could intercept the calls in the DataAccessAdapter, like this:

    public class CommandInterceptorDataAccessAdapter : DataAccessAdapter
    {
        /// <summary>CTor</summary>
        public CommandInterceptorDataAccessAdapter()
        {
        }


        /// <summary>CTor</summary>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        public CommandInterceptorDataAccessAdapter(bool keepConnectionOpen) : base(keepConnectionOpen)
        {
        }


        /// <summary>CTor</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public CommandInterceptorDataAccessAdapter(string connectionString) : base(connectionString)
        {
        }


        /// <summary>CTor</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen) : base(connectionString, keepConnectionOpen)
        {
        }


        /// <summary>CTor.</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="catalogNameUsageSetting"> Configures this data access adapter object how to threat catalog names in persistence information.</param>
        /// <param name="catalogNameToUse"> The name to use if catalogNameUsageSetting is set to ForceName. Ignored otherwise.</param>
        /// <remarks>For backwards compatibility.</remarks>
        public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse) : base(connectionString, keepConnectionOpen, catalogNameUsageSetting, catalogNameToUse)
        {
        }


        /// <summary>CTor</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="schemaNameUsageSetting">Configures this data access adapter object how to threat schema names in persistence information.</param>
        /// <param name="schemaNameToUse">Oracle specific. The name to use if schemaNameUsageSetting is set to ForceName. Ignored otherwise.</param>
        public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen, SchemaNameUsage schemaNameUsageSetting, string schemaNameToUse) : base(connectionString, keepConnectionOpen, schemaNameUsageSetting, schemaNameToUse)
        {
        }


        /// <summary>CTor.</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="catalogNameOverwrites"> The from-to name value pairs and setting for the overwriting of catalog names. Can be null.</param>
        /// <param name="schemaNameOverwrites"> The from-to name value pairs and setting for the overwriting of schema names. Can be null.</param>
        public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameOverwriteHashtable catalogNameOverwrites, SchemaNameOverwriteHashtable schemaNameOverwrites) : base(connectionString, keepConnectionOpen, catalogNameOverwrites, schemaNameOverwrites)
        {
        }


        protected override IRetrievalQuery CreateSelectDQ(QueryParameters parameters)
        {
            if(this.GeneratedCommands == null)
            {
                this.GeneratedCommands = new List<DbCommand>();
            }
            var toReturn = base.CreateSelectDQ(parameters);
            this.GeneratedCommands.Add(toReturn.Command);
            return toReturn;
        }


        public List<DbCommand> GeneratedCommands { get; private set; }

        /// <summary>
        /// Gets the last generated command.
        /// </summary>
        public DbCommand LastGeneratedCommand
        {
            get
            {
                if(this.GeneratedCommands == null)
                {
                    return null;
                }
                return this.GeneratedCommands.Last();
            }
        }
    }

Modify other individual SQL parts individually is also possible but not that trivial. What is what you want to alter/modify exactly? (maybe there is another way).

(For clarity, change the SQL in the CreateSelectDQ override, you don't need to collect the DbCommand, the class above is an example, we use it for tests)

Awesome! Thanks for your responses!

daelmo wrote:

Modify other individual SQL parts individually is also possible but not that trivial. What is what you want to alter/modify exactly? (maybe there is another way).

So I was hoping to get access to the sp exec SQL statement that gets sent to the server, but right now I'm really only seeing the base query, is there any way to get access to that? or am I just missing it?

Thanks!

Could you please explain the use case why do you want that?

Walaa wrote:

Could you please explain the use case why do you want that?

I can, but I know you all probably won't love it

We want to take integer parameters and move them out of the parameter list and directly into the query in some form. We have a lot of cases where we occasionally bump into the 2100 parameter limit, and it's almost always due to filtering on primary key values, which are always integers in our application. This doesn't happen often enough to cause performance worries, but it does happen often enough that we need to handle it in a lot of places in our code.

We're not really worried about SQL injection from integer values (we wouldn't do this for strings for example), and it would be something we'd probably turn on/off based on the number of parameters in the query (which is easy enough to figure out with a compiled regex). Additionally, we could set our own limits that were higher than 2100 parameters but still existed.

I did notice in the above example that there is an underlying SQL command with parameters, but I didn't know if altering that would affect the final exec statement that you generate, so I figured I'd ask if there was a way to get at that first.

The IRetrievalQuery object contains a DbCommand with the SQL query that's executed. If you want to remove integer parameters and replace their names in the query in the DbCommand with a literal, you can do so by altering the IRetrievalQuery's DbCommand's CommandText and parameters collection.

I'd still look for a way to avoid the 2100 parameter limit in another way i.e., look into why you need 2100 parameters to begin with, but I think we've been over that before . The good news is that Microsoft is trying to lift this limitation from the SqlClient in Microsoft.Data.SqlClient, but when it'll be released I don't know.

Otis wrote:

The IRetrievalQuery object contains a DbCommand with the SQL query that's executed. If you want to remove integer parameters and replace their names in the query in the DbCommand with a literal, you can do so by altering the IRetrievalQuery's DbCommand's CommandText and parameters collection.

Awesome thanks!

Otis wrote:

I'd still look for a way to avoid the 2100 parameter limit in another way i.e., look into why you need 2100 parameters to begin with, but I think we've been over that before .

yeah a lot of it ends up being due to filtering subsets of large data sets, where the subset itself is large. In that case, its not feasible to load everything, and there's not really a grouping construct that ties to way to address the subset

Otis wrote:

The good news is that Microsoft is trying to lift this limitation from the SqlClient in Microsoft.Data.SqlClient, but when it'll be released I don't know.

Oh man, thanks for telling me, I hadn't heard I have to read more about that!