HomeActive Query Builder - Query analysis and modificationPrinter Friendly Version

Active Query Builder - Query analysis and modification

1. Analyzing SQL query structure

1.1. Retrieving common information about the query

Database objects usage and other information about the currently loaded SQL query is instantly available through the QueryBuilder.QueryStatistics set of collections.

  1. Used database objects: QueryBuilder.QueryStatistics.UsedDatabaseObjects collection.

    Each item of this collection has link to the MetadataObject object to retrieve detailed information about this database object. Please note that this link might be set to null if such object can not be found in the Metadata Container.

  2. Used database object fields: QueryBuilder.QueryStatistics.UsedDatabaseObjectColumns collection.

    Each item of this collection has name, type of the field. It also has links to the MetadataField and MetadataObject objects to retrieve detailed information about this field and the database object to which it belongs to. The Selected property indicates if this field is used in some of the output columns in the query.

    Please note that this collection does not contain fields that were used in the query without table alias prefix. This because it is impossible to uniquely determine correspondence of this field to a table.

  3. Output query columns: QueryBuilder.QueryStatistics.OutputColumns collection.

    This collection lists output columns of the query. Each item of this collection has the Expression and ExpressionAlias properties to retrieve SQL expression and it's alias (if defined). If SQL expression is a simple database object field, additional information for it is available: field type, object name, links to MetadataField and MetadataObject objects, etc.

1.2. Understanding complex SQL query structure representation

Each query and sub-query in the query is represented with the SubQuery object in Active Query Builder; The main query representation class Query is descendant of the SubQuery class. The main query can be accessed via the QueryBuilder.Query property. To get access to the list of sub-queries, use the QueryBuilder.SubQueries collection.

Each sub-query may not be a single "SELECT ... FROM ..." statement, but a set of statements joined with the UNION, EXCEPT, INTERSECT and other keywords. Each single "SELECT ... FROM ..." statement is represented via the UnionSubQuery object.

There are few useful helpers you may use in your project:

  • The QueryBuilder.Query.FirstSelect() method returns the link to an object representing the first single SELECT statement in the query.

  • The QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery property gets the single SELECT statement currently visible to the end-user. The QueryBuilder.ActiveUnionSubQueryChanged event is fired when user switches to another statement.

The UnionSubQuery object holds datasources, their links (UnionSubQuery.FromClause), expressions and criteria (UnionSubQuery.QueryColumnList) of the single SELECT statement. To get list of all datasources, use the UnionSubQuery.FromClause.GetDatasources() method. The CriteriaList collection is organized the same way as you see it in the Query Columns Grid: each item of this collection (CriteriaItem) represents a single row of this grid; columns are accessible via the corresponding properties.

1.3. Enumeration of sub-queries in a complex query

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

1. Enumeration of sub-queries in a query.

Use the QueryBuilder.SubQueries and the QueryBuilder.SubQueryCount properties to list all sub-queries in the query.

public void DumpSubQueriesInfo(StringBuilder stringBuilder, QueryBuilder queryBuilder)
{
	for (int i = 0; i < queryBuilder.SubQueryCount; i++)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		DumpSubQueryInfo(stringBuilder, i, queryBuilder.SubQueries[i]);
	}
}

private void DumpSubQueryInfo(StringBuilder stringBuilder, int index, SubQuery subQuery)
{
    string sql = subQuery.GetResultSQL();

    stringBuilder.AppendLine(index.ToString() + ": " + sql);
}

2. Enmeration of union queries in a sub-query.

Union sub-query groups might be enlosed in parentheses, thus it's not a simple union sub-query list, but a list of single union sub-queries and union sub-query groups organized in a tree-like structure.

public void DumpQueryStructureInfo(StringBuilder stringBuilder, SubQuery subQuery)
{
    DumpUnionGroupInfo(stringBuilder, "", subQuery);
}
        
private void DumpUnionGroupInfo(StringBuilder stringBuilder, string indent, UnionGroup unionGroup)
{
	QueryBase[] children = GetUnionChildren(unionGroup);

	foreach (QueryBase child in children)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		if (child is UnionSubQuery)
		{
			// UnionSubQuery is a leaf node of query structure.
			// It represent a single SELECT statement in the tree of unions
			DumpUnionSubQueryInfo(stringBuilder, indent, (UnionSubQuery) child);
		}
		else
		{
			// UnionGroup is a tree node containing one or more child nodes.
			// It represents a root of the subquery in the union tree or 
			// a group of sub-queries enclosed in parentheses in the union tree.
			unionGroup = (UnionGroup) child;

			stringBuilder.AppendLine(indent + unionGroup.UnionOperatorFull + "group: [");
			DumpUnionGroupInfo(stringBuilder, indent + "    ", unionGroup);
			stringBuilder.AppendLine(indent + "]");
		}
	}
}

