SelectQueryFluentBuilder Class

Provides methods that can be chained within a single statement specifying a SelectQuery.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v21.2.dll

NuGet Packages: DevExpress.DataAccess, DevExpress.Win.Design

Declaration

C#

public sealed class SelectQueryFluentBuilder

VB.NET

Public NotInheritable Class SelectQueryFluentBuilder

Related API Members

The following members return SelectQueryFluentBuilder objects:

Show 39 links

• SelectQueryFluentBuilder.AddTable(String, String)
• SelectQueryFluentBuilder.AddTable(String)
• SelectQueryFluentBuilder.Distinct()
• SelectQueryFluentBuilder.Filter(String)
• SelectQueryFluentBuilder.GroupBy(String)
• SelectQueryFluentBuilder.GroupBy(String[])
• SelectQueryFluentBuilder.GroupFilter(String)
• SelectQueryFluentBuilder.Join(String, RelationColumnInfo[])
• SelectQueryFluentBuilder.Join(String, SqlJoinType, RelationColumnInfo[])
• SelectQueryFluentBuilder.Join(String, SqlJoinType, String, String, ConditionType)
• SelectQueryFluentBuilder.Join(String, SqlJoinType, String, String, String, ConditionType)
• SelectQueryFluentBuilder.Join(String, SqlJoinType, String, String)
• SelectQueryFluentBuilder.Join(String, SqlJoinType, String)
• SelectQueryFluentBuilder.Join(String, String, RelationColumnInfo[])
• SelectQueryFluentBuilder.Join(String, String, SqlJoinType, RelationColumnInfo[])
• SelectQueryFluentBuilder.Join(String, String, SqlJoinType, String, String)
• SelectQueryFluentBuilder.Join(String, String, SqlJoinType, String)
• SelectQueryFluentBuilder.Join(String, String, String, String)
• SelectQueryFluentBuilder.Join(String, String, String)
• SelectQueryFluentBuilder.Join(String, String)
• SelectQueryFluentBuilder.SelectAllColumns()
• SelectQueryFluentBuilder.SelectAllColumnsFromTable()
• SelectQueryFluentBuilder.SelectColumn(String, AggregationType, String)
• SelectQueryFluentBuilder.SelectColumn(String, String)
• SelectQueryFluentBuilder.SelectColumn(String)
• SelectQueryFluentBuilder.SelectColumns(String[])
• SelectQueryFluentBuilder.SelectExpression(String, AggregationType, String)
• SelectQueryFluentBuilder.SelectExpression(String, String)
• SelectQueryFluentBuilder.SelectRecordsCount(String)
• SelectQueryFluentBuilder.Skip(Int32)
• SelectQueryFluentBuilder.SortBy(String, AggregationType, ListSortDirection)
• SelectQueryFluentBuilder.SortBy(String, AggregationType)
• SelectQueryFluentBuilder.SortBy(String, ListSortDirection)
• SelectQueryFluentBuilder.SortBy(String)
• SelectQueryFluentBuilder.SortBy(String[])
• SelectQueryFluentBuilder.SortByExpression(String, AggregationType, ListSortDirection)
• SelectQueryFluentBuilder.SortByExpression(String, ListSortDirection)
• SelectQueryFluentBuilder.SortByExpression(String)
• SelectQueryFluentBuilder.Top(Int32)

Remarks

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.

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

Inheritance

Object

SelectQueryFluentBuilder