Getting unformatted SQL Text

Active Query Builder is a component intended to build SQL queries as well as to parse them and represent them visually. Thus, the main property is the QueryBuilder.SQL property. You can read it to get unformatted SQL query text for executing against a database server, and you can write to it to parse a query and represent it visually.

Getting formatted SQL Text

- ActiveX Edition
Use the ActiveQueryBuilderX.FormattedSQL property.
- .NET 2.x and 1.x, Java and VCL Editions
Use the PlainTextSQLBuilder.SQL property. To work with PlainTextSQLBuilder you need assign an instance of the QueryBuilder component to the PlainTextSQLBuilder.QueryBuilder property. After that you'll be able to get the formatted query text from the PlainTextSQLBuilder.SQL property.
- Active Query Builder for .NET 3
Using the QueryBuilder visual component, you can read the query text from the QueryBuilder.FormattedSQL property and alter SQL formatting options using the QueryBuilder.SQLFormattingOptions group of properties.
Working with the separated user interface controls mode or in the non-visual mode, you must create an instance of the short-living FormattedSQLBuilder object and pass to it the root of your SQL query and an instance of the SQLFormattingOptions object that stores the options:
sqlTextEditor1.Text = FormattedSQLBuilder.GetSQL(sqlQuery1.QueryRoot, new SQLFormattingOptions());

Saving and Loading SQL query text between work sessions

- with layout of objects within the Design Pane

The QueryBuilder.LayoutSQL property allows to save and to load the layout of objects within the Design Pane among with the query text, so the component's visual state could be saved between work sessions.

- with text formatting

To save the text formatting in the SQL text editor you should read and write the SqlTextEditor.Text property.

- combining these techniques

Both properties should be used to save both SQL text formatting and query visual layout between work sessions. The sequence of reading them to save a query is not important, but you should write the QueryBuilder.LayoutSQL property first and then write the formatted SQL query text to both the QueryBuilder.SQL and the SqlTextEditor.Text properties.

ASP.NET Edition

Getting the query text and layout with the ASP.NET you should use the following code:

// if QueryBuilderControl.MultipleQueriesPerSession = false (default)
SessionStoreItem item = SessionStore.Current;

// if QueryBuilderControl.MultipleQueriesPerSession = true
SessionStoreItem item = SessionStore.GetCurrent("SessionId");

string sql = item.ClientSQL;
string layout = item.Layout;

Syncronizing Active Query Builder with the SQL Text Editor

Use the QueryBuilder.SQLUpdated event to get new SQL query text each time when the query is updated in Active Query Builder visually or programmatically. To disable the firing of this event during batch programmatic changes, use the BeginUpdate and EndUpdate methods.

Note: The SQLUpdated event won't occur if you try to load the query text identical to the query that is loaded into the component already.

On assigning the query text, Active Query Builder tries to parse it. During the parsing process the component builds the "abstract syntax tree" (AST) from the query text (all elements that irrelevant for the parser, such as spaces, line breaks, and even comments are ignored). In fact, AST is a set of meaningful tokens. After that, the new AST is compared with the old one, and if they are equal, the substitution of the old tree for the new one doesn't occur. Therefore, multiple assignment of the same syntactically correct query won't lead to the multiple firing of the SQLUpdated event, but the multiple assignment of the incorrect query will lead to the multiple firing of the SQLError event.

Is this article helpful for you?