HomeActive Query Builder - Query analysis and modificationAnalyzing SQL query structureEnumeration of output columns in a sub-query

1.5. Enumeration of output columns in a sub-query

The UnionSubQuery.CriteriaList collection contains information about SQL query expressions, their properties and criteria. Each element of this collection has all the necessary properties to read and modify text of expression, it's alias, ordering, grouping, aggregate function and criteria for this expression. The CriteriaItem.Select property determines if expression is listed in the SELECT list of output expressions.

The ExpressionField and ExpressionDatasource properties refer to the MetadataField and Datasource objects if expression is a single database object field.

Below are the fragments of the QueryStructureDemo project that included in the installation package. You may review it for more details.

public void DumpSelectedExpressionsInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
    // get list of CriteriaItems
    CriteriaList criteriaList = unionSubQuery.CriteriaList;

    // dump all items
    for (int i = 0; i < criteriaList.Count; i++)
    {
        CriteriaItem criteriaItem = criteriaList[i];

        // only items with the Select property set to True go to SELECT list
        if (!criteriaItem.Select)
        {
            continue;
        }

        DumpSelectedExpressionInfo(stringBuilder, criteriaItem);
    }
}
        
private void DumpSelectedExpressionInfo(StringBuilder stringBuilder, CriteriaItem selectedExpression)
{
	// write full sql fragment of selected expression
	stringBuilder.AppendLine(selectedExpression.ExpressionString);

	// write alias
	if (!String.IsNullOrEmpty(selectedExpression.AliasString))
	{
		stringBuilder.AppendLine("  alias: " + selectedExpression.AliasString);
	}

	// write datasource reference (if any)
	if (selectedExpression.ExpressionDatasource != null)
	{
		stringBuilder.AppendLine("  datasource: " + selectedExpression.ExpressionDatasource.GetResultSQL());
	}

	// write metadata information (if any)
	if (selectedExpression.ExpressionField != null)
	{
		MetadataField field = selectedExpression.ExpressionField;
		stringBuilder.AppendLine("  field name: " + field.NameStr);

		string s = Enum.GetName(typeof(DbType), field.FieldType);
		stringBuilder.AppendLine("  field type: " + s);
	}
}

This page was: Helpful | Not Helpful