Active Query Builder support area

"*" select in the table should replace *in the select query with column names.

Avatar
Rujuta
  • updated
  • Completed

Hi,

I have some selected fields in the XML to generate metadata. While designing the query, if I use * selector for the query, it fetches all column from the database. 

Instead, on selecting *, I want to put the column name list in the query. So the user does not get all the columns from the database. Only the columns that are in the design query pane, gets fetched

I don't want to run a loop. Is there a function / option that will replace * with output columns.

I tried QueryTransformer .AlwaysExpandColumnsInQuery = True. Even after selecting all the columns, if I click * it adds all the columns again. Let me know how to avoid this

Is there a way to disable / hide the * option in the table. 

Looking forward to your reply.

 

 

Replies 2
Avatar
0
Andrey Zavyalov, PM

This is possible using the QueryBuilder.DataSourceFieldAdded event. I will provide the sample tomorrow.

Meanwhile, you can review the QueryUIEventsDemo project included in the installation project and available on the GitHub.

Avatar
0
Andrey Zavyalov, PM

Hello, Rujuta.

As usual, there are several ways to cope with this problem.

The first is just to hide the asterisk item from all datasources as you suggested. This can be done by setting the QueryBuilder.DataSourceOptions.HideAsteriskItem property to True.

Another approach is to handle the QueryBuilder.DataSourceFieldAdded event and correct the query. The sample event handler code is below.

private void queryBuilder1_DataSourceFieldAdded(DataSource dataSource, MetadataField field, QueryColumnListItem queryColumnListItem, bool focusCondition)
{
    var unionSubQuery = dataSource.ParentUnionSubQuery;

    using (new UpdateRegion(unionSubQuery))
    {
        if (field == null) // sign of the asterisk item
        {
            var queryColumnList = unionSubQuery.QueryColumnList;
            var index = queryColumnList.IndexOf(queryColumnListItem);

            foreach (var metadataField in dataSource.Metadata.Fields)
            {
                // if field already added - skip it
                if (queryColumnList.FindField(dataSource, metadataField.Name) != null)
                    continue;

                // field not in select list - add it
                index++;
                var newExpression = queryColumnList.InsertField(index, dataSource, metadataField.Name);
                newExpression.Selected = true;
            }

            // remove "*" item
            queryColumnListItem.Dispose();
        }
    }
}

Alternatively, we may consider changing the behaviour of the asterisk item, so that it acts as an aggregated checker/unchecker for the rest of the datasource fields, but this is not possible in the current version.