protected void Page_Load(object sender, EventArgs e)
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("QueryModification");
if (qb == null)
{
qb = CreateQueryBuilder();
}
QueryBuilderControl1.QueryBuilder = qb;
ObjectTreeView1.QueryBuilder = qb;
Canvas1.QueryBuilder = qb;
Grid1.QueryBuilder = qb;
SubQueryNavigationBar1.QueryBuilder = qb;
SqlEditor1.QueryBuilder = qb;
StatusBar1.QueryBuilder = qb;
cbCompanyName.Enabled = cbCustomers.Checked;
tbCompanyName.Enabled = cbCompanyName.Checked;
cbOrderDate.Enabled = cbOrders.Checked;
tbOrderDate.Enabled = cbOrderDate.Checked;
//prepare parsed names
_joinFieldName = qb.SQLContext.ParseQualifiedName(CusomerId);
_companyNameFieldName = qb.SQLContext.ParseQualifiedName(CustomersCompanyName);
_orderDateFieldName = qb.SQLContext.ParseQualifiedName(OrderDate);
}
private DataSource FindTableInQueryByName(UnionSubQuery unionSubQuery, SQLQualifiedName name)
{
List <DataSource> foundDatasources = new List <DataSource>();
unionSubQuery.FromClause.FindTablesByDBName(name, foundDatasources);
// if found more than one tables with given name in the query, use the first one
return(foundDatasources.Count > 0 ? foundDatasources[0] : null);
}
protected override void Dispose(bool disposing)
{
if (_defaultDatabaseName != null)
{
_defaultDatabaseName.Dispose();
_defaultDatabaseName = null;
}
base.Dispose(disposing);
}
private void buttonStats_Click(object sender, EventArgs e)
{
var result = new StringBuilder();
// collect all subQueries
var subQueries = queryBuilder.SQLQuery.QueryRoot.GetChildrenRecursive <SubQuery>(true);;
// process main query also
subQueries.Insert(0, queryBuilder.SQLQuery.QueryRoot);
// OR collect unionSubQueries (single SELECT expressions)
//var subQueries = queryBuilder.SQLQuery.QueryRoot.GetChildrenRecursive<UnionSubQuery>(true);
foreach (var subQuery in subQueries)
{
result.AppendLine();
result.AppendLine("subQuery: " + subQuery.SQL);
// collect all dataSources in this subQuery
var dataSources = subQuery.GetChildrenRecursive <DataSourceObject>(false);
foreach (var dataSource in dataSources)
{
result.AppendLine(dataSource.NameInQuery);
var metadataObject = dataSource.MetadataObject;
// metadataObject will be null in 2 cases:
// 1. dataSource is CTE reference
if (dataSource.SubQueryCTE != null)
{
result.AppendLine("\tCTE reference");
continue;
}
// 2. no object with such name in MetadataContainer
if (metadataObject == null)
{
using (var fullName = new SQLQualifiedName(queryBuilder.SQLContext))
{
fullName.Assign(dataSource.DatabaseObject);
result.AppendLine("\tno such object in DB: " + fullName.GetSQL(queryBuilder.SQLGenerationOptions));
}
continue;
}
result.AppendLine("\tobject name: " + metadataObject.GetQualifiedNameSQL(null, queryBuilder.SQLGenerationOptions));
result.AppendLine("\tconnection: " + metadataObject.Connection.Name);
}
}
MessageBox.Show(result.ToString());
}
protected override void OnLoad(EventArgs e)
{
// load metadata from XML file located in resource (for demonstration purposes)
queryBuilder1.MetadataContainer.LoadingOptions.OfflineMode = true;
queryBuilder1.MetadataContainer.ImportFromXML("Northwind.xml");
queryBuilder1.InitializeDatabaseSchemaTree();
queryBuilder1.SQLUpdated += SqlUpdated;
//prepare parsed names
_joinFieldName = queryBuilder1.SQLContext.ParseQualifiedName(CusomerId);
_companyNameFieldName = queryBuilder1.SQLContext.ParseQualifiedName(CustomersCompanyName);
_orderDateFieldName = queryBuilder1.SQLContext.ParseQualifiedName(OrderDate);
base.OnLoad(e);
}
public void ApplyChanges(Model m)
{
var queryBuilder1 = QueryBuilderStore.Get(InstanceId);
DataSource customers = null;
DataSource orders = null;
QueryColumnListItem _companyName = null;
QueryColumnListItem _orderDate = null;
//prepare parsed names
SQLQualifiedName joinFieldName = queryBuilder1.SQLContext.ParseQualifiedName(CusomerId);
SQLQualifiedName companyNameFieldName = queryBuilder1.SQLContext.ParseQualifiedName(CustomersCompanyName);
SQLQualifiedName orderDateFieldName = queryBuilder1.SQLContext.ParseQualifiedName(OrderDate);
// get the active SELECT
var usq = queryBuilder1.ActiveUnionSubQuery;
#region actualize stored references (if query is modified in GUI)
#region actualize datasource references
// if user removed previously added datasources then clear their references
if (customers != null && !IsTablePresentInQuery(usq, customers))
{
// user removed this table in GUI
customers = null;
}
if (orders != null && !IsTablePresentInQuery(usq, orders))
{
// user removed this table in GUI
orders = null;
}
#endregion
// clear CompanyName conditions
if (_companyName != null)
{
// if user removed entire row OR cleared expression cell in GUI, clear the stored reference
if (!IsQueryColumnListItemPresentInQuery(usq, _companyName))
_companyName = null;
}
// clear all condition cells for CompanyName row
if (_companyName != null)
{
ClearConditionCells(usq, _companyName);
}
// clear OrderDate conditions
if (_orderDate != null)
{
// if user removed entire row OR cleared expression cell in GUI, clear the stored reference
if (!IsQueryColumnListItemPresentInQuery(usq, _orderDate))
_orderDate = null;
}
// clear all condition cells for OrderDate row
if (_orderDate != null)
{
ClearConditionCells(usq, _orderDate);
}
#endregion
#region process Customers table
if (m.Customers)
{
// if we have no previously added Customers table, try to find one already added by the user
if (customers == null)
{
customers = FindTableInQueryByName(usq, CustomersName);
}
// there is no Customers table in query, add it
if (customers == null)
{
customers = AddTable(usq, CustomersName, CustomersAlias);
}
#region process CompanyName condition
if (m.CompanyName && !String.IsNullOrEmpty(m.CompanyNameText))
{
// if we have no previously added grid row for this condition, add it
if (_companyName == null || _companyName.IsDisposing)
{
_companyName = usq.QueryColumnList.AddField(customers, companyNameFieldName.QualifiedName);
// do not append it to the select list, use this row for conditions only
_companyName.Selected = false;
}
// write condition from edit box to all needed grid cells
AddWhereCondition(usq.QueryColumnList, _companyName, m.CompanyNameText);
}
else
{
// remove previously added grid row
if (_companyName != null)
{
_companyName.Dispose();
}
_companyName = null;
}
#endregion
}
else
{
// remove previously added datasource
if (customers != null)
{
customers.Dispose();
}
customers = null;
}
#endregion
#region process Orders table
if (m.Orders)
{
// if we have no previosly added Orders table, try to find one already added by the user
if (orders == null)
{
orders = FindTableInQueryByName(usq, OrdersName);
}
// there are no Orders table in query, add one
if (orders == null)
{
orders = AddTable(usq, OrdersName, OrdersAlias);
}
#region link between Orders and Customers
// we added Orders table,
// check if we have Customers table too,
// and if there are no joins between them, create such join
string joinFieldNameStr = joinFieldName.QualifiedName;
if (customers != null &&
usq.FromClause.FindLink(orders, joinFieldNameStr, customers, joinFieldNameStr) == null &&
usq.FromClause.FindLink(customers, joinFieldNameStr, orders, joinFieldNameStr) == null)
{
queryBuilder1.SQLQuery.AddLink(customers, joinFieldName, orders, joinFieldName);
}
#endregion
#region process OrderDate condition
if (m.OrderDate && !String.IsNullOrEmpty(m.OrderDateText))
{
// if we have no previously added grid row for this condition, add it
if (_orderDate == null)
{
_orderDate = usq.QueryColumnList.AddField(orders, orderDateFieldName.QualifiedName);
// do not append it to the select list, use this row for conditions only
_orderDate.Selected = false;
}
// write condition from edit box to all needed grid cells
AddWhereCondition(usq.QueryColumnList, _orderDate, m.OrderDateText);
}
else
{
// remove prviously added grid row
if (_orderDate != null)
{
_orderDate.Dispose();
}
_orderDate = null;
}
#endregion
}
else
{
if (orders != null)
{
orders.Dispose();
orders = null;
}
}
#endregion
}
private void LoadQueryWithDerivedTableAndCte()
{
UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();
// Derived Table
SQLFromQuery fq = new SQLFromQuery(_query.SQLContext)
{
Alias = new SQLAliasObjectAlias(_query.SQLContext)
{
Alias = _query.QueryRoot.CreateUniqueSubQueryName()
},
SubQuery = new SQLSubSelectStatement(_query.SQLContext)
};
var sqse = new SQLSubQuerySelectExpression(_query.SQLContext);
fq.SubQuery.Add(sqse);
sqse.SelectItems = new SQLSelectItems(_query.SQLContext);
sqse.From = new SQLFromClause(_query.SQLContext);
var dataSourceQuery = (DataSourceQuery)_query.AddObject(unionSubQuery, fq, typeof(DataSourceQuery));
var usc = dataSourceQuery.SubQuery.FirstSelect();
var dsDerivedTable = _query.AddObject(usc, "MyDB.MySchema.SalesOrderHeader");
var ciDerivedTable1 = usc.QueryColumnList.AddField(dsDerivedTable, "OrderDate");
ciDerivedTable1.Selected = true;
var ciDerivedTable2 = usc.QueryColumnList.AddField(dsDerivedTable, "SalesOrderID");
ciDerivedTable2.ConditionStrings[0] = "> 25";
// CTE
var qn = new SQLQualifiedName(_query.SQLContext);
if (!unionSubQuery.QueryRoot.SQLContext.SyntaxProvider.IsSupportCTE())
{
return;
}
DataSource dataSourceCte;
try
{
var withClauseItemName = _query.QueryRoot.CreateUniqueCTEName("CTE");
qn.Add(withClauseItemName);
var parentSubQuery = unionSubQuery.ParentSubQuery ?? unionSubQuery.QueryRoot;
if (parentSubQuery.IsMainQuery)
{
_query.QueryRoot.AddNewCTE(null, withClauseItemName);
}
else
{
if (parentSubQuery.IsSubQueryCTE())
{
var index = parentSubQuery.GetSubQueryCTEIndex();
parentSubQuery.InsertNewCTE(index, null, withClauseItemName);
}
else
{
parentSubQuery.AddNewCTE(null, withClauseItemName);
}
}
if (_query.IsUniqueAlias(unionSubQuery, withClauseItemName))
{
dataSourceCte = _query.AddObject(unionSubQuery, qn, null);
}
else
{
var withClauseItemNameStr = withClauseItemName.GetSQL(new SQLGenerationOptions());
using (var alias = _query.CreateUniqueAlias(unionSubQuery, withClauseItemNameStr))
dataSourceCte = _query.AddObject(unionSubQuery, qn, alias);
}
}
finally
{
qn.Dispose();
}
var cte = _query.QueryRoot.GetSubQueryCTEList().FirstOrDefault();
if (cte == null)
{
return;
}
var unionSubQueryCte = cte.FirstSelect();
var ds1 = _query.AddObject(unionSubQueryCte, "MyDB.MySchema.Customers");
// create output column
QueryColumnListItem ci1 = unionSubQueryCte.QueryColumnList.AddField(ds1, "CustomerName");
ci1.Selected = true;
// create output column
QueryColumnListItem ci2 = unionSubQuery.QueryColumnList.AddField(dataSourceCte, "CustomerName");
ci2.Selected = true;
}
private MetadataItem FindItemByName(SQLQualifiedName name)
{
return(databaseSchemaView1.MetadataStructure.MetadataItem.FindItem <MetadataItem>(name));
}