private void DumpUnionSubQueryInfo(StringBuilder stringBuilder, string indent, UnionSubQuery unionSubQuery)
{
	string sql = unionSubQuery.GetResultSQL();

	stringBuilder.AppendLine(indent + unionSubQuery.UnionOperatorFull + ": " + sql);
}

private QueryBase[] GetUnionChildren(UnionGroup unionGroup)
{
	ArrayList result = new ArrayList();

	for (int i = 0; i < unionGroup.Count; i++)
	{
		result.Add(unionGroup[i]);
	}

	return (QueryBase[]) result.ToArray(typeof(QueryBase));
}

1.4. Enumeration of data sources and joins in a sub-query

The UnionSubQuery.FromClause object holds the list of SELECT statement datasources and their links.

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

1. Enumeration of all datasources in a single SELECT statement.

You may get plain list of datasources using the GetDatasourceByClass() method. There are DataSourceObject and DataSourceQuery classes representing database objects and derived tables correspondingly. The DataSourceQuery has the Query property to get access to SubQuery object representing this derived table. The DataSorceObject has MetadataObject property to get access to the database object details.

public void DumpDataSourcesInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
    DumpDataSourcesInfo(stringBuilder, GetDataSourceList(unionSubQuery));
}

private void DumpDataSourcesInfo(StringBuilder stringBuilder, ArrayList dataSources)
{
    for (int i = 0; i < dataSources.Count; i++)
    {
        if (stringBuilder.Length > 0)
        {
            stringBuilder.AppendLine();
        }

        DumpDataSourceInfo(stringBuilder, (DataSource)dataSources[i]);
    }
}

private void DumpDataSourceInfo(StringBuilder stringBuilder, DataSource dataSource)
{
	// write full sql fragment
	stringBuilder.AppendLine(dataSource.GetResultSQL());

	// write alias
	stringBuilder.AppendLine("  alias: " + dataSource.Alias);

	// write referenced MetadataObject (if any)
	if (dataSource.MetadataObject != null)
	{
		stringBuilder.AppendLine("  ref: " + dataSource.MetadataObject.FullNameStr);
	}

	// write subquery (if datasource is actually a derived table)
	if (dataSource is DataSourceQuery)
	{
		stringBuilder.AppendLine("  subquery sql: " + ((DataSourceQuery) dataSource).Query.GetResultSQL());
	}

	// write fields
	string fields = String.Empty;

	for (int i = 0; i < dataSource.Fields.Count; i++)
	{
		if (fields.Length > 0)
		{
			fields += ", ";
		}

		fields += dataSource.Fields[i].NameStr;
	}

	stringBuilder.AppendLine("  fields (" + dataSource.Fields.Count.ToString() + "): " + fields);
}

private ArrayList GetDataSourceList(UnionSubQuery unionSubQuery)
{
    ArrayList list = new ArrayList();

    unionSubQuery.FromClause.GetDatasourceByClass(typeof(DataSource), list);

    return list;
}

2. Enumeration of all links in a single SELECT statement.

You may get the list of links using the GetLinksRecursive() method. Each Link object has references to it's left and right datasources (LeftDatasource, RightDatasource), linked fields (LeftField, RightField) and has properties to read and change it's join type (LeftType, RightType). Link expression is available trough the LinkExpression property.

public void DumpLinksInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
    DumpLinksInfo(stringBuilder, GetLinkList(unionSubQuery));
}
        
private void DumpLinksInfo(StringBuilder stringBuilder, ArrayList links)
{
	for (int i = 0; i < links.Count; i++)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		DumpLinkInfo(stringBuilder, (Link) links[i]);
	}
}

