Modification of sub-query text separately from the entire query
Last modified:
Active Query Builder lets modify the text of sub-query independently of the main query.
You can choose to edit the text of a single SELECT statement ("Union Sub-query") or edit the text of sub-query which will contain unions if they are present in it. In other words, if a subquery contains unions, the user can separately edit each union sub-query, or he can modify the whole set of union sub-queries.
The sample code of getting the text of the current sub-query or union sub-query:
private void ApplyText()
{
var sqlFormattingOptions = queryBuilder1.SQLFormattingOptions;
switch (_mode)
{
case ModeEditor.Entire:
textBox1.Text = queryBuilder1.FormattedSQL;
break;
case ModeEditor.SubQuery:
if (queryBuilder1.ActiveUnionSubQuery == null)
break;
var subQuery = queryBuilder1.ActiveUnionSubQuery.ParentSubQuery;
textBox1.Text = FormattedSQLBuilder.GetSQL(subQuery, sqlFormattingOptions);
break;
case ModeEditor.Expression:
if (queryBuilder1.ActiveUnionSubQuery == null)
break;
var unionSubQuery = queryBuilder1.ActiveUnionSubQuery;
textBox1.Text = FormattedSQLBuilder.GetSQL(unionSubQuery, sqlFormattingOptions);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
The code sample of setting the sub-query text back to Active Query Builder:
switch (_mode)
{
case ModeEditor.Entire:
queryBuilder1.SQL = text;
break;
case ModeEditor.SubQuery:
var subQuery = queryBuilder1.ActiveUnionSubQuery.ParentSubQuery;
subQuery.SQL = text;
break;
case ModeEditor.Expression:
var unionSubQuery = queryBuilder1.ActiveUnionSubQuery;
unionSubQuery.SQL = text;
break;
default:
throw new ArgumentOutOfRangeException();
}
The fine point of editing sub-query text is that user can switch to another sub-query assuming that the changes made to the sub-query text will be applied to the query prior to switching. But the problem is that user can make a typo so the changed text can not be applied to the query. If the programmer don't want to let user loose those changes, he must handle the ActiveUnionSubQueryChanging and ActiveUnionSubQueryChanged events. The first one of them lets deny switching, so the programmer will be able to point to the user error and ask him what he wants: to correct the error or to loose those changes.
private void QueryBuilder1_ActiveUnionSubQueryChanging(object sender, ActiveQueryBuilder.View.SubQueryChangingEventArgs e)
{
string sql = textBox1.Text.Trim();
if (string.IsNullOrEmpty(sql))
{
return;
}
try
{
queryBuilder1.SQLContext.ParseSelect(sql);
}
catch
{
e.Abort = true;
}
}
The UnionSubQuery.SQL and SubQuery.SQL properties can be used to read and write sub-query SQL text.
When working in the sub-query editing mode, the programmer must assign the user edited query text to the PlainTextSQLBuilder.SQL property as it's always mapped to the active sub-query. He must use the PlainTextSQLBuilder.SQLUpdated event to update the text editor as this event is fired not only on changing of the query in the Query Builder but also on switching of the active sub-query.
Another option is to let the PlainTextSQLBuilder retrieve the formatted text of the currently active sub-query. In this mode, the user sees and edits the currently active sub-query as an independent query. He can easily get back from this mode when needed.
There are two variants of sub-query editing mode: editing single union sub-queries or editing sets of sub-queries with unions. In other words, if a subquery contains unions, the user can separately edit each union sub-query, or he can modify the whole set of union sub-queries.
Below are the possible values of the PlainTextSQLBuilder.TargetQueryPart property that turns this mode on are the following:
- Query (default) – instructs to generate SQL text for the whole query;
- SubQuery – instructs to generate SQL text for the currently active sub-query with unions;
- UnionSubQuery – instructs to generate SQL text for the currently active union sub-query.
The "SubQuery Text Editing" demo project illustrates this functionality.