We are glad to announce the new version of the Active Query Builder for .NET!

With the possibility of decomposition of the user interface, which has been added in the version 3.0, it is possible to build a composite user interface. For example, you can separate the database schema browsing UI from the SQL query building UI. Or you can combine the SQL text editor with the visual query building UI, giving the user the ability to edit the text of each sub-query apart from the entire query. This release is dedicated to the rethinking of the old features in a new way.

1. Improvements in the User Queries API.
   Previously, to edit the text of the saved user query, the user had to abandon the currently edited query, and the programmer could not keep track of this transition.
   • Now when the user selects the query editing command, the UserQueriesView.EditUserQuery event is triggered, in which the developer can save the current query before opening the new one, or he can open the user query in a new window, the way it's done in the updated Full-featured demo project. Also, the events are now fired on deleting, renaming and moving user queries between the folders. You can review all API changes in the release notes article.
   • The new helper class Core.UserQueries that comprises static methods has been added to perform various manipulations with user queries, such as adding, saving, moving, renaming them. It also has the means to save and load user queries serialized in XML format.
   • The updated Professional Full-Featured demo project illustrates the new functionality.
     
     
2. Editing Sub-query text apart from the entire query is possible now.
   The component's API has allowed editing the text of a subquery long ago. The problem is that the text of the query changed by the user may have syntax errors, and the component lets switch to another subquery. Active Query Builder cannot preserve the erroneous text, as it has to be parsed and translated into an object representation. In order not to lose the user changes when switching between subqueries, the component must be able to block this switch to inform the user of the error and offer ways out of the situation.
   • The new ActiveUnionSubQueryChanging event has been added to the QueryView and QueryBuilder components. The programmer can deny switching between sub-queries in this event handler by setting the e.Abort parameter to true.
   • The new SubQueryBreadcrumbsBar control has been made to display path from the main query to the currently active sub-query. It helps quickly switch to parent sub-queries.
   • The new Sub-Query Text Editing demo project has been added to illustrate the new functionality.
     
     
3. Miscellaneous improvements.
   • The data browsing UI is improved in the Full-featured demo project. It now uses the Query Transformer API to modify SQL queries.
   • The new MetadataLoadingOptions.AllowRecursiveVirtualObjects property allows specifying the mode of working with virtual objects. By default, referring to other virtual objects in the text of a virtual object is allowed. Such reference lets build a chain of objects to break complex queries in a series of simple ones. However, in this mode, you can't use the same-named physical object in a virtual object because of the emerging recursion. Setting this property to false prohibits creation chains of virtual objects, but allows to create virtual objects that substitute physical database objects.
   • Query Statistics: The UsedDatabaseObjects and UsedDatabaseObjectFields collections now include invalid objects and fields being used in the query. They differ from valid objects by the absence of MetadataObject (for objects) and MetadataField (for fields) references. Such inclusion lets detect incorrect objects and fields in the query. 
   • Usability of the Expression Editor is improved now with the new dockable panels.
   • SQL Text Editor with syntax highlighting is used now to edit joining expression in the Properties bar and dialog.
   • Now it's possible to select multiple rows and drag them to reorder columns in the SELECT list.
   • WinForms DevExpress UI: Query Columns Grid has been remade with DevExpress controls.
   • The new SQLite metadata provider has been added.
   • The Query Structure demo project has been refactored.
   • Removal of unused objects from the query when the QueryBuilder.BehaviorOptions.DeleteUnusedObjects property is turned to True has been fixed.
4. SQL syntax improvements.
   • IBM DB2: The ListAgg function is supported now.
   • PostgreSQL: Support of materialized view, TOAST tables and foreign tables has been made.
   • Oracle: Support of the "AT TIME ZONE" clause has been added.
   • Firebird: Column name definition after alias in the FROM clause is supported now.