private void DumpLinkInfo(StringBuilder stringBuilder, Link link)
{
    // write full sql fragment of link expression
    stringBuilder.AppendLine(link.LinkExpression.SimpleSQL(link.SQLContext.SQLBuilderExpression));

    // write information about left side of link
    stringBuilder.AppendLine("  left datasource: " + link.LeftDatasource.GetResultSQL());

    if (link.LeftType == LinkSideType.Inner)
    {
        stringBuilder.AppendLine("  left type: Inner");
    }
    else
    {
        stringBuilder.AppendLine("  left type: Outer");
    }

    // write information about right side of link
    stringBuilder.AppendLine("  right datasource: " + link.RightDatasource.GetResultSQL());

    if (link.RightType == LinkSideType.Inner)
    {
        stringBuilder.AppendLine("  lerightft type: Inner");
    }
    else
    {
        stringBuilder.AppendLine("  right type: Outer");
    }
}

private ArrayList GetLinkList(UnionSubQuery unionSubQuery)
{
    ArrayList links = new ArrayList();

    unionSubQuery.FromClause.GetLinksRecursive(links);

    return links;
}

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);
	}
}

1.6. Determination of presence of the INTO clause in a query

The SyntaxProvider.DenyIntoClause property set to true prescribes Active Query Builder to raise an exception on parsing a query with the INTO clause. But sometimes you may want to determine if such clause is present in a query. Use the following code to determine it's presence.

var haveInto = false;

// MySql
if (queryBuilder1.SyntaxProvider is MySQLSyntaxProvider)
{
	var usqList = queryBuilder1.GetUnionSubQueryList();
	foreach (var unionSubQuery in usqList)
	{
		var extender = (MySQLSubQuerySelectExpressionExtender) unionSubQuery.QueryAST.Extender;
		haveInto = extender.IntoFile != null;
		if (haveInto) break;
	}
}

// Access
else if (queryBuilder1.SyntaxProvider is MSAccessSyntaxProvider)
{
	var usqList = queryBuilder1.GetUnionSubQueryList();
	foreach (var unionSubQuery in usqList)
	{
		var extender = (AccessSubQuerySelectExpressionExtender) unionSubQuery.QueryAST.Extender;
		haveInto = extender.Into != null;
		if (haveInto) break;
	}
}

// MS SQL Server
else if (queryBuilder1.SyntaxProvider is MSSQLSyntaxProvider)
{
	var usqList = queryBuilder1.GetUnionSubQueryList();
	foreach (var unionSubQuery in usqList)
	{
		var extender = (MSSQLSubQuerySelectExpressionExtender)unionSubQuery.QueryAST.Extender;
		haveInto = extender.Into != null;
		if (haveInto) break;
	}
}

// PostgreSql
else if (queryBuilder1.SyntaxProvider is PostgreSQLSyntaxProvider)
{
	var usqList = queryBuilder1.GetUnionSubQueryList();
	foreach (var unionSubQuery in usqList)
	{
		var extender = (PostgreSQLSubQuerySelectExpressionExtender)unionSubQuery.QueryAST.Extender;
		haveInto = extender.IntoClause != null;
		if (haveInto) break;
	}
}

MessageBox.Show(haveInto ? "Have INTO" : "No INTO");

2. Programmatic modification of SQL queries

2.1. How to add object to the query programmatically

There are four methods that can help you with this:

  • UnionSubQuery.AddObject
  • UnionSubQuery.AddObjectAt
  • UnionSubQuery.AddObjectWithFK
  • UnionSubQuery.AddObjectWithFKAt

Methods with the "At" should be used when dragging objects to the Design Pane as they allow to specify position of the added object.

