SelectQueryFluentBuilder Class
Provides methods that can be chained within a single statement specifying a SelectQuery.
Namespace: DevExpress.DataAccess.Sql
Assembly: DevExpress.DataAccess.v23.2.dll
NuGet Packages: DevExpress.DataAccess, DevExpress.Win.PivotGrid, DevExpress.Win.TreeMap
Declaration
Related API Members
The following members return SelectQueryFluentBuilder objects:
Remarks
A SelectQueryFluentBuilder instance is created by calling the static SelectQueryFluentBuilder.AddTable method.
The chain of methods must end by 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;
}
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;
}
After adding queries and relations, you should call the SqlDataSource.RebuildResultSchema method. It rebuilds the set of fields that become available after the query or stored procedure is executed on the server.
The final step in setting up the SQL data source is a call to the SqlDataSource.Fill method when you need to manually manipulate the results returned by the SqlDataSource.Result property value. Components bound to the SqlDataSource call the Fill method internally when needed.
For more information, review the following help topic: Use SqlDataSource.