Active Query Builder support area

How to create a simple query programmatically?

Last modified:

First of all, we are sorry for the lack of documentation. The fourth version will introduce a redesigned and well-documented API for this purpose. For now, you can look at the following sample to get an idea of how to do this. Also please review other articles in this section and feel free to ask us for details. 

// HOWTO: Create a query programmatically
public void CreateQuery()
	UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect();

	// add data source objects into the query
	// AddObjectWithFK() analyzes object relations and they will be automatically used in our query
	DataSource ds1 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Customers", "c");
	DataSource ds2 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Orders", "o");
	DataSource ds3 = _query.AddObject(unionSubQuery, "MyDB.MySchema.Resellers", "r");

	// manually create relation between 'Resellers' and 'Orders'
	// this relation doesn't exist in metadata structure we filled, but we need it for our query
	Link lnk = _query.AddLink(ds3, "ResellerID", ds2, "ResellerID");
	lnk.AllFromLeft = true; // create left outer join

	// create first output column (expression)
	QueryColumnListItem ci1 = unionSubQuery.QueryColumnList.AddExpression("GetDate()");
	ci1.Selected = true;
	ci1.AliasString = "d";

	// create simple WHERE condition
	QueryColumnListItem ci2 = unionSubQuery.QueryColumnList.AddExpression("CustomerID");
	ci2.Selected = false;
	ci2.ConditionStrings[0] = "> 0";

	// create second output column
	QueryColumnListItem ci3 = unionSubQuery.QueryColumnList.AddField(ds1, "CustomerName");
	ci3.Selected = true;

	// create third output column with condition
	QueryColumnListItem ci4 = unionSubQuery.QueryColumnList.AddField(ds2, "OrderID");
	ci4.Selected = true;
	ci4.ConditionStrings[0] = "> 0";

	// order by...
	ci3.SortType = ItemSortType.Desc;
	ci3.SortOrder = 0;

	// at this stage you can get simple unformatted query text...
	//textBox1.Text = _query.SQL;

	// ... or format the query text with SQL formatter
	SQLFormattingOptions formattingOptions = new SQLFormattingOptions();
	formattingOptions.KeywordFormat = KeywordFormat.UpperCase;
	string sql = FormattedSQLBuilder.GetSQL(_query.QueryRoot, formattingOptions);

	// put the result SQL query text to the text box
	textBox1.Text = sql;

Is this article helpful for you?