The following methods of the IQueryController interface (implemented in the SQLQuery class) are intended to add new objects to the query and link them to exising query objects:
  • AddObject - adds a database object (table, view or synonym) to the query.
  • AddObjectFromExpression - adds a datasource represented by SQL expression (a derived table) to the query.
  • AddStoredProcedure - adds a stored procedure to the query (those that can be used in the FROM clause of SELECT statements).
  • AddLink - creates a join between two datasources in the query.
  • AddObjectUI - adds a database object to the query and creates links as prescribed by the CreateLinksFromForeignKeys, CreateLinksByIdenticalFieldNames and AddLinkedObjects properties.
Example:
public void CreateQuery()
{
    UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();

    // add data source objects into the query
    DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Customers", "c");
    DataSource ds2 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Orders", "o");
    DataSource ds3 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Resellers", "r");

    // manually create relation between 'Resellers' and 'Orders'
    _query.AddLink(ds3, "ResellerID", ds2, "ResellerID");
}
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 (CreateLinksByIdenticalFieldNames, CreateLinksFromForeignKeys).
The "WithFK"-suffixed methods are obsolete. Use the separate methods to add links to existing query objects automatically:
  • UnionSubQuery.AddFKLinksForObject
  • UnionSubQuery.AddIdenticalFieldsLinksForObject
Example:
UnionSubQuery usq = queryBuilder1.Query.ActiveUnionSubquery;
DataSource dataSource = usq.AddObject("Orders");

int addedLinksNum = usq.AddFKLinksForObject(dataSource);

if (addedLinksNum == 0) 
{
    usq.AddLinkedObjectsFor(dataSource);
}

Joining indirectly referenced objects

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.
The complete code sample of prgrammatic SQL query creation can be found in the Non-visual Demo project that's included in the installation package (see the CreateQuery procedure).

Is this article helpful for you?