5. API changes and improvements.
   The new ActiveUnionSubQueryChanging event has been added to the QueryBuilder and QueryView controls:

   // Fired when active union sub-query is about to be changed. You can cancel changing by setting the e.Abort parameter to True.
   SubQueryChangingEventHandler ActiveUnionSubQueryChanging 
   
   SubQueryChangingEventHandler(object sender, SubQueryChangingEventArgs e)
       SubQueryChangingEventArgs : EventArgs
       {
           public object Tag
           public bool Abort
           public UnionSubQuery OldSubQuery
           public UnionSubQuery NewSubQuery
       }

   The new AllowRecursiveVirtualObjects property has been added to the MetadataLoadingOptions group:

   // Lets use virtual object names in SQL expressions of other virtual entities.
   bool AllowRecursiveVirtualObjects

   The new UserQueries helper class with low-level static methods to handle user queries has been added to the Core assembly:

   // Lets determine if an item is a folder or not.
   bool IsFolder (MetadataStructureItem item) 
   
   // Adds new user query.
   MetadataStructureItem AddUserQuery (MetadataContainer metadataContainer, MetadataStructureItem parentFolder, string name, string query, int imageIndex = -1, string layout = null) 
   // Adds new folder.
   MetadataStructureItem AddFolder (MetadataStructureItem parentFolder, string name, int imageIndex = -1) 
   // Replaces the user query content with the new SQL string and layout.
   void SaveUserQuery (MetadataContainer metadataContainer, MetadataStructureItem userQuery, string query, string layout = null)
   
   // Moves the query to the specified folder.
   MetadataStructureItem MoveUserQuery (MetadataStructureItem userQuery, MetadataStructureItem destinationFolder) 
   // Moves the folder into another folder as a subfolder.
   MetadataStructureItem MoveFolder (MetadataStructureItem folder, MetadataStructureItem destinationFolder) 
   
   // Sets the given name for the query.
   void RenameUserQuery (MetadataContainer metadataContainer, MetadataStructureItem userQuery, string newName)
   // Sets the given name for the folder.
   void RenameFolder (MetadataStructureItem folder, string newName) 
   // Deletes the query.
   MetadataObject RemoveUserQuery (MetadataContainer metadataContainer, MetadataStructureItem userQuery) 
   // Deletes the folder and all of its queries and subfolders.
   void RemoveFolder (MetadataContainer metadataContainer, MetadataStructureItem folder) 
   
   // Saves the queries and their tree structure to file identified by the fileName parameter.
   void ExportToXML (MetadataContainer metadataContainer, MetadataStructure metadataStructure, string fileName) 
   // Saves the queries and their tree structure to the stream.
   void ExportToXML (MetadataContainer metadataContainer, MetadataStructure metadataStructure, stream stream) 
   
   // Loads the queries and their tree structure from file identified by the fileName parameter.
   void ImportFromXML (MetadataContainer metadataContainer, MetadataStructure metadataStructure, string fileName) 
   // Loads the queries and their tree structure from the stream.
   void ImportFromXML (MetadataContainer metadataContainer, MetadataStructure metadataStructure, stream stream)

   DatabaseSchemaView:

   // Instructs to expand and scroll the view to display the given item
   void BringIntoItem (MetadataStructureItem item) 
   // Turns the view into edit mode to change caption of the given item.
   void EditItem (MetadataStructureItem item)  
   // Returns the tree view control node of the given metadata structure item.
   TreeViewItem GetItemNode (MetadataStructureItem item) 
   
   // Fired when the item caption is about to be edited. Editing can be denied by setting the args.Abort parameter to True.
   TreeViewStructureItemAcknowledgement BeforeItemCaptionEdit 
   // Fired when editing of the item caption is about to be finished. Editing can be continued by setting the args.Abort parameter to True.
   TreeViewStructureItemAcknowledgement AfterItemCaptionEdit 
   // Fired when the item caption has been changed.
   TreeViewStructureItemNotification ItemCaptionChanged 
   
   // Fired when selected item has been changed.
   TreeViewStructureItemNotification SelectedItemChanged 
   // Fired when the item context menu is about to be displayed.
   TreeViewStructureItemContextMenuValidation ValidateItemContextMenu

   UserQueriesView:

   // Holds the caption string in the view header.
   string Title 
   // Indicates the currently selected item in the view.
   MetadataStructureItem SelectedItem 
   
   // Gets the reference to the MetadataStructure object, which keeps the tree structure of items in the view.
   MetadataStructure MetadataStructure 
   // Gets or sets the reference to the query view control, to which a query will be added on double clicking on it in this view.
   QueryView QueryView 
   
   // Determines whether the user can rename queries and folders in the view.
   bool AllowEditUserQueries 
   // Determines the visibility of buttons to save the currently edited query in the query view as a new or existing query.
   bool DisplaySaveButtons
   
   // Expands the item and scrolls the view to make it visible.
   void BringIntoItem ( MetadataStructureItem item) 
   
   // Fired when the user selects the Edit Query command from the toolbar or the context menu.
   event TreeViewStructureItemNotification EditUserQuery 
   
   // Fired when the user has renamed a query or a folder in the view.
   event TreeViewStructureItemNotification UserQueryItemRenamed 
   
   // Fired when the user is about to delete an item from the view. You can cancel removal by setting the args.Abort parameter to True.
   event TreeViewStructureAcknowledgement UserQueryItemRemoving 
   
   // Fired when the user has removed a query or folder in the view.
   event TreeViewStructureItemNotification UserQueryItemRemoved 
   
   // Fired when the user drops an item (query or folder) to a folder to reorganize the tree. Action can be canceled by setting the args.Abort parameter to True.
   event TreeViewStructureDragDropAcknowledgement UserQueryItemDragDrop 
   
   // Fired when the view is about to show the error message. By handling this event, you take responsibility for displaying error messages.
   event ErrorNotificationEventHandler ErrorMessage 
   
   // Fired when the context menu is about to be shown for the item. You can change this menu by altering the referred menu object.
   event TreeViewStructureItemContextMenuValidation ValidateItemContextMenu 
   
   // Saves the queries and their tree structure to file identified by the fileName parameter.
   void ExportToXML (string fileName) 
   // Saves the queries and their tree structure to the stream.
   void ExportToXML (stream stream) 
   // Loads the queries and their tree structure from the file identified by the fileName parameter.
   void ImportFromXML (string fileName) 
   // Loads the queries and their tree structure from the stream.
   void ImportFromXML (stream stream)

   Event types:

   ErrorNotificationEventHandler (object sender, Exception exception)
   
   TreeViewStructureItemNotification (object sender, MetadataStructureItem item)
   
   TreeViewStructureItemContextMenuValidation (object sender, MetadataStructureItem item, ICustomContextMenu& menu)
   
   TreeViewStructureItemAcknowledgement (object sender, EditItemCaptionArgs args)
       public class EditItemCaptionArgs: EventArgs
       {
           public MetadataStructureItem Item
           public string OldCaption
           public string NewCaption
           public bool Abort
       }
   
   TreeViewStructureDragDropAcknowledgement (object sender, TreeViewStructureDragDropArgs args);
       public class TreeViewStructureDragDropArgs : EventArgs
       {
           public MetadataStructureItem DraggedItem
           public MetadataStructureItem TargetItem
           public bool Abort
       }