Methods with the "WithFK" suffix instruct the component to add joins to the newly added object from other objects that may exist in the active sub-query (according to the foreign keys or by identical field names (CreateLinksByIdenticalFieldNamesCreateLinksFromForeignKeys).

The "WithFK"-suffixed methods are obsolete, they will not exist in the upcoming version 3.0. I recommend to use separate methods to add links to existing query objects automatically:

  • UnionSubQuery.AddFKLinksForObject
  • UnionSubQuery.AddIdenticalFieldsLinksForObject

The UnionSubQuery.AddLinkedObjectsFor method allows to link an object with other query objects by foreign keys even if it doesn't direclty linked with existing objects. Intermediate objects will be added to the query automatically (if such objects exist) in order to join this object.

Example:

UnionSubQuery usq = queryBuilder1.Query.ActiveUnionSubquery;
DataSource dataSource = usq.AddObject("Orders");
int addedLinksNum = usq.AddFKLinksForObject(dataSource);
if (addedLinksNum == 0) 
{
    usq.AddLinkedObjectsFor(dataSource);
}

2.2. How to change sorting, add filters, limits and aggregations to the query?

There's a special component intended to change SQL queries according to the end-user data browsing needs: QueryTransformer. It was made to meet the needs of programmers who want to let the end-users to transfigure data while browsing query result set in the grid. The component lets change data sorting and filtration while browsing the query result data in the grid, apply limits to the result set, and calcluate totals by columns.

It needs to be linked to the QueryBuilder and SQLBuilder objects to get the initial query columns and produce the modified query text in result.

Follow the simple steps below to change the query the way you need:

  1. Find the column you need in the QueryTransformer.Columns collection:

        column = queryTransformer.Columns.ColumnByName("OrderDate")
  2. Create an object that defines the necessary modification:

        sorting = column.Ascending()
        filter = column.NotLess("31/12/2012")
        aggregate = column.Max("DateMax")
  3. Apply these objects to the query:

        queryTransformer.OrderBy(sorting)
        queryTransformer.Where(filter)
        queryTransformer.Select(aggregate)
  4. Apply limits to the query:

        If queryTransformer.IsSupportLimitCount() Then
            queryTransformer.Take("50")
        End If
  5. Get the modified query from the QueryTransformer.SQL property:

        MessageBox.Show(queryTransformer.SQL)

    OR save the component state for future use with the QueryTransformer.StateXML property:

        savedState = queryTransformer.StateXML
        queryBuilder.SQL = updatedSql
        queryTransformer.StateXML = savedState

Read the QueryTransformer and accompanying classes reference for details.

Below is the object relationship diagram and code samples for different languages.

Delphi, VCL Edition

queryTransform.BeginUpdate;
try

       // filter
       queryTransform.Filters.Clear; // gets back the WHERE clause to initial state 

       queryTransform //adds conditions to the WHERE clause, conjunctioning it with conditions from initial query
	       .Where(queryTransform.Columns[1].Not_Equal('100'))
	       .Where(queryTransform.Columns[2].In_('(''Value1'',''Value2'',''Value3'')'))
	       .Where(queryTransform.Columns.ColumnByName('OrderId').Condition('> 100'))
	       .Where('OrderDate is not null')
	       .Where(TacFilterFactory.Or_()
		       .Add(queryTransform.Columns[1].Not_Equal('100'))
		       .Add(queryTransform.Columns[1].Not_Equal('200')));

       // ordering
       queryTransform.Sortings.Clear; // gets back the ORDER BY clause to initial state 

       queryTransform // removes the ORDER BY clause of initial query and defines alternate ordering for a query
	       .OrderBy(queryTransform.Columns[1])
	       .OrderBy(queryTransform.Columns[2], false)
	       .OrderBy(queryTransform.Columns.ColumnByName('OrderId'))
	       .OrderBy('OrderDate');

       // offset
       queryTransform // applies the offset clauses to the query according to specific database server SQL syntax
	       .Skip('100')
	       .Take('50');

	// aggregations
	queryTransform.Aggregations.Clear;  // removes wrapping of the query in a sub-query

	queryTransform  // wraps the query in a sub-query and defines output columns for the outer query
	.SelectRecordsCount('recordsCount')
	.Select(queryTransform.Columns[1].Count, 'column1Count')
	.Select(queryTransform.Columns[1].Sum, 'column1Sum')
	.Select(queryTransform.Columns[1].Avg, 'column1Avg')
	.Select(queryTransform.Columns[1].Min, 'column1Min')
	.Select(queryTransform.Columns[1].Max, 'column1Max')
	;

finally
       queryTransform.EndUpdate;
end;

C#, .NET Edition

using (QueryTransformer queryTransformer = new QueryTransformer())
{
    queryTransformer.QueryBuilder = queryBuilder1;
    queryTransformer.SqlBuilder = plainTextSQLBuilder1;
 
    queryTransformer.BeginUpdate();
 
    try
    {
        // filter
        queryTransformer.Filters.Clear();
 
        queryTransformer
            .Where(queryTransformer.Columns[1].Not_Equal("100"))
            .Where(queryTransformer.Columns[2].In("('Value 1','Value 2','Value 3')"))
            .Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100"))
            .Where("OrderDate is not null")
            .Where(FilterFactory.Or()
                .Add(queryTransformer.Columns[1].Not_Equal("100"))
                .Add(queryTransformer.Columns[1].Not_Equal("200")));
 
        // ordering
        queryTransformer.Sortings.Clear();
 
        queryTransformer
            .OrderBy(queryTransformer.Columns[1])
            .OrderBy(queryTransformer.Columns[2], false)
            .OrderBy(queryTransformer.Columns.GetColumnByName("OrderId"))
            .OrderBy("OrderDate");
 
        // limit, offset
        queryTransformer
            .Skip("100")
            .Take("50");
 
        // aggregations
        queryTransformer.Aggregations.Clear();
 
        queryTransformer
            .SelectRecordsCount("recordsCount")
            .Select(queryTransformer.Columns[1].Count(), "column1Count")
            .Select(queryTransformer.Columns[1].Sum(), "column1Sum")
            .Select(queryTransformer.Columns[1].Avg(), "column1Avg")
            .Select(queryTransformer.Columns[1].Min(), "column1Min")
            .Select(queryTransformer.Columns[1].Max(), "column1Max");
    }
    finally
    {
        queryTransformer.EndUpdate();
    }
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql);
}

