Active Query Builder for .NET

Product summary
Latest version:
...
History of changes
Price from:
$450
All purchase options
Download free trial

Active Query Builder ASP.NET Edition Live Demo

Visual SQL Query Builder for classic ASP.NET and MVC environemnts

Active Query Builder ASP.NET Edition provides a native and easy way to embed visual query building functionality into your ASP.NET and MVC projects. Active Query Builder is the first and only visual query builder for ASP.NET platform which brings full power of SQL language to the end-users by letting them build complex SQL queries with unions and sub-queries visually.

The client side of Active Query Builder ASP.NET Edition is written in pure HTML with CSS to separate the web page layout from the content for a quicker, efficient, and easier redesign. The query parsing and SQL text generation is performed on the server side.

Read more about the Features of Active Query Builder ASP.NET Edition.

To build a query visually, drag some database objects from the Database Schema Tree to the Design Pane. Joins between objects will be created automatically according to the foreign keys defined in the database, but you can create them either by dragging a field of one database object to a field of another object. Check the fields that you want to add to the query and define their properties in the Query Column List below the Design Pane.

Active Query Builder is a two-way visual SQL query builder. It allows you to combine visual query building with direct SQL text editing. To load new or to change the existing SQL query text, you can modify it in the text editor at the bottom, and apply changes to the query builder by clicking the Refresh button.

The easiest way to learn how to build specific statement in Active Query Builder is to write it once in SQL text and see how it will be represented visually. However, you can read the User's guide to learn the visual query building process in detail.

view source code

This handler is defined for the QueryBuilderControl.Init event of this demo project to initialize the component. Please refer to the complete Quick Start Guide for detailed instructions on how to integrate Acitve Query Builder ASP.NET Edition to your project.

public void QueryBuilderControl1_Init(object sender, EventArgs e)
{
	// Get instance of the QueryBuilder
	QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;

	// Initialize syntax provider
	if (currentSyntaxProvider == null) currentSyntaxProvider = new MSSQLSyntaxProvider();
	queryBuilder.SyntaxProvider = currentSyntaxProvider;

    // Deny automatic metadata loading from the metadata provider  
	queryBuilder.OfflineMode = true;
	
    // Load MetaData from XML document 
	try
	{
		queryBuilder.MetadataContainer.ImportFromXML(Page.Server.MapPath("/AdventureWorks.xml"));
	}
	catch (Exception ex)
	{
		const string message = "Can't load metadata from XML.";
		Logger.Error(message, ex);
		StatusBar1.Message.Error(message + " Check log.txt for details.");
	}
}

Active Query Builder allows for substituting unintelligible names of database objects and fields for user-friendly aliases ("Alternate Names"). Active Query Builder hides real database object names completely: the user can even combine visual query building with direct SQL text editing using alternate names.

In this demo we took a snapshot of the database with the unintelligible object and field names and defined alternate names for them, so the database schema and the SQL queries become easily understandable. All aliased names that were used in the query will be substituted back for real names in the resultant SQL query text to execute a query against database server. The difference between the user's and server's query text demonstrates the operating principle of this feature.

view source code

Alternate names can be defined programmatically or using the metadata XML files. This code is a piece of the metadata XML file that is used in this demo project. Alternate names are defined in the "alt_name" tags, they override real names defined in the "name" tag.

    <table>
		<database>
			<item name="SAMPLE" case_sens="0"/>
		</database>
		<schema name="ADMINISTRATOR" case_sens="1"/>
		<name name="ACT" case_sens="1"/>
		<alt_name>Activities</alt_name>
		<system>0</system>
		<visible>1</visible>
		<descr>Activity Table</descr>
		<long_descr>The activity table describes each activity.</long_descr>
		<fields>
			<field>
				<name>
					<item name="ACTNO" case_sens="0"/>
				</name>
				<type>Int16</type>
				<size>2</size>
				<precision>5</precision>
				<primary_key>1</primary_key>
				<nullable>0</nullable>
				<readonly>1</readonly>
				<alt_name>Activity ID</alt_name>
				<descr>Activity number</descr>
			</field>
			<field>
				<name>
					<item name="ACTKWD" case_sens="0"/>
				</name>
				<type>String</type>
				<size>6</size>
				<precision>6</precision>
				<nullable>0</nullable>
				<readonly>1</readonly>
				<alt_name>Keyword</alt_name>
				<descr>Keyword for activity</descr>
			</field>
			<field>
				<name>
					<item name="ACTDESC" case_sens="0"/>
				</name>
				<type>String</type>
				<size>20</size>
				<precision>20</precision>
				<nullable>0</nullable>
				<readonly>1</readonly>
				<alt_name>Description</alt_name>
				<descr>Description of activity</descr>
			</field>
		</fields>
		<relations>
		</relations>
	</table>

Active Query Builder allows to inject Virtual Objects and Fields to the metadata taken from the database. Being actually ordinary SELECT queries, virtual objects can eliminate the need to create physical views in the database. Virtual fields are actually complex expressions or correlated sub-queries that are frequently used by the end-users.

