corelibraries-devexpress-dot-dataaccess-dot-sql-65bea6fc.md
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.v25.2.dll
NuGet Package : DevExpress.DataAccess
public sealed class SelectQuery :
SqlQuery
Public NotInheritable Class SelectQuery
Inherits SqlQuery
The following members return SelectQuery objects:
| Library | Related API Members |
|---|---|
| Cross-Platform Class Library | QueryBuilderInput.ResultQuery |
| SelectQueryFluentBuilder.Build(String) | |
| ASP.NET MVC Extensions | QueryBuilderSaveCallbackResult.ResultQuery |
| .NET Reporting Tools | SaveQueryEventArgs.ResultQuery |
The SelectQueryFluentBuilder class allows you to use a fluent syntax when constructing a SELECT statement.
A SelectQueryFluentBuilder instance is created by calling the static SelectQueryFluentBuilder.AddTable method.
The chain of methods must end with calling the SelectQueryFluentBuilder.Build method accepting the query name as a parameter.
In the following example, a query selects and orders records from a single data table.
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
// ...
private SqlDataSource BindToData() {
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters =
new Access97ConnectionParameters("../../Data/nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Return a list of categories sorted by the number of products in each category.
// The chain ends with calling the Build method accepting the query name as a parameter.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Products")
.SelectColumn("CategoryID")
.GroupBy("CategoryID")
.SortBy("ProductID", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending)
.Filter("[CategoryID] != 8")
.Build("MyQuery");
// Add the query to the collection and return the data source.
ds.Queries.Add(query);
return ds;
}
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
' ...
Private Function BindToData() As SqlDataSource
' Create a data source with the required connection parameters.
Dim connectionParameters As New Access97ConnectionParameters("../../Data/nwind.mdb", "", "")
Dim ds As New SqlDataSource(connectionParameters)
' Return a list of categories sorted by the number of products in each category.
' The chain ends with calling the Build method accepting the query name as a parameter.
Dim query As SelectQuery = SelectQueryFluentBuilder.
AddTable("Products").
SelectColumn("CategoryID").
GroupBy("CategoryID").
SortBy("ProductID", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending).
Filter("[CategoryID] != 8").
Build("MyQuery")
' Add the query to the collection and return the data source.
ds.Queries.Add(query)
Return ds
End Function
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.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.Xpo.DB;
// ...
private SqlDataSource BindToData() {
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters =
new Access97ConnectionParameters("../../Data/nwind.mdb", "", "");
SqlDataSource 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.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Categories")
.SelectColumn("CategoryName")
.GroupBy("CategoryName")
.Join("Products", "CategoryID")
.SelectColumn("ProductName", AggregationType.Count, "ProductCount")
.SortBy("ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending)
.GroupFilter("[ProductCount] > 7")
.Build("MyQuery");
// Add the query to the collection and return the data source.
ds.Queries.Add(query);
return ds;
}
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports DevExpress.Xpo.DB
' ...
Private Function BindToData() As SqlDataSource
' Create a data source with the required connection parameters.
Dim connectionParameters As New Access97ConnectionParameters("../../Data/nwind.mdb", "", "")
Dim ds As 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.
Dim query As SelectQuery = SelectQueryFluentBuilder.
AddTable("Categories").
SelectColumn("CategoryName").
GroupBy("CategoryName").
Join("Products", "CategoryID").
SelectColumn("ProductName", AggregationType.Count, "ProductCount").
SortBy("ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending).
GroupFilter("[ProductCount] > 7").
Build("MyQuery")
' Add the query to the collection and return the data source.
ds.Queries.Add(query)
Return ds
End Function
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.
See Also