VB.NET, .NET Edition

Using queryTransformer As New QueryTransformer()
    queryTransformer.QueryBuilder = queryBuilder1
    queryTransformer.SqlBuilder = plainTextSQLBuilder1
 
    queryTransformer.BeginUpdate()
 
    Try
        ' filter
        queryTransformer.Filters.Clear()
 
        queryTransformer.Where(queryTransformer.Columns(1).Not_Equal("100")). _
         Where(queryTransformer.Columns(2).[In]("('Value 1','Value 2','Value 3')")). _
         Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100")). _
         Where("OrderDate is not null"). _
         Where(FilterFactory.[Or](). _
          Add(queryTransformer.Columns(1).Not_Equal("100")). _
           Add(queryTransformer.Columns(1).Not_Equal("200")))
 
        ' ordering
        queryTransformer.Sortings.Clear()
 
        queryTransformer. _
         OrderBy(queryTransformer.Columns(1)). _
         OrderBy(queryTransformer.Columns(2), False). _
         OrderBy(queryTransformer.Columns.GetColumnByName("OrderId")). _
         OrderBy("OrderDate")
 
        ' offset
        queryTransformer. _
         Skip("100"). _
         Take("50")
 
        ' aggregations
        queryTransformer.Aggregations.Clear()
 
        queryTransformer. _
         SelectRecordsCount("recordsCount"). _
         Select(queryTransformer.Columns(1).Count(), "column1Count"). _
         Select(queryTransformer.Columns(1).Sum(), "column1Sum"). _
         Select(queryTransformer.Columns(1).Avg(), "column1Avg"). _
         Select(queryTransformer.Columns(1).Min(), "column1Min"). _
         Select(queryTransformer.Columns(1).Max(), "column1Max")
 
    Finally
        queryTransformer.EndUpdate()
    End Try
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql)
End Using

Visual Basic, ActiveX Edition

Private Sub miTransform_Click()
Dim t As ActiveQueryBuilderXControls.aqbxQueryTransformer
Set t = ActiveQueryBuilderX1.QueryTransformer

t.BeginUpdate
On Error GoTo EndUpdate

' prepare
Dim column0 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column0 = t.Columns.Items(0)
Dim column1 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column1 = t.Columns.ColumnByName("OrderDate")


' Filtering
t.Filters.Clear

t.Where column0.Not_Equal(100)
t.Where column1.In("('Value 1','Value 2','Value 3')")
t.WhereExpr "CustomerName Like 'John%'"


' Sorting
t.Sortings.Clear

t.OrderBy column0.Ascending
t.OrderByColumn column1, False
t.OrderByExpr "CustomerName"


' limits
t.Take 50
t.Skip 100


' aggregations
t.Aggregations.Clear

t.SelectRecordsCount "recordsCount"
t.Select column0.Count, "column0Count"

EndUpdate:
t.EndUpdate

MsgBox t.SQL
End Sub

C#, ActiveX Edition

2.3. How to take additional actions after user has been added an object to the Design Pane?

Active Query Builder allows to customize objects added to the Design Pane via the QueryBuilder.DatasourceAdded event.

The followng code checks all fields of the newly added datasource. The next sample illustrates adding of alias for a newly added datasource

private void queryBuilder_DatasourceAdded(UnionSubQuery query, DataSource addedObject)
{
    // Select all fields

    CriteriaList cl = query.CriteriaList;
    CriteriaItem ci;
     
    cl.BeginUpdate();
     
    try
    {
        for (int i = 0; i < addedObject.Fields.Count; i++)
        {
            ci = cl.FindField(addedObject, addedObject.Fields[i].Name);
             
            if (ci == null)
            {
                ci = cl.AddField(addedObject, addedObject.Fields[i].Name);
            }
             
            ci.Select = true;
        }
    }
    finally
    {
        cl.EndUpdate();
    }
}

 

