SelectQuery Class
A set of columns (from a single table or multiple joined tables) that forms a SELECT statement when executing a query.
Namespace: DevExpress.DataAccess.Sql
Assembly: DevExpress.DataAccess.v21.2.dll
NuGet Packages: DevExpress.DataAccess, DevExpress.Win.Design
Declaration
Remarks
Note
Use the SelectQueryFluentBuilder class instead, to use a fluent syntax when constructing a SELECT statement.
In the following example, a query selects and orders records from a single data table.
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using System.ComponentModel;
// ...
private SqlDataSource BindToData() {
// Create a data source with the required connection parameters.
var connectionParameters = new Access97ConnectionParameters("../../Data/nwind.mdb", "", "");
var ds = new SqlDataSource(connectionParameters);
// Return a list of categories sorted by the number of products in each category.
var selectQuery = new SelectQuery("Products");
var products = selectQuery.AddTable("Products");
selectQuery.SelectColumn(products, "CategoryID");
selectQuery.GroupBy(products, "CategoryID");
selectQuery.SortBy(products, "ProductID", AggregationType.Count, ListSortDirection.Descending);
selectQuery.FilterString = "[CategoryID] != 8";
// Add the query to the collection and return the data source
ds.Queries.Add(selectQuery);
ds.Fill();
return ds;
}
In the following example, a query uses an inner join to select columns from separate data tables sharing a common column key.
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
private SqlDataSource BindToData(){
// Create a data source with the required connection parameters.
var connectionParameters = new Access97ConnectionParameters("../../Data/nwind.mdb", "", "");
var ds = new SqlDataSource(connectionParameters);
// Join the Categories and Products table by the CategoryID column.
// Return the list of categories and the number of products in each category.
// Sort the categories by the number of products in them.
// The included categories must contain a specific number of products.
var selectQuery = new SelectQuery("Categories");
var categories = selectQuery.AddTable("Categories");
selectQuery.SelectColumn(categories, "CategoryName");
selectQuery.GroupBy(categories, "CategoryName");
var products = selectQuery.AddTable("Products");
selectQuery.AddRelation(categories, products, "CategoryID");
selectQuery.SelectColumn(products, "ProductName", AggregationType.Count, "ProductCount");
selectQuery.SortBy(products, "ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending);
selectQuery.GroupFilterString = "[ProductCount] > 7";
// Add the query to the collection and return the data source.
ds.Queries.Add(selectQuery);
ds.Fill();
return ds;
}
To execute all valid queries specified for a data source and obtain the result set, call the SqlDataSource.Fill method. To obtain the data source schema, call the SqlDataSource.RebuildResultSchema method.
You can register a custom aggregate function and use it in a SELECT query expression. For sample code, see the following example online: How to use a custom function in a query expression.