Virtual objects and fields look like ordinary objects and fields in the Database Schema Tree. They are added as sub-queries in the resultant SQL query text, but this process goes seamlessly for the end-user. The user will see ordinary objects and fields in the query text, and it can combine visual query building with direct SQL text editing using virtual entities.

In this demo, we've added virtual fields to the Orders table and two virtual objects called OrdersOnTop and MyOrders. All virtual entity names that were used in the query will be substituted back for sub-queries and expressions in the resultant SQL query text to execute a query against database server. The difference between the user's and server's query text demonstrates the operating principle of this feature.

view source code

Virtual objects and fields can be defined programmatically or using the metadata XML files. This sample C# code demonstrates programmatic addition of virtual objects and fields. To define virtual object or field in the metadata XML file, add the "expression" tag to the database objects or field definition.

MetadataObject o;
MetadataField f;

// Virtual fields for real object
// ===========================================================================
o = queryBuilder.MetadataContainer.FindObjectByFullName("Northwind.dbo.Orders");

// first test field - SQL expression
f = o.Fields.Add();
f.FieldType = System.Data.DbType.Int32;
f.NameStr = "OrderId_Plus_One";
f.Expression = "Orders.OrderId + 1";
f.FieldType = System.Data.DbType.Int32;

// second test field - correlated sub-query
f = o.Fields.Add();
f.NameStr = "CustomerName_Lookup";
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = Orders.CustomerId)";
f.FieldType = System.Data.DbType.String;

// Virtual object (table) with virtual fields
// ===========================================================================

o = queryBuilder.MetadataContainer.AddTable("Northwind.._OrdersOnTop_", false);
o.Expression = "Northwind.dbo.Orders";
o.LoadFields();

// first test field - SQL expression
f = o.Fields.Add();
f.NameStr = "OrderId_Plus_One";
f.Expression = "_OrdersOnTop_.OrderId + 1";
f.FieldType = System.Data.DbType.Int32;

// second test field - correlated sub-query
f = o.Fields.Add();
f.NameStr = "CustomerName_Lookup";
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = _OrdersOnTop_.CustomerId)";
f.FieldType = System.Data.DbType.String;

// Virtual object (sub-query) with virtual fields
// ===========================================================================

o = queryBuilder.MetadataContainer.AddTable("Northwind.dbo._MyOrders_", false);
o.Expression = "(select OrderId, CustomerId, OrderDate from Orders) as Ord";
o.LoadFields();

// first test field - SQL expression
f = o.Fields.Add();
f.NameStr = "OrderId_Plus_One";
f.Expression = "_MyOrders_.OrderId + 1";
f.FieldType = System.Data.DbType.Int32;

// second test field - correlated sub-query
f = o.Fields.Add();
f.NameStr = "CustomerName_Lookup";
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = _MyOrders_.CustomerId)";
f.FieldType = System.Data.DbType.String;

Active Query Builder allows to analyze SQL queries of any complexity. The whole SQL sub-queries structure is decomposed into 3 levels that are represented by the respective entities: Query - SubQuery - UnionSubQuery.

UnionSubQuery is an object representing single SELECT statement. Using it you may get access to its parts: data sources, links, output expressions, grouping, sorting and criteria.

The group of union sub-queries that are joined using one of the operators (UNION, EXCEPT, INTERSECT, MINUS) comprise a SubQuery. SubQuery object may represent a main query, a common table expression, a derived table or a sub-query used in the expression ("WHERE Field IN (SELECT ...)").

In the process of building a query it is often not known what data the user will get as a result therefore it is more convenient to modify the query at the stage of browsing its results. But going back to the visual query builder means to distract from the data analysis. So we took another step towards the user, and created a visual component that allows you to set filters visually in a convenient tree-like form. CriteriaBuilder utilizes API of the non-visual QueryTransformer component that allows for adding filters, changing sorting and limits and applying aggregations (to get totals).

Active Query Builder allows for building a query without the Design Pane. In this way the user will be limited in defining properties of links and datasources of the query, but for those users who are not familiar with database relationship diagrams this mode may seem to be less complicated.

To build a query one has to drag database object fields to the Query Column List and define the query column's properties in the grid. The appropriate database objects will be added (and removed) to the query automatically, even if the user adds the objects that are not directly linked to each other.

For example, by adding fields of Customers and Products tables to the query in this demo, the Orders and Order Details tables will be added to the query automatically.

view source code

It is only needed to set two properties in the QueryBuilderControl.Init event handler, and remove the "AQB:Canvas" control from the web page to activate this mode.


queryBuilder.LinkOptions.DeleteUnusedObjects = true;
queryBuilder.LinkOptions.AddLinkedObjects = true;

Active Query Builder ASP.NET Edition meets WAI-ARIA requirements to allow for using by people with disabilities.

The following demo is made in a high-contrast theme with keyboard navigation turned on. Role and ARIA-LABEL attributes are defined for the controls to allow narrative speaking.

Use the "1" to "5" keys to switch between main parts of the component: database schema tree, sub-query navigation bar, design pane, query columns grid and query text editor. Use Tab and Shift-Tab keys to navigate over the controls. Use Enter to step into the sub-items of complex controls: datasource field lists, and cells of the query columns grid.

Please wait....