private void queryBuilder_DatasourceAdded(UnionSubQuery query, DataSource addedObject)
{
    if (addedObject.MetadataObject != null)
    {
        using (AstTokenIdentifier alias = query.CreateUniqueAlias(addedObject.MetadataObject.Name))
        {
            addedObject.Alias = alias.Token;
        }
    }
}

2.4. How to add a join programmatically

One way to add join is to use the QueryBuilder.DatasourceAdded event. Another is to perform a post-processing query modification.

Acting the first way the user will see joins immeditately after adding an object to the query. The sample of the QueryBuilder.DatasourceAdded event is below.

private void queryBuilder1_DatasourceAdded(DataSource dataSource)
{
	ArrayList dataSources = new ArrayList();
	dataSource.Group.FromClause.FromClause.GetDatasources(dataSources);

	// enumerate data source links
	foreach (MetadataField currentTableField in dataSource.Metadata.Fields)
	{
		// if field is primary key then search for same-named fields in other datasources and create links.
		if (currentTableField.PrimaryKey)
		{
			string keyFieldName = currentTableField.Name;

			foreach (DataSource otherDataSource in dataSources)
			{
				// avoid self-linking
				if (otherDataSource.MetadataObject != null &&
					otherDataSource != dataSource &&
					otherDataSource.MetadataObject != dataSource.MetadataObject)
				{
					foreach (MetadataField otherTableField in otherDataSource.Metadata.Fields)
					{
						// if names are equal...
						if (otherTableField.Name == keyFieldName && otherTableField.FieldType == currentTableField.FieldType)
						{
							// check link does not already exist
							if (dataSource.Query.FindLink(dataSource, currentTableField.Name, otherDataSource, otherTableField.Name) == null)
							{
								// add link
								dataSource.Query.AddLink(dataSource, currentTableField.Name, otherDataSource, otherTableField.Name);
							}
						}
					}
				}
			}
		}
		else // field is not primary key. 
			 // search other datasources for same named primary key fields and link them.
		{
			string childFieldName = currentTableField.Name;

			foreach (DataSource otherDataSource in dataSources)
			{
				// avoid self-linking
				if (otherDataSource.MetadataObject != null &&
					otherDataSource != dataSource &&
					otherDataSource.MetadataObject != dataSource.MetadataObject)
				{
					foreach (MetadataField otherTableField in otherDataSource.Metadata.Fields)
					{
						// if names are equal...
						if (otherTableField.Name == childFieldName && otherTableField.FieldType == currentTableField.FieldType)
						{
							// ... and it's primary key
							if (otherTableField.PrimaryKey)
							{
								// check link does not already exist
								if (otherDataSource.Query.FindLink(otherDataSource, otherTableField.Name, dataSource, currentTableField.Name) == null)
								{
									// add link
									otherDataSource.Query.AddLink(otherDataSource, otherTableField.Name, dataSource, currentTableField.Name);
								}
							}
						}
					}
				}
			}
		}
	}
}

Suppose that we need a query always having the Orders and Customers tables and a join between these tables. The following example demonstrates programmatic query modification of a query to suit our requirement.

private void button1_Click(object sender, EventArgs e)
{
    string q;
    UnionSubQuery usq;
    DatasourceGroup fc;
    ArrayList ordersTables, customersTables;
    int iOrders, iCustomers;
    SQLQualifiedName OrdersName, CustomersName;
    SQLQualifiedName joinFieldName;
    DataSource o, c;

    // initial query
    q = "select * from (Orders o inner join [Order details] od on o.OrderId=od.OrderId) 
         inner join Products p On p.ProductID=od.ProductID";

    // create parsed names of tables we need to work with
    OrdersName = queryBuilder1.SyntaxProvider.ParseQualifiedName("Orders");
    CustomersName = queryBuilder1.SyntaxProvider.ParseQualifiedName("Customers");
    joinFieldName = queryBuilder1.SyntaxProvider.ParseQualifiedName("CustomerID");

    // synchronously load the query in the Query Builder
    queryBuilder1.SyncSQL = q;

    // get reference to the UnionSubQuery
    usq = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery;

    // get reference to the FromClause
    fc = usq.FromClause;

    ordersTables = new ArrayList();

    // try to find Orders table
    fc.FindTablesByDBName(OrdersName, ordersTables);

    if (ordersTables.Count == 0)
    {
        // Orders table not found - add it
        ordersTables.Add(usq.AddObject(OrdersName, null));
    }

    customersTables = new ArrayList();

    // try to find Customers table
    fc.FindTablesByDBName(CustomersName, customersTables);

    if (customersTables.Count == 0)
    {
        // Customers table not found - add it
        customersTables.Add(usq.AddObject(CustomersName, null));
    }

    // check existence of link between each Orders and each Customers
    for (iOrders = 0; iOrders < ordersTables.Count; iOrders++)
    {
        o = (DataSource) ordersTables[iOrders];

        for (iCustomers = 0; iCustomers < customersTables.Count; iCustomers++)
        {
            c = (DataSource) customersTables[iCustomers];

            if (fc.FindLink(o, joinFieldName, c, joinFieldName) == null &&
                fc.FindLink(c, joinFieldName, o, joinFieldName) == null)
            {
                usq.AddLink(o, joinFieldName, c, joinFieldName);
            }
        }
    }
}

