How to check if a query contains specific tables and they are joined correctly?
Last modified:
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
string 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
SQLQualifiedName ordersQualifiedName = queryBuilder1.SQLContext.ParseQualifiedName("Orders");
SQLQualifiedName customersQualifiedName = queryBuilder1.SQLContext.ParseQualifiedName("Customers");
string joinFieldName = "CustomerID";
// synchronously load the query in the Query Builder
queryBuilder1.SQL = q;
// get reference to the UnionSubQuery
UnionSubQuery unionSubQuery = queryBuilder1.ActiveUnionSubQuery;
// get reference to the FromClause
DataSourceGroup fromClause = unionSubQuery.FromClause;
List<DataSourceObject> ordersTables = new List<DataSourceObject>();
// try to find Orders table
fromClause.FindTablesByDbName(ordersQualifiedName, ordersTables);
if (ordersTables.Count == 0)
{
// Orders table not found - add it
ordersTables.Add((DataSourceObject) queryBuilder1.SQLQuery.AddObject(unionSubQuery, ordersQualifiedName, null));
}
List<DataSourceObject> customersTables = new List<DataSourceObject>();
// try to find Customers table
fromClause.FindTablesByDbName(customersQualifiedName, customersTables);
if (customersTables.Count == 0)
{
// Customers table not found - add it
customersTables.Add((DataSourceObject) queryBuilder1.SQLQuery.AddObject(unionSubQuery, customersQualifiedName, null));
}
// check existence of link between each Orders and each Customers
for (int iOrders = 0; iOrders < ordersTables.Count; iOrders++)
{
DataSource ordersDataSource = ordersTables[iOrders];
for (int iCustomers = 0; iCustomers < customersTables.Count; iCustomers++)
{
DataSource customersDataSource = customersTables[iCustomers];
if (fromClause.FindLink(ordersDataSource, joinFieldName, customersDataSource, joinFieldName) == null &&
fromClause.FindLink(customersDataSource, joinFieldName, ordersDataSource, joinFieldName) == null)
{
queryBuilder1.SQLQuery.AddLink(ordersDataSource, joinFieldName, customersDataSource, joinFieldName);
}
}
}
}
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);
}
}
}
}