Active Query Builder support area

How to join an object that has just been added by the user with existing objects in the query?

Last modified:


The DatasourceAdded event is fired right after an object is added to the query. Use it to take additional actions, like adding joins or setting datasource alias. 

The DatasourceAdding event lets deny the addition of object by the user.

private void queryBuilder1_DataSourceAdded(SQLQuery query, DataSource addedObject)
{
    List<DataSource> dataSources = new List<DataSource>();
    addedObject.ParentUnionSubQuery.FromClause.GetDatasources(dataSources);
 
    // enumerate data source links
    foreach (MetadataField currentTableField in addedObject.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 != addedObject &&
                    otherDataSource.MetadataObject != addedObject.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 (addedObject.ParentUnionSubQuery.FindLink(addedObject, currentTableField.Name, otherDataSource, otherTableField.Name) == null)
                            {
                                // add link
                                query.AddLink(addedObject, 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 != addedObject &&
                    otherDataSource.MetadataObject != addedObject.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.ParentUnionSubQuery.FindLink(otherDataSource, otherTableField.Name, addedObject, currentTableField.Name) == null)
                                {
                                    // add link
                                    query.AddLink(otherDataSource, otherTableField.Name, addedObject, currentTableField.Name);
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
private void queryBuilder1_DatasourceAdded(DataSource dataSource)
{
    ArrayList dataSources = new ArrayList();
    dataSource.Query.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);
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Is this article helpful for you?