public string DumpSelectedExpressionsInfoFromUnionSubQuery(UnionSubQuery unionSubQuery)
{
var stringBuilder = new StringBuilder();
// get list of CriteriaItems
QueryColumnList criteriaList = unionSubQuery.QueryColumnList;
// dump all items
for (int i = 0; i < criteriaList.Count; i++)
{
QueryColumnListItem criteriaItem = criteriaList[i];
// only items have .Select property set to True goes to SELECT list
if (!criteriaItem.Selected)
{
continue;
}
// separator
if (stringBuilder.Length > 0)
{
stringBuilder.AppendLine("<br />");
}
DumpSelectedExpressionInfo(stringBuilder, criteriaItem);
}
return(stringBuilder.ToString());
}
private void buttonClearWhere_Click(object sender, EventArgs e)
{
// get reference to first UnionSubQuery of the Main query
UnionSubQuery usq = queryBuilder1.SQLQuery.QueryRoot.FirstSelect();
ClearWhere(usq);
}
public string DumpLinksInfoFromUnionSubQuery(UnionSubQuery unionSubQuery)
{
var stringBuilder = new StringBuilder();
DumpLinksInfo(stringBuilder, GetLinkList(unionSubQuery));
return(stringBuilder.ToString());
}
public string DumpDataSourcesInfoFromUnionSubQuery(UnionSubQuery unionSubQuery)
{
StringBuilder stringBuilder = new StringBuilder();
DumpDataSourcesInfo(stringBuilder, GetDataSourceList(unionSubQuery));
return(stringBuilder.ToString());
}
private void LoadQueryWithAggregateAndGroup()
{
UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();
// add an object to the query
DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Customers");
// create two output columns
QueryColumnListItem ci1 = unionSubQuery.QueryColumnList.AddField(ds1, "CustomerID");
ci1.Selected = true;
QueryColumnListItem ci2 = unionSubQuery.QueryColumnList.AddField(ds1, "CustomerValue");
ci2.Selected = true;
// add WHERE condition
ci2.ConditionStrings[0] = "> 100";
// specify order by
ci2.SortType = ItemSortType.Desc;
ci2.SortOrder = 0;
// group by
ci2.Grouping = true;
// define aggregate function for the first column
ci1.AggregateString = "Count";
// add a HAVING clause
ci1.ConditionStrings[0] = "> 10";
ci1.ConditionType = ConditionType.Having;
}
private void ClearConditionCells(UnionSubQuery unionSubQuery, QueryColumnListItem item)
{
for (int i = 0; i < unionSubQuery.QueryColumnList.GetMaxConditionCount(); i++)
{
item.ConditionStrings[i] = "";
}
}
private void LoadQuerySimple()
{
UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();
// add three objects to the query
DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Customers", "c");
DataSource ds2 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Orders", "o");
DataSource ds3 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Resellers", "r");
// create a relation between 'Resellers' and 'Orders'
_query.AddLink(ds3, "ResellerID", ds2, "ResellerID");
// create WHERE condition
QueryColumnListItem ci2 = unionSubQuery.QueryColumnList.AddExpression("CustomerID");
// do not add this item to the select list
ci2.Selected = false;
ci2.ConditionStrings[0] = "> 0";
// create an output column
QueryColumnListItem ci3 = unionSubQuery.QueryColumnList.AddField(ds1, "CustomerName");
// add this item to the select list
ci3.Selected = true;
}
private void ClearWhere(UnionSubQuery unionSubQuery)
{
// get reference to QueryColumnList
QueryColumnList cl = unionSubQuery.QueryColumnList;
// disable SQL updating for QueryBuilder
queryBuilder1.BeginUpdate();
try
{
// clear old Where
for (int i = 0; i < cl.Count; i++)
{
QueryColumnListItem ci = cl[i];
if (ci.ConditionType == ConditionType.Where)
{
// clear all WHERE expressions in a row
for (int j = 0; j < ci.ConditionCount; j++)
{
ci.ConditionStrings[j] = "";
}
}
}
}
finally
{
queryBuilder1.EndUpdate();
}
}
private void buttonReplaceWhereAccess_Click(object sender, EventArgs e)
{
// get reference to first UnionSubQuery of the Main query
UnionSubQuery usq = queryBuilder1.SQLQuery.QueryRoot.FirstSelect();
ReplaceWhereAccess(usq, textBoxNewWhereAccess.Text);
}
private void ReplaceWhereAccess(UnionSubQuery unionSubQuery, string newWhereStr)
{
// parse and prepare new WHERE expression
SQLExpressionItem newWhere = ParseExpressionAccess(newWhereStr);
if (newWhere != null)
{
FixupExpression(unionSubQuery, newWhere);
ConvertExpressionConstantsNotation(newWhere);
}
// defer SQL updates
unionSubQuery.BeginUpdate();
try
{
// clear old WHERE expression
ClearWhere(unionSubQuery);
// load new WHERE expression (if exists)
if (newWhere != null)
{
unionSubQuery.QueryColumnList.LoadConditionFromAST(newWhere, ConditionType.Where);
}
}
finally
{
// enable SQL updates
unionSubQuery.EndUpdate();
}
}
private ArrayList GetLinkList(UnionSubQuery unionSubQuery)
{
ArrayList links = new ArrayList();
unionSubQuery.FromClause.GetLinksRecursive(links);
return(links);
}
private ArrayList GetDataSourceList(UnionSubQuery unionSubQuery)
{
ArrayList list = new ArrayList();
unionSubQuery.FromClause.GetDatasourceByClass(typeof(DataSource), list);
return(list);
}
public static List <Link> GetLinkList(UnionSubQuery unionSubQuery)
{
var links = new List <Link>();
unionSubQuery.FromClause.GetLinksRecursive(links);
return(links);
}
private bool IsTablePresentInQuery(UnionSubQuery unionSubQuery, DataSource table)
{
// collect the list of datasources used in FROM
var dataSources = unionSubQuery.GetChildrenRecursive<DataSource>(false);
// check given table in list of all datasources
return dataSources.IndexOf(table) != -1;
}
private DataSource AddTable(UnionSubQuery unionSubQuery, string name, string alias)
{
using (var parsedName = queryBuilder1.SQLContext.ParseQualifiedName(name))
using (var parsedAlias = queryBuilder1.SQLContext.ParseIdentifier(alias))
{
return(queryBuilder1.QueryView.Query.AddObject(unionSubQuery, parsedName, parsedAlias));
}
}
private IList <Link> GetLinkList(UnionSubQuery unionSubQuery)
{
List <Link> links = new List <Link>();
unionSubQuery.FromClause.GetLinksRecursive(links);
return(links);
}
public static List <DataSource> GetDataSourceList(UnionSubQuery unionSubQuery)
{
var list = new List <DataSource>();
unionSubQuery.FromClause.GetDatasourceByClass(list);
return(list);
}
private List <DataSource> GetDataSourceList(UnionSubQuery unionSubQuery)
{
List <DataSource> list = new List <DataSource>();
unionSubQuery.FromClause.GetDatasourceByClass <DataSource>(list);
return(list);
}
private void UpdateSelectedExpressionsInfo()
{
UnionSubQuery unionSubQuery = Builder.ActiveUnionSubQuery;
StringBuilder stringBuilder = new StringBuilder();
SelectedExpressionsInfo.DumpSelectedExpressionsInfoFromUnionSubQuery(stringBuilder, unionSubQuery);
tbSelectedExpressions.Text = stringBuilder.ToString();
}
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);
}
private void UpdateDataSourcesInfo()
{
UnionSubQuery unionSubQuery = Builder.ActiveUnionSubQuery;
StringBuilder stringBuilder = new StringBuilder();
DataSourcesInfo.DumpDataSourcesInfoFromUnionSubQuery(stringBuilder, unionSubQuery);
tbDataSources.Text = stringBuilder.ToString();
}
private void UpdateLinksInfo()
{
UnionSubQuery unionSubQuery = Builder.ActiveUnionSubQuery;
StringBuilder stringBuilder = new StringBuilder();
LinksInfo.DumpLinksInfoFromUnionSubQuery(stringBuilder, unionSubQuery);
tbLinks.Text = stringBuilder.ToString();
}
private DataSource AddTable(UnionSubQuery unionSubQuery, string name, string alias)
{
var queryBuilder1 = QueryBuilderStore.Get(InstanceId);
using (var parsedName = queryBuilder1.SQLContext.ParseQualifiedName(name))
using (var parsedAlias = queryBuilder1.SQLContext.ParseIdentifier(alias))
{
return queryBuilder1.SQLQuery.AddObject(unionSubQuery, parsedName, parsedAlias);
}
}
private bool IsTablePresentInQuery(UnionSubQuery unionSubQuery, DataSource table)
{
// collect the list of datasources used in FROM
List <DataSource> dataSources = new List <DataSource>();
unionSubQuery.FromClause.GetDatasources(dataSources);
// check given table in list of all datasources
return(dataSources.IndexOf(table) != -1);
}
private bool IsRequiredItemsPresent(UnionSubQuery query)
{
var isTablePresent = query.FromClause.Items.OfType <DataSourceObject>()
.FirstOrDefault(x => x.MetadataObject.Name == RequiredTable) != null;
var isFieldPresent = query.QueryColumnList.Items
.FirstOrDefault(x => x.ExpressionString == RequiredFieldExpression) != null;
return(isTablePresent && isFieldPresent);
}
private DataSource FindTableInQueryByName(UnionSubQuery unionSubQuery, string name)
{
List <DataSourceObject> foundDatasources;
using (var qualifiedName = queryBuilder1.SQLContext.ParseQualifiedName(name))
{
foundDatasources = new List <DataSourceObject>();
unionSubQuery.FromClause.FindTablesByDbName(qualifiedName, foundDatasources);
}
// if found more than one tables with given name in the query, use the first one
return(foundDatasources.Count > 0 ? foundDatasources[0] : null);
}
public static string GetUnlinkedDataSourcesFromUnionSubQuery(UnionSubQuery unionSubQuery)
{
var dataSources = DataSourcesInfo.GetDataSourceList(unionSubQuery);
// Process trivial cases
if (dataSources.Count == 0)
{
return("There are no datasources in current UnionSubQuery!");
}
if (dataSources.Count == 1)
{
return("There are only one datasource in current UnionSubQuery!");
}
var links = LinksInfo.GetLinkList(unionSubQuery);
// The first DataSource is the initial point of reachability algorithm
var firstDataSource = dataSources[0];
// Remove all linked DataSources from dataSources list
GetUnlinkedDatsourcesRecursive(firstDataSource, dataSources, links);
// Now dataSources list contains only DataSources unreachable from the firstDataSource
if (dataSources.Count == 0)
{
return("All DataSources in the query are connected!");
}
else
{
// Some DataSources are not reachable - show them in a message box
var sb = new StringBuilder();
for (var i = 0; i < dataSources.Count; i++)
{
var dataSource = dataSources[i];
sb.AppendLine((i + 1) + ": " + dataSource.GetResultSQL());
}
return("The following DataSources are not reachable from the first DataSource:\r\n" + sb);
}
}
private void LoadQueryWithUnions()
{
UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();
DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Customers");
// create output column
var ci1 = unionSubQuery.QueryColumnList.AddField(ds1, "City");
ci1.Selected = true;
var union1 = unionSubQuery.ParentGroup.Add();
DataSource ds2 = _query.AddObject(union1, "MyDB.MySchema.Orders");
// create output column with grouping
var ci2 = union1.QueryColumnList.AddField(ds2, "City");
ci2.Selected = true;
ci2.Grouping = true;
// Copy UnionSubQuery
// add an empty UnionSubQuery
var usq = unionSubQuery.ParentGroup.Add();
// copy the content of existing union sub-query to a new one
var usqAst = unionSubQuery.ResultQueryAST;
usqAst.RestoreColumnPrefixRecursive(true);
var lCte = new List <SQLWithClauseItem>();
var lFromObj = new List <SQLFromSource>();
unionSubQuery.GatherPrepareAndFixupContext(lCte, lFromObj, false);
usqAst.PrepareAndFixupRecursive(lCte, lFromObj);
usq.LoadFromAST(usqAst);
var ci3 = usq.QueryColumnList.AddField(ds1, "CustomerAddress");
ci3.Selected = true;
}
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 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());
}
