SelectQuery Class

A set of columns (from a single table or multiple joined tables), which forms a SELECT statement when executing a query.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v17.2.dll

Declaration

C#

public sealed class SelectQuery :
    SqlQuery

VB.NET

Public NotInheritable Class SelectQuery
    Inherits SqlQuery

Related API Members

The following members accept/return SelectQuery objects:

Library	Related API Members
Cross-Platform Class Library	SelectQueryFluentBuilder.Build(String)
Reporting	SaveQueryEventArgs.ResultQuery

Remarks

In the following example, a query selects and orders records from a single data table.

C#

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;
}

VB.NET

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.

C#

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;
}

VB.NET

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.

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the SelectQuery class.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-create-data-access-library-data-sources-at-runtime-t424210/CS/SqlDataSourceSnippets/Code.cs#L25

{
    SelectQuery query = SelectQueryFluentBuilder
                        .AddTable("Categories")

Reporting_how-to-implement-a-custom-authorization-service-t488888/CS/AuthorizationService/Reports/CategoriesReport.cs#L74

this.components = new System.ComponentModel.Container();
DevExpress.DataAccess.Sql.SelectQuery selectQuery1 = new DevExpress.DataAccess.Sql.SelectQuery();
DevExpress.DataAccess.Sql.Column column1 = new DevExpress.DataAccess.Sql.Column();

Reporting_how-to-use-the-web-report-designer-and-web-document-viewer-in-the-web-gardens-t528588/CS/DevExpressWebGardenReporting/Reports/InvoiceReport.cs#L29

this.components = new System.ComponentModel.Container();
DevExpress.DataAccess.Sql.SelectQuery selectQuery1 = new DevExpress.DataAccess.Sql.SelectQuery();
DevExpress.DataAccess.Sql.Column column1 = new DevExpress.DataAccess.Sql.Column();

Reporting_how-to-use-the-web-report-designer-in-javascript-t561230/CS/ServerSide/XtraReport1.cs#L15

this.components = new System.ComponentModel.Container();
DevExpress.DataAccess.Sql.SelectQuery selectQuery1 = new DevExpress.DataAccess.Sql.SelectQuery();
DevExpress.DataAccess.Sql.Column column1 = new DevExpress.DataAccess.Sql.Column();

Reporting_how-to-use-the-web-document-viewer-in-javascript-with-reactjs-t561300/CS/ServerSide/XtraReport1.cs#L15

this.components = new System.ComponentModel.Container();
DevExpress.DataAccess.Sql.SelectQuery selectQuery1 = new DevExpress.DataAccess.Sql.SelectQuery();
DevExpress.DataAccess.Sql.Column column1 = new DevExpress.DataAccess.Sql.Column();

VB.NET

how-to-create-data-access-library-data-sources-at-runtime-t424210/VB/SqlDataSourceSnippets/Code.vb#L21

Private Sub SelectQueryCreation()
    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("Categories")
    DataSource.Queries.Add(query)

Reporting_how-to-implement-a-custom-authorization-service-t488888/VB/AuthorizationService/Reports/CategoriesReport.vb#L76

Me.components = New System.ComponentModel.Container()
Dim selectQuery1 As New DevExpress.DataAccess.Sql.SelectQuery()
Dim column1 As New DevExpress.DataAccess.Sql.Column()

Reporting_how-to-use-the-web-report-designer-and-web-document-viewer-in-the-web-gardens-t528588/VB/DevExpressWebGardenReporting/Reports/InvoiceReport.vb#L31

components = New System.ComponentModel.Container()
Dim selectQuery1 As DevExpress.DataAccess.Sql.SelectQuery = New DevExpress.DataAccess.Sql.SelectQuery()
Dim column1 As DevExpress.DataAccess.Sql.Column = New DevExpress.DataAccess.Sql.Column()

Reporting_how-to-use-the-web-document-viewer-in-javascript-with-reactjs-t561300/VB/ServerSide/XtraReport1.vb#L17

Me.components = New System.ComponentModel.Container()
Dim selectQuery1 As New DevExpress.DataAccess.Sql.SelectQuery()
Dim column1 As New DevExpress.DataAccess.Sql.Column()

Reporting_how-to-use-the-web-document-viewer-in-javascript-t561226/VB/ServerSide/XtraReport1.vb#L17

Me.components = New System.ComponentModel.Container()
Dim selectQuery1 As New DevExpress.DataAccess.Sql.SelectQuery()
Dim column1 As New DevExpress.DataAccess.Sql.Column()

Inheritance

Object

SqlQuery

SelectQuery

Extension Methods

Show 20 items

EditQuery()

EditQuery(IRepositoryItemsProvider)

EditQuery(UserLookAndFeel)

EditQuery(UserLookAndFeel, IRepositoryItemsProvider)

EditQuery(UserLookAndFeel, IWin32Window)

EditQuery(UserLookAndFeel, IWin32Window, IRepositoryItemsProvider)

EditQuery(UserLookAndFeel, IWin32Window, IDBSchemaProvider)

EditQuery(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IRepositoryItemsProvider)

EditQuery(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService)

EditQuery<TModel>(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService, Action<IWizardCustomization<TModel>>)

EditQuery<TModel>(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService, Action<IWizardCustomization<TModel>>, IServiceProvider)

EditQuery(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService, IRepositoryItemsProvider)

EditQuery<TModel>(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService, IRepositoryItemsProvider, Action<IWizardCustomization<TModel>>)

EditQuery<TModel>(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService, IRepositoryItemsProvider, Action<IWizardCustomization<TModel>>, IServiceProvider)

EditQuery<TModel>(UserLookAndFeel, IWin32Window, IDBSchemaProvider, IParameterService, IRepositoryItemsProvider, Action<IWizardCustomization<TModel>>, IServiceProvider, ICustomQueryValidator)

EditQuery(EditQueryContext)

EditQuery<TModel>(EditQueryContext, Action<IWizardCustomization<TModel>>)

EditQueryWithQueryBuilder()

EditQueryWithQueryBuilder(QueryBuilderEditQueryContext)

EditQueryWithQueryBuilder(QueryBuilderEditQueryContext, QueryBuilderRunnerDelegate)