Dear LLBLGen team,

I know we are not suppose to but I have a solid need to run a raw SQL query.

Is DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery designed to run a raw SQL and get the result?

For example, can I run a "Select id,name from person where age<16" then populate an EntityCollection of some arbitrary TeenagerEntity?

If possible, some example code could be helpful since ExecuteMultiRowRetrievalQuery takes quite a lot of parameters.

Sincerely,

What you are looking for is ActionQuery. But please post a real query that you need to run in your scenario. Maybe there is no need to do that in your case.

daelmo wrote:

What you are looking for is ActionQuery. But please post a real query that you need to run in your scenario. Maybe there is no need to do that in your case.

This query Select id from person where age<16 is almost exactly the same as what I would run. To be specific, I would append different where conditions to construct raw sql then get all ids of records that meets the condition.

Could I achieve this?

Thanks for you answering in both of my post.

Yes,you can, but you don't need to write raw sql for that. Example (using DynamicList)

// Low-level API, C#
var fields = new ResultsetFields(3);
fields.DefineField(PersonFields.Id, 0, "Id");

var bucket = new RelationPredicateBucket(PersonFields.Age < 16);

var dynamicList = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, null);
}

More info about this at the documentation.

There are other options: QuerySpec, Linq2LLBL, Entity collection fetch. No need to raw sql.

daelmo wrote:

Yes,you can, but you don't need to write raw sql for that. Example (using DynamicList)

// Low-level API, C#
var fields = new ResultsetFields(3);
fields.DefineField(PersonFields.Id, 0, "Id");

var bucket = new RelationPredicateBucket(PersonFields.Age < 16);

var dynamicList = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, null);
}

More info about this at the documentation.

There are other options: QuerySpec, Linq2LLBL, Entity collection fetch. No need to raw sql.

Thanks for your help. I could see many usage of this method.

However, the where conditions are stored and retrieved as string from database. (the "age < 18" is stored as string). In this case, do I have many options?

Sincerely,

I strongly recommend against storing a raw text for the where clauses. This could lead to tons of problems. SQL injection is one of them, also what will happen if the database was modified and a field got renamed?!

You can serialize the PredicateExpression and store it, then deserialize it back when needed. Or you can store it in a dataStructure, as [EntityField - Operator - Value].

Walaa wrote:

I strongly recommend against storing a raw text for the where clauses. This could lead to tons of problems. SQL injection is one of them, also what will happen if the database was modified and a field got renamed?!

You can serialize the PredicateExpression and store it, then deserialize it back when needed. Or you can store it in a dataStructure, as [EntityField - Operator - Value].

Thank you for clarification on why it is bad to run string raw queries. But I am working on a legacy application, and writing raw sql is how the the application work.

Executing raw sql is done through creating a RetrievalQuery object or ActionQuery object. Problem is though that you need to provide a connection object to do so, and that's not going to work out of the box.

YOu have to add custom code to a partial class of DataAccessAdapter. THere you first call CreateDynamicQueryEngine and use the returned object to obtain a hold of the DbProviderFactory though the Creator property.

WIth the DbProviderFactory (or with the creator object of the DynamicQueryEngine) you can create DbCommands, which is needed to create the RetrievalQuery.

After you've created the RetrievalQuery you can execute it to do something using the FetchProjection method as that's the easiest. That way you can project the result to whatever you want.

If you only obtain the where clauses from the db (though why not solve that problem there?) and the projections etc. are not read from the DB, you can also create a new Predicate derived class.

In your Predicate derived class you simply store the string read from the DB (e.g. through its ctor) and in its ToQueryText() override (see the sourcecode of the runtime libraries for details, it's in the extras section) you simply return the string you passed to it in the ctor. The engine will then append the string to the query generated as the WHERE clause.

Thanks you guys for all your help.

I will go work on the method @Otis provided.