How to add objects and joins to the query programmatically?
Last modified:
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 existing 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.
- AddForeignKeyLinksForObject and AddIdenticalFieldsLinksForObject methods add links to the given object from other objects that exist in the query according to the foreign key relationships or by identical field names.
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");
}
Four methods can help you with this:
- UnionSubQuery.AddObject
- UnionSubQuery.AddObjectAt
- UnionSubQuery.AddObjectWithFK
- UnionSubQuery.AddObjectWithFKAt
Use the methods with the "At" suffix when dragging objects to the Design Pane as they allow to specify the 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 joining an object with other query objects by foreign keys even if it doesn't directly join with existing objects. Intermediate objects will be added to the query automatically (if such objects exist) in order to join this object.
More code samples of programmatic SQL query creation can be found in the QueryCreation project that's included in the installation package and available on GitHub (see the LoadQuery* methods of the form).