3.1 - DataAdapter

I have a generic search form that builds up a RelationPredicateBucket. Then I FetchEntityCollection to display results.

How can I get the sql statement that was generated and executed by LLBL?

I'm after the WHERE clause so that I can send that to the reporting engine.

happyfirst wrote:

How can I get the sql statement that was generated and executed by LLBL?

You can create a DataAccessAdapter partial class and override the CreateSelectDQ method. In that place you have access to que query, and you can extract the WHERE part and set it to a custom string property (MyWhereClause), or you can create a event and raise it. At your own code you can consult your MyWhereClause property or subscribe to the event you created. This is a Miha's post about how to trap the generated sql and transform it to be more verbose. You can take a look to undestand how to get into the CreateSelectDQ method. Example:

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, SD.LLBLGen.Pro.ORMSupportClasses.ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
    IRetrievalQuery query = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
    string whereClause = ExtractMyWhereClause(query);

    this.MyWhereClause = whereClause;
    return query;
}

happyfirst wrote:

I'm after the WHERE clause so that I can send that to the reporting engine.

Note that your approach is not ideal in cases where you are filtering on related entities. If you are fetching Orders but filtering on the Customer.City, extracting the where clause doesn't tell you about what additional entities you have to fetch. I faced this before, my approach was to ask for the data, then pass it to my report. Example:

DataTable data = GetOrdersAsDataTable(...);
BindToMyReport(data);

That depends on how your report engine looks like. I hope this information would help you

Thanks! I will give this a shot.