Home → Active Query Builder → Programmatic modification of SQL queries → How to add a relation programmatically

7.3. How to add a relation programmatically

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