2.5. How to define aliases programmatically

The following example adds aliases for the fields of the main query in case an alternate name is defined for a field and alias is empty.

UnionSubQuery unionSubQuery = queryBuilder1.Query.FirstSelect();

unionSubQuery.BeginUpdate();

try
{
    for (int i = 0; i < unionSubQuery.CriteriaList.Count; i++)
    {
        CriteriaItem ci = unionSubQuery.CriteriaList[i];

        if (ci.ExpressionField != null) // the criteria item is bound to a metadata field
        {
            if (ci.AliasString.Length == 0 && ci.ExpressionField.AltName.Length > 0)
            {
                ci.AliasString = ci.ExpressionField.AltName;
            }
        }
    }
}
finally
{
    unionSubQuery.EndUpdate();
}

2.6. How to modify WHERE clause programmatically

There are two or more ways to cope with this task depending on the type of modification.

1. You may want to add specific criteria if the query contains specific tables and if such criteria it's not already added to the query, i.e. to analyze the existing query first.

In general, you should find the necessary datasource in the list of datasources in the query; if found, find or add the necessary field name to the list of expressions, then add or modify the condition for this expression. Read the Understanding complex SQL query structure representationEnumeration of data sources and joins in a sub-query and Enumeration of output columns in a sub-query articles to learn the basics. Use the code sample from the QueryModificationDemo project to cope with this task.

2. You may want to append or replace the criteria string to the WHERE clause regardless of it's current content.

It is not possible just to join the new and old criteria strings with AND operator and put the result string back to the query, but it's possible to operate with internal query representation objects to do almost the same. Use the code sample from the AppendCriteriaStringDemo project to cope with this task.

2.7. How to rename a table in the query programmatically

To rename a table in the query you may simply iterate through the datasources (learn basics in the Understanding complex SQL query structure representation and Enumeration of data sources and joins in a sub-query articles) and rename them, but the fine point is that you should call the QueryBuilder.Query.NotifySQLUpdatedRecursive() method to apply your changes to the query text.

Dictionary renamedDataSources = new Dictionary();
renamedDataSources.Add("old","new");

queryBuilder1.BeginUpdate();
try
{
    UnionSubQuery firstSelect = queryBuilder1.Query.FirstSelect();
    System.Collections.ArrayList dataSourcesArrayList = new System.Collections.ArrayList();

    firstSelect.FromClause.GetDatasources(dataSourcesArrayList);
    foreach (DataSourceObject dataSourceObject in dataSourcesArrayList)
    {
        SQLDatabaseObject databaseObject = dataSourceObject.DatabaseObject;
        string originalDataSourceName = databaseObject.QualifiedNameWithoutQuotes;

        if (renamedDataSources.ContainsKey(originalDataSourceName))
        {
            databaseObject.RemoveLastName();
            databaseObject.AddName(renamedDataSources[originalDataSourceName]);
        }
    }
    queryBuilder1.Query.NotifySQLUpdatedRecursive();
}
finally
{
    queryBuilder1.EndUpdate();
}

2.8. How to insert DISTINCT keyword into a query

Use the following code to insert DISTINCT keyword into a query:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
selexpr.SelectMode = new SQLSubQuerySelectModeDistinct(queryBuilder1.SQLContext);
queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

2.9. How to add TOP clause to MS SQL Server query

To get access to the TOP clause of MS SQL Server query, you should use the MSSQLSubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
MSSQLSubQuerySelectExpressionExtender extender = (MSSQLSubQuerySelectExpressionExtender) selexpr.Extender;

Use the following code to add the top clause programmatically:

// add "TOP n" clause
SQLSubQueryTopRows top = new SQLSubQueryTopRows(queryBuilder1.SQLContext);

// or "TOP n PERCENT" clause
// SQLSubQueryTopPercent top = new SQLSubQueryTopPercent(queryBuilder1.SQLContext);

// add "... WITH TIES" if needed
top.WithTies = new SQLSubQueryTopWithTies(queryBuilder1.SQLContext);

top.Count = queryBuilder1.SQLContext.ParseExpression("10", null);

extender.Top = top;

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

2.10. How to add LIMIT clause to MySQL query

To get access to the LIMIT clause of MySQL Server query, you should use the MySQLSubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
MySQLSubQuerySelectExpressionExtender extender = (MySQLSubQuerySelectExpressionExtender) selexpr.Extender;

Use one of the following code samples to add the LIMIT clause programmatically:

// add "LIMIT n" clause
extender.Limit = new SQLLimit(queryBuilder1.SQLContext);
extender.Limit.Limit = 100;

// or add "LIMIT n OFFSET n"
extender.Limit = new SQLLimitOffset(queryBuilder1.SQLContext);
extender.Limit.Limit = 100;
((SQLLimitOffset) extender.Limit).Offset = 20;

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

2.11. How to add FETCH FIRST to DB2 query

To get access to the FETCH FIRST clause of a DB2 query, you should use the DB2SubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
DB2SubQuerySelectExpressionExtender extender = (DB2SubQuerySelectExpressionExtender) selexpr.Extender;

Use one of the following code samples to add the LIMIT clause programmatically:

extender.FetchFirst = new SQLFetchFirst(queryBuilder1.SQLContext);
extender.FetchFirst.Count = new AstTokenNumber(queryBuilder1.SQLContext, "10");

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

2.12. How to add/check ROWNUM condition in Oracle query

Use the following function to add new or to modify existing ROWNUM condition for the Oracle query:

public void SetOracelRownum(int rowCount)
{
// set ROWNUM for the currently selected sub-query
UnionSubQuery usq = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery;

// or set ROWNUM for the main query
UnionSubQuery usq = queryBuilder1.Query.FirstSelect;

ArrayList ciList = new ArrayList();

CriteriaItem rownumItem = null;

// Check the query already contains ROWNUM criteria item
for (int i = 0; i < usq.CriteriaList.Count; i++)
{
if (usq.CriteriaList[i].ExpressionString.Equals("ROWNUM", StringComparison.CurrentCultureIgnoreCase))
{
rownumItem = usq.CriteriaList[i];
break;
}
}

if (rownumItem == null)
{
// Add new ROWNUM criteria item
rownumItem = usq.CriteriaList.AddExpression("ROWNUM");
}

// Clear previously set rownum conditions
for (int i = 0; i < usq.CriteriaList.GetMaxConditionCount(); i++)
{
rownumItem.ConditionStrings[i] = "";
}

if (usq.CriteriaList.GetMaxConditionCount() == 0)
{
// Query contains no other conditions.
// Just set the new rownum condition.
rownumItem.ConditionStrings[0] = "< " + rowCount;
}
else
{
// Query contains other conditions.
// Add rownum condition to every OR condition in the query.

for (int i = 0; i < usq.CriteriaList.GetMaxConditionCount(); i++)
{
// CollectCriteriaItemsWithWhereCondition:
// This function returns the list of conditions that were found in
// the i-th criteria column, applied to specific clause (WHERE or HAVING).
// Thus, this function collects all conditions joined with AND
// within one OR operator.
CollectCriteriaItemsWithWhereCondition(usq.CriteriaList, i, ciList);

ciList.Remove(rownumItem);

if (ciList.Count > 0)
{
rownumItem.ConditionStrings[i] = "< " + rowCount;
}
}
}
}

private void CollectCriteriaItemsWithWhereCondition(CriteriaList criteriaList, int columnIndex, ArrayList result)
{
result.Clear();

for (int i = 0; i < criteriaList.Count; i++)
{
if (criteriaList[i].ConditionType == ConditionType.Where &&
criteriaList[i].ConditionCount > columnIndex &&
criteriaList[i].GetASTCondition(columnIndex) != null)
{
result.Add(criteriaList[i]);
}
}
}