Hello,

In the project I am working, there is an extensive use of GetMulti methods. And in few cases, we need to send tag/comments for the queries. As far as I understood, starting from v5.3 we can use MarkWithTag method and I saw the example usage in the documents. My question is how can I use it with GetMulti.

Is there another way to send comments/tags for the queries done with GetMulti (preferably for the v5.0, if not, then for v5.3)?

Not in selfservicing, which is what you're using. The query tag ends up in the QueryParameters object which is passed to the DaoBase.GetMulti() method accepting a QueryParameters object, but there's no way to adjust that at that point or after it (no virtual methods).

Thank you for your answer.

So if we want to change from selfservicing to adapters, then all the DAL will be regenerated differently and so all the other parts of the code which speak to DAL should be rewritten.

So, in a large project with few developers and with tight time limitations, this doesn't look like a feasible solution to me.

Do you have a guide which describes such a change?

I'm sure you don't want to have tagging on all queries, correct? You could rewrite the queries you do want to have tagging on to either linq or queryspec queries (for selfservicing), and go from there? Migrating to adapter isn't a feasible option for you I think, as it is indeed a lot of work in large projects.

Thank you for your answer.

According to business people, apparently even a dummy filter will do fine. So I tried the following but it seems like this is not really possible. Or I am missing something?

IPredicateExpression selectFilter = new PredicateExpression(); selectFilter.Add("MY_TAG" == "MY_TAG");

Thanks in advance.

that's not leading to anything useful, but using that there's another route I think

using System;
using System.Text;
using System.Collections.Generic;
using System.Xml;

namespace SD.LLBLGen.Pro.ORMSupportClasses
{
    [Serializable]
    public class TagPredicate : Predicate
    {
        #region Class Member Declarations
        private string _tag;
        #endregion

        public TagPredicate(string tag)
        {
            _tag = tag;
        }


        public override string ToQueryText()
        {
            return ToQueryText(false);
        }

        public override string ToQueryText(bool inHavingClause)
        {
            if(string.IsNullOrEmpty(_tag))
            {
                string.Empty;
            }
            var queryText = StringBuilderCache.Acquire(_tag.Length + 10);
            queryText.Append("1=1;/*");
            queryText.Append(_tag);
            queryText.Append("*/");
            return StringBuilderCache.GetStringAndRelease(queryText);
        }
    }
}

This is a custom predicate, TagPredicate. To use, do:

selectFilter.Add(new TagPredicate("MY_TAG"));

This will then append a 1=1 predicate followed by /MY_TAG/

(not tested live, but should do the trick)

In case you're on an older runtime, there's no StringBuilderCache, so in that case you have to create a new StringBuilder instance and append the strings there or do a string.Format().

Thank you. This works very good.

Maybe one last point which interests us can be, if we can put a tag next to a column name, instead of a filter. Our DBA thinks that this would be better if it is possible.

As far as I see, we dont have access to column list before calling GetMulti. I tried to do the following but this gives an error.

objectCollection[0].AddinfoComment = "TAG_TO_ADD"; //Error .... objectCollection.GetMulti(selectFilter, 0L, sort, relations);

Thanks.

dogacozen wrote:

Thank you. This works very good

Nice it worked and helped in your situation.

dogacozen wrote:

Maybe one last point which interests us can be, if we can put a tag next to a column name, instead of a filter. Our DBA thinks that this would be better if it is possible.

How it would help your original scenario/need? and How it would be? Do you have an approximate SQL the you would like to generate?

Hi, thanks for your message. Our DBA thinks that it would be better if we can have a comment like this: SELECT COLUMN_A /comment_a/, COLUMN_B AS B /comment_b/

There's at the moment no support for that. In a custom projection you could add the comment to the alias of a field, but you then need to use queryspec to do so, and that mitigates the point, as you then can add the querytag without problems.

Not sure why it is wanted to have comments besides each column? Could you elaborate on the use case for this please?

You are right this is not a technical requirement actually and what we would like to have is just a single comment near the beginning of the query. This is because our queries are really long. When the DBA wants to see our queries in the query view of oracle, he cannot immediately see after 4000 characters. He has to see the LOB window etc. So that's why we wanted to have the tags near the beginning of the query.

Query tagging won't help then (in case you want to rewrite queries to linq) as these are appended, not inserted up front.

Hmm. The method 'DaoBase.ExecuteMultiRowRetrievalQuery' is the method that's executed when you e.g. do a GetMulti. It's virtual, so you could override it in a partial class of CommonDaoBase, and in that overload insert a string at the front of the query.Command.CommandText. (using /* tag */)

The problem is: how to get the tag you want to insert there reliable into the Dao instance that's going to execute the query. As doing:

var collection = new CustomerCollection(); collection.GetMulti(...);

will create a new Dao instance (CustomerDao) on the fly, there's no way to pass the string to that. You could try a [ThreadStatic] marked static var perhaps:

[ThreadStatic] public static string Tag = string.Empty;

then set that before the GetMulti call, in the override of ExecuteMultiRowRetrievalQuery, check if it's non-empty, if it's non-empty, insert it as /* tag */, and reset the Tag variable to string.Empty again.

It's somewhat reliable, but in asp.net (core) apps under heavy load your request might get stalled and passed on to another thread right at that moment. Unlikely but not impossible. But as it's not a mission critical feature, it's likely one that would work out fine.