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