private void tsbCopyUnionSubquery_Click(object sender, EventArgs e)
{
// add empty UnionSubQuery
UnionSubQuery usq = queryBuilder1.ActiveUnionSubQuery.ParentGroup.Add();
// copy the content of existing union sub-query to a new one
SQLSubQuerySelectExpression usqAst = queryBuilder1.ActiveUnionSubQuery.ResultQueryAST;
usqAst.RestoreColumnPrefixRecursive(true);
List <SQLWithClauseItem> lCte = new List <SQLWithClauseItem>();
List <SQLFromSource> lFromObj = new List <SQLFromSource>();
queryBuilder1.ActiveUnionSubQuery.GatherPrepareAndFixupContext(lCte, lFromObj, false);
usqAst.PrepareAndFixupRecursive(lCte, lFromObj);
usq.LoadFromAST(usqAst);
queryBuilder1.ActiveUnionSubQuery = usq;
}
private void LoadQueryWithUnions()
{
UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();
DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Customers");
// create output column
QueryColumnListItem ci1 = unionSubQuery.QueryColumnList.AddField(ds1, "City");
ci1.Selected = true;
UnionSubQuery union1 = unionSubQuery.ParentGroup.Add();
DataSource ds2 = _query.AddObject(union1, "MyDB.MySchema.Orders");
// create output column with grouping
QueryColumnListItem ci2 = union1.QueryColumnList.AddField(ds2, "City");
ci2.Selected = true;
ci2.Grouping = true;
// Copy UnionSubQuery
// add an empty UnionSubQuery
UnionSubQuery usq = unionSubQuery.ParentGroup.Add();
// copy the content of existing union sub-query to a new one
SQLSubQuerySelectExpression usqAst = unionSubQuery.ResultQueryAST;
usqAst.RestoreColumnPrefixRecursive(true);
List <SQLWithClauseItem> lCte = new List <SQLWithClauseItem>();
List <SQLFromSource> lFromObj = new List <SQLFromSource>();
unionSubQuery.GatherPrepareAndFixupContext(lCte, lFromObj, false);
usqAst.PrepareAndFixupRecursive(lCte, lFromObj);
usq.LoadFromAST(usqAst);
QueryColumnListItem ci3 = usq.QueryColumnList.AddField(ds1, "CustomerAddress");
ci3.Selected = true;
}
private string GetWhereInfo(UnionSubQuery unionSubQuery)
{
StringBuilder stringBuilder = new StringBuilder();
SQLSubQuerySelectExpression unionSubQueryAst = unionSubQuery.ResultQueryAST;
try
{
if (unionSubQueryAst.Where != null)
{
DumpWhereInfo(stringBuilder, unionSubQueryAst.Where);
}
}
finally
{
unionSubQueryAst.Dispose();
}
return(stringBuilder.ToString());
}
private void UpdateWhereInfo()
{
UnionSubQuery unionSubQuery = Builder.ActiveUnionSubQuery;
StringBuilder stringBuilder = new StringBuilder();
SQLSubQuerySelectExpression unionSubQueryAst = unionSubQuery.ResultQueryAST;
try
{
if (unionSubQueryAst.Where != null)
{
WhereInfo.DumpWhereInfo(stringBuilder, unionSubQueryAst.Where);
}
}
finally
{
unionSubQueryAst.Dispose();
}
tbWhere.Text = stringBuilder.ToString();
}
private void tsbAddDerivedTable_Click(object sender, EventArgs e)
{
using (new UpdateRegion(queryBuilder1.ActiveUnionSubQuery.FromClause))
{
var sqlContext = queryBuilder1.ActiveUnionSubQuery.SQLContext;
var fq = new SQLFromQuery(sqlContext)
{
Alias = new SQLAliasObjectAlias(sqlContext)
{
Alias = queryBuilder1.ActiveUnionSubQuery.QueryRoot.CreateUniqueSubQueryName()
},
SubQuery = new SQLSubSelectStatement(sqlContext)
};
var sqse = new SQLSubQuerySelectExpression(sqlContext);
fq.SubQuery.Add(sqse);
sqse.SelectItems = new SQLSelectItems(sqlContext);
sqse.From = new SQLFromClause(sqlContext);
queryBuilder1.SQLQuery.AddObject(queryBuilder1.ActiveUnionSubQuery, fq, typeof(DataSourceQuery));
}
}
public void AddDerivedTable()
{
using (new UpdateRegion(QueryView.ActiveUnionSubQuery.FromClause))
{
var sqlContext = QueryView.ActiveUnionSubQuery.SQLContext;
var fq = new SQLFromQuery(sqlContext)
{
Alias = new SQLAliasObjectAlias(sqlContext)
{
Alias = QueryView.ActiveUnionSubQuery.QueryRoot.CreateUniqueSubQueryName()
},
SubQuery = new SQLSubSelectStatement(sqlContext)
};
var sqse = new SQLSubQuerySelectExpression(sqlContext);
fq.SubQuery.Add(sqse);
sqse.SelectItems = new SQLSelectItems(sqlContext);
sqse.From = new SQLFromClause(sqlContext);
NavigationBar.Query.AddObject(QueryView.ActiveUnionSubQuery, fq, typeof(DataSourceQuery));
}
}
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;
}
