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 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 static void DumpSelectedExpressionInfo(StringBuilder stringBuilder, QueryColumnListItem selectedExpression)
{
// write full sql fragment of selected expression
stringBuilder.AppendLine(selectedExpression.ExpressionString);
// write alias
if (!string.IsNullOrEmpty(selectedExpression.AliasString))
{
stringBuilder.AppendLine(" alias: " + selectedExpression.AliasString);
}
// write datasource reference (if any)
if (selectedExpression.ExpressionDatasource != null)
{
stringBuilder.AppendLine(" datasource: " + selectedExpression.ExpressionDatasource.GetResultSQL());
}
// write metadata information (if any)
if (selectedExpression.ExpressionField == null)
{
return;
}
var field = selectedExpression.ExpressionField;
stringBuilder.AppendLine(" field name: " + field.Name);
var s = Enum.GetName(typeof(DbType), field.FieldType);
stringBuilder.AppendLine(" field type: " + s);
}
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();
}
}
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.Select)
{
continue;
}
// separator
if (stringBuilder.Length > 0)
{
stringBuilder.AppendLine("<br />");
}
DumpSelectedExpressionInfo(stringBuilder, criteriaItem);
}
return(stringBuilder.ToString());
}
private void DumpSelectedExpressionInfo(StringBuilder stringBuilder, QueryColumnListItem selectedExpression)
{
// write full sql fragment of selected expression
stringBuilder.AppendLine(selectedExpression.ExpressionString + "<br />");
// write alias
if (!String.IsNullOrEmpty(selectedExpression.AliasString))
{
stringBuilder.AppendLine(" alias: " + selectedExpression.AliasString + "<br />");
}
// write datasource reference (if any)
if (selectedExpression.ExpressionDatasource != null)
{
stringBuilder.AppendLine(" datasource: " + selectedExpression.ExpressionDatasource.GetResultSQL() + "<br />");
}
// write metadata information (if any)
if (selectedExpression.ExpressionField != null)
{
MetadataField field = selectedExpression.ExpressionField;
stringBuilder.AppendLine(" field name: " + field.Name + "<br />");
string s = Enum.GetName(typeof(DbType), field.FieldType);
stringBuilder.AppendLine(" field type: " + s + "<br />");
}
}
private void AddWhereCondition(QueryColumnList columnList, QueryColumnListItem whereListItem, string condition)
{
bool whereFound = false;
// GetMaxConditionCount: returns the number of non-empty criteria columns in the grid.
for (int i = 0; i < columnList.GetMaxConditionCount(); i++)
{
// CollectCriteriaItemsWithWhereCondition:
// This function returns the list of conditions that were found in
// the i-th criteria column, applied to specific clause (WHERE or HAVING).
// Thus, this function collects all conditions joined with AND
// within one OR group (one grid column).
List<QueryColumnListItem> foundColumnItems = new List<QueryColumnListItem>();
CollectCriteriaItemsWithWhereCondition(columnList, i, foundColumnItems);
// if found some conditions in i-th column, append condition to i-th column
if (foundColumnItems.Count > 0)
{
whereListItem.ConditionStrings[i] = condition;
whereFound = true;
}
}
// if there are no cells with "where" conditions, add condition to new column
if (!whereFound)
{
whereListItem.ConditionStrings[columnList.GetMaxConditionCount()] = condition;
}
}
private void ClearConditionCells(UnionSubQuery unionSubQuery, QueryColumnListItem item)
{
for (int i = 0; i < unionSubQuery.QueryColumnList.GetMaxConditionCount(); i++)
{
item.ConditionStrings[i] = "";
}
}
public static void DumpSelectedExpressionsInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
// 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();
}
DumpSelectedExpressionInfo(stringBuilder, criteriaItem);
DumpSelectedExpressionsStatistics(stringBuilder, criteriaItem);
}
}
private void QBuilder_OnQueryColumnListItemChanged(QueryColumnList querycolumnlist,
QueryColumnListItem querycolumnlistitem, QueryColumnListItemProperty property, int conditionindex,
object newvalue)
{
if (CbQueryColumnListItemChanged.IsChecked != true)
{
return;
}
BoxLogEvents.Text = "QueryColumnListItemChanged property \"" + property + "\" changed" +
Environment.NewLine + BoxLogEvents.Text;
}
private void QBuilder_OnQueryColumnListItemRemoving(object sender, QueryColumnListItem item, ref bool abort)
{
if (CbQclRemoving.IsChecked != true)
{
return;
}
BoxLogEvents.Text = "QueryColumnListItemRemoving [" + item.ExpressionString + "]" +
Environment.NewLine + BoxLogEvents.Text;
var answer = MessageBox.Show(this,
"Do you want to delete the QueryColumnListItem [" + item.ExpressionString + "]",
"QueryColumnListItem removing", MessageBoxButton.YesNo);
abort = answer == MessageBoxResult.No;
}
private void QBuilder_QueryColumnListItemRemoving(object sender, QueryColumnListItem item, ref bool abort)
{
if (!cbQueryColumnListItemRemoving.Checked)
{
return;
}
var value = string.Format("QueryColumnListItemRemoving. Deleting the [{0}]", item.ExpressionString);
AddRowToReport(value);
var answer = MessageBox.Show(this,
@"Do you want to delete the QueryColumnListItem [" + item.ExpressionString + @"]",
@"QueryColumnListItemRemoving", MessageBoxButtons.YesNo);
abort = answer == DialogResult.No;
}
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 void LoadQueryWithSubQueryExpression()
{
UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();
// add data source to the query
DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.SalesOrderHeader", "Ord");
// add SQL expression column
QueryColumnListItem ci0 = unionSubQuery.QueryColumnList.AddExpression("GetDate()");
ci0.Selected = true;
ci0.AliasString = "CurrentDate";
// add database field columns
var ci1 = unionSubQuery.QueryColumnList.AddField(ds1, "SalesOrderID");
ci1.Selected = true;
var ci2 = unionSubQuery.QueryColumnList.AddField(ds1, "OrderDate");
ci2.Selected = true;
// add sub-query in expression
var ci3 = unionSubQuery.QueryColumnList.AddExpression("(SELECT *)");
ci3.AliasString = "MaxUnitPrice";
ci3.Selected = true;
var subQueriesAst = ci3.CollectSubqueryProxiesByRootNode(ci3.Expression);
var unionSubQueryExpression = subQueriesAst[0].SubQuery.FirstSelect();
var dsExpression = _query.AddObject(unionSubQueryExpression, "MyDB.MySchema.SalesOrderDetail", "OrdDet");
var ciExpression1 = unionSubQueryExpression.QueryColumnList.AddField(ds1, "SalesOrderID");
ciExpression1.ConditionStrings[0] = "= OrdDet.SalesOrderID";
var ciExpression = unionSubQueryExpression.QueryColumnList.AddField(dsExpression, "UnitPrice");
ciExpression.AggregateString = "Max";
ciExpression.Selected = true;
}
private void queryBuilder_CustomExpressionBuilder(QueryColumnListItem queryColumnListItem, int conditionIndex, string expression)
{
using (CustomExpressionEditor f = new CustomExpressionEditor())
{
f.textBox.Text = expression;
if (f.ShowDialog() == DialogResult.OK)
{
// Update the criteria list with new expression text.
if (conditionIndex > -1) // it's one of condition columns
{
queryColumnListItem.ConditionStrings[conditionIndex] = f.textBox.Text;
}
else // it's the Expression column
{
queryColumnListItem.ExpressionString = f.textBox.Text;
}
}
}
}
private void QBuilder_OnCustomExpressionBuilder(QueryColumnListItem querycolumnlistitem, int conditionIndex, string expression)
{
var msg = new MessageContainer(this)
{
Title = "Edit " + (conditionIndex != -1 ? "condition" : "expression"), TextContent = expression
};
if (msg.ShowDialog() != true)
{
return;
}
// Update the criteria list with new expression text.
if (conditionIndex > -1) // it's one of condition columns
{
querycolumnlistitem.ConditionStrings[conditionIndex] = msg.TextContent;
}
else // it's the Expression column
{
querycolumnlistitem.ExpressionString = msg.TextContent;
}
}
//
// GridCellValueChanging event allows to prevent the cell value changing or modify the new cell value.
// Note: Some columns hide/unhide dynamically but this does not affect the column index in the event parameters -
// it includes hidden columns.
// private void queryBuilder1_GridCellValueChanging(UnionSubQuery unionSubQuery, QueryColumnList criteriaList, QueryColumnListItem criteriaItem, int column, int row, object oldValue, ref object newValue, ref bool abort)
private void QBuilder_QueryColumnListItemChanging(QueryColumnList queryColumnList, QueryColumnListItem queryColumnListItem,
QueryColumnListItemProperty property, int conditionIndex, object oldValue, ref object newValue, ref bool abort)
{
if (!CbQueryColumnListItemChanging.Checked)
{
return;
}
AddRowToReport("QueryColumnListItemChanging. Changes column \"" + property + "\"");
if (property == QueryColumnListItemProperty.Expression) // Prevent changes in the Expression column.
{
abort = true;
}
else if (property == QueryColumnListItemProperty.Alias) // Alias column. Lets add the underscore char in the beginning of all aliases, for example.
{
if (newValue != null && newValue is string)
{
var s = (string)newValue;
if (s.Length > 0 && !s.StartsWith("_"))
{
s = "_" + s;
newValue = s;
}
}
}
}
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 bool IsQueryColumnListItemPresentInQuery(UnionSubQuery unionSubQuery, QueryColumnListItem item)
{
return unionSubQuery.QueryColumnList.IndexOf(item) != -1 && !String.IsNullOrEmpty(item.ExpressionString);
}
//
// GridCellValueChanging event allows to prevent the cell value changing or modify the new cell value.
// Note: Some columns hide/unhide dynamically but this does not affect the column index in the event parameters -
// it includes hidden columns.
//
private void QBuilder_OnQueryColumnListItemChanging(QueryColumnList querycolumnlist, QueryColumnListItem querycolumnlistitem,
QueryColumnListItemProperty property, int conditionindex, object oldvalue, ref object newValue, ref bool abort)
{
if (CbQueryColumnListItemChanging.IsChecked != true)
{
return;
}
BoxLogEvents.Text = "QueryColumnListItemChanging Changing the value for property \"" + property + "\"" +
Environment.NewLine + BoxLogEvents.Text;
if (property == QueryColumnListItemProperty.Expression) // Prevent changes in the Expression column.
{
abort = true;
return;
}
if (property != QueryColumnListItemProperty.Alias)
{
return;
}
var s = newValue as string;
if (s == null)
{
return;
}
if (s.Length <= 0 || s.StartsWith("_"))
{
return;
}
s = "_" + s;
newValue = s;
}
private void QBuilder_OnDataSourceFieldAdded(DataSource datasource, MetadataField field, QueryColumnListItem querycolumnlistitem, bool isFocused)
{
if (CbDataSourceFieldAdded.IsChecked != true)
{
return;
}
var fieldText = field == null ? datasource.NameInQuery + ".*" : field.Name;
BoxLogEvents.Text = $"DataSourceFieldAdded {fieldText}" +
Environment.NewLine + BoxLogEvents.Text;
}
private static string CountCharacter(QueryColumnListItem value)
{
return(string.IsNullOrEmpty(value.ExpressionString)
? "Empty"
: $" Length {value.ExpressionString.Length}");
}
private void QueryBuilder1_QueryColumnListItemChanging(QueryColumnList queryColumnList, QueryColumnListItem queryColumnListItem,
QueryColumnListItemProperty property, int conditionIndex, object oldValue, ref object newValue, ref bool abort)
{
}
private void QBuilder_OnQueryColumnListItemAdded(object sender, QueryColumnListItem item)
{
BoxLogEvents.Text = "QueryColumnListItemAdded [" + item.ExpressionString + "]" +
Environment.NewLine + BoxLogEvents.Text;
}
protected void btnApply_Click(object sender, EventArgs e)
{
var queryBuilder = QueryBuilderControl1.QueryBuilder;
queryBuilder.BeginUpdate();
try
{
// get the active SELECT
UnionSubQuery usq = queryBuilder.Query.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 (cbCustomers.Checked || cbCompanyName.Checked)
{
// 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 = usq.AddObject(CustomersName, CustomersAlias);
}
#region process CompanyName condition
if (cbCompanyName.Enabled && cbCompanyName.Checked && !String.IsNullOrEmpty(tbCompanyName.Text))
{
// if we have no previously added grid row for this condition, add it
if (CompanyName == null)
{
CompanyName = usq.QueryColumnList.AddField(Customers, CompanyNameFieldName.QualifiedNameWithoutQuotes);
// do not append it to the select list, use this row for conditions only
CompanyName.Select = false;
}
// write condition from edit box to all needed grid cells
AddWhereCondition(usq.QueryColumnList, CompanyName, tbCompanyName.Text);
}
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 (cbOrders.Checked || cbOrderDate.Checked)
{
// 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 = usq.AddObject(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.QualifiedNameWithoutQuotes;
if (Customers != null &&
usq.FromClause.FindLink(Orders, joinFieldNameStr, Customers, joinFieldNameStr) == null &&
usq.FromClause.FindLink(Customers, joinFieldNameStr, Orders, joinFieldNameStr) == null)
{
usq.AddLink(Customers, JoinFieldName, Orders, JoinFieldName);
}
#endregion
#region process OrderDate condition
if (cbOrderDate.Enabled && cbOrderDate.Checked && !String.IsNullOrEmpty(tbOrderDate.Text))
{
// if we have no previously added grid row for this condition, add it
if (OrderDate == null)
{
OrderDate = usq.QueryColumnList.AddField(Orders, OrderDateFieldName.QualifiedNameWithoutQuotes);
// do not append it to the select list, use this row for conditions only
OrderDate.Select = false;
}
// write condition from edit box to all needed grid cells
AddWhereCondition(usq.QueryColumnList, OrderDate, tbOrderDate.Text);
}
else
{
// remove prviously added grid row
if (OrderDate != null)
{
OrderDate.Dispose();
}
OrderDate = null;
}
#endregion
}
else
{
if (Orders != null)
{
Orders.Dispose();
Orders = null;
}
}
#endregion
}
finally
{
queryBuilder.EndUpdate();
}
tbSQL.Text = QueryBuilderControl1.PlainTextSQLBuilder.SQL;
}
private void QBuilder_DataSourceFieldAdded(DataSource dataSource, MetadataField field, QueryColumnListItem queryColumnListItem, bool focusCondition)
{
if (CbDataSourceFieldAdded.Checked != true)
{
return;
}
var text = field == null ? dataSource.NameInQuery + ".*" : field.Name;
AddRowToReport("DataSourceFieldAdded \"" + text + "\"");
}
protected void btnApply_Click(object sender, EventArgs e)
{
var queryBuilder1 = QueryBuilderStore.Get("QueryModification");
// 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 (cbCustomers.Checked)
{
// 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 (cbCompanyName.Enabled && cbCompanyName.Checked && !String.IsNullOrEmpty(tbCompanyName.Text))
{
// 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, tbCompanyName.Text);
}
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 (cbOrders.Checked)
{
// 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 (cbOrderDate.Enabled && cbOrderDate.Checked && !String.IsNullOrEmpty(tbOrderDate.Text))
{
// 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, tbOrderDate.Text);
}
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 QBuilder_QueryColumnListItemChanged(QueryColumnList queryColumnList, QueryColumnListItem queryColumnListItem,
QueryColumnListItemProperty property, int conditionIndex, object newValue)
{
if (CbQueryColumnListItemChanged.Checked != true)
{
return;
}
AddRowToReport("QueryColumnListItemChanged property \"" + property + "\" changed");
}
private static void DumpSelectedExpressionsStatistics(StringBuilder stringBuilder, QueryColumnListItem criteriaItem)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine("Statistic:");
StatisticsInfo.DumpQueryStatisticsInfo(stringBuilder, criteriaItem.QueryStatistics);
}
private void QBuilder_QueryColumnListItemAdded(object sender, QueryColumnListItem item)
{
var value = string.Format("QueryColumnListItemAdded. Added the [{0}]", item.ExpressionString);
AddRowToReport(value);
}
