How to read the result SQL query text and load the query back?

Last modified: 2 years ago

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 prime property of the QueryBuilder component is the 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

in ActiveX Edition

Use the ActiveQueryBuilderX.FormattedSQL property.

in .NET 2.x and 1.x, Java and VCL Editions

Use the PlainTextSQLBuilder.SQL property. To work with PlainTextSQLBuilder, you must 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.

in Active Query Builder for .NET v.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:

// copy the current formatting options and redefine just some of them 
using (var options = new SQLFormattingOptions(queryBuilder1.SQLFormattingOptions)
{
   // initialize some props here
   UseAltNames = false,
})
{
   sqlTextEditor1.Text = queryBuilder1.GetSQL(options);
}

// another way
sqlTextEditor1.Text = FormattedSQLBuilder.GetSQL(sqlQuery1.QueryRoot, sqlFormattingOptions1);

in ASP.NET Edition

Refer to the following articles for details:

Saving and Loading SQL query text between work sessions

- with the layout of objects within the Design Pane

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

- with text formatting

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

- combining these techniques

Both properties work fine save both SQL text formatting and query visual layout between work sessions. The sequence of reading them to save a query doesn't matter, but you should write the QueryBuilder.LayoutSQL property first and then write the formatted SQL query text to the QueryBuilder.SQL and SqlTextEditor.Text properties.

Synchronizing 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 are 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, it compares the new AST with the old one, and if they are equal, the substitution of the old tree for the new one doesn't occur. Therefore, multiple assignments of the same syntactically correct query won't lead to the multiple firing of the SQLUpdated event, but the multiple assignments of the incorrect query will give rise to the multiple firing of the SQLError event.

Active Query Builder support area