Enumeration of data sources and joins in a (sub)query
Last modified:
The UnionSubQuery.FromClause object holds the list of SELECT statement datasources and their links.
Below are the fragments of the QueryStructureDemo project that included in the installation package. You may review it for more details.
1. Enumeration of all datasources in a single SELECT statement.
You can get the plain list of datasources using the GetDatasourceByClass() method. There are DataSourceObject and DataSourceQuery classes representing database objects and derived tables correspondingly. The DataSourceQuery has the Query property to get access to SubQuery object representing this derived table. The DataSorceObject has MetadataObject property to get access to the database object details.
public void DumpDataSourcesInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
DumpDataSourcesInfo(stringBuilder, GetDataSourceList(unionSubQuery));
}
private void DumpDataSourcesInfo(StringBuilder stringBuilder, ArrayList dataSources)
{
for (int i = 0; i < dataSources.Count; i++)
{
if (stringBuilder.Length > 0)
{
stringBuilder.AppendLine();
}
DumpDataSourceInfo(stringBuilder, (DataSource)dataSources[i]);
}
}
private void DumpDataSourceInfo(StringBuilder stringBuilder, DataSource dataSource)
{
// write full sql fragment
stringBuilder.AppendLine(dataSource.GetResultSQL());
// write alias
stringBuilder.AppendLine(" alias: " + dataSource.Alias);
// write referenced MetadataObject (if any)
if (dataSource.MetadataObject != null)
{
stringBuilder.AppendLine(" ref: " + dataSource.MetadataObject.FullNameStr);
}
// write subquery (if datasource is actually a derived table)
if (dataSource is DataSourceQuery)
{
stringBuilder.AppendLine(" subquery sql: " + ((DataSourceQuery) dataSource).Query.GetResultSQL());
}
// write fields
string fields = String.Empty;
for (int i = 0; i < dataSource.Fields.Count; i++)
{
if (fields.Length > 0)
{
fields += ", ";
}
fields += dataSource.Fields[i].NameStr;
}
stringBuilder.AppendLine(" fields (" + dataSource.Fields.Count.ToString() + "): " + fields);
}
private ArrayList GetDataSourceList(UnionSubQuery unionSubQuery)
{
ArrayList list = new ArrayList();
unionSubQuery.FromClause.GetDatasourceByClass(typeof(DataSource), list);
return list;
}
2. Enumeration of all links in a single SELECT statement.
You may get the list of links using the GetLinksRecursive() method. Each Link object has references to it's left and right datasources (LeftDatasource, RightDatasource), linked fields (LeftField, RightField) and has properties to read and change it's join type (LeftType, RightType). Link expression is available trough the LinkExpression property.
public void DumpLinksInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
DumpLinksInfo(stringBuilder, GetLinkList(unionSubQuery));
}
private void DumpLinksInfo(StringBuilder stringBuilder, ArrayList links)
{
for (int i = 0; i < links.Count; i++)
{
if (stringBuilder.Length > 0)
{
stringBuilder.AppendLine();
}
DumpLinkInfo(stringBuilder, (Link) links[i]);
}
}
private void DumpLinkInfo(StringBuilder stringBuilder, Link link)
{
// write full sql fragment of link expression
stringBuilder.AppendLine(link.LinkExpression.SimpleSQL(link.SQLContext.SQLBuilderExpression));
// write information about left side of link
stringBuilder.AppendLine(" left datasource: " + link.LeftDatasource.GetResultSQL());
if (link.LeftType == LinkSideType.Inner)
{
stringBuilder.AppendLine(" left type: Inner");
}
else
{
stringBuilder.AppendLine(" left type: Outer");
}
// write information about right side of link
stringBuilder.AppendLine(" right datasource: " + link.RightDatasource.GetResultSQL());
if (link.RightType == LinkSideType.Inner)
{
stringBuilder.AppendLine(" lerightft type: Inner");
}
else
{
stringBuilder.AppendLine(" right type: Outer");
}
}
private ArrayList GetLinkList(UnionSubQuery unionSubQuery)
{
ArrayList links = new ArrayList();
unionSubQuery.FromClause.GetLinksRecursive(links);
return links;
}