HomeActive Query Builder - Query analysis and modificationProgrammatic modification of SQL queriesHow to add a join programmatically

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

This page was: Helpful | Not Helpful