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