Create SQL Queries

• Dec 17, 2021
• 3 minutes to read

You can add the following query types to the Queries collection:

Tables (SelectQuery)

The SelectQuery class allows you to create SELECT statements. You can use the SelectQueryFluentBuilder class properties and methods:

C#

using DevExpress.DataAccess.Sql;
//...  
    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");  
    sqlDataSource.Queries.Add(query);  

VB.NET

Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
' ...   
    Dim query As SelectQuery = SelectQueryFluentBuilder.
        AddTable("Products").
        SelectColumn("CategoryID").
        GroupBy("CategoryID").
        SortBy("ProductID", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending).
        Filter("[CategoryID] != 8").
        Build("MyQuery") 
    sqlDataSource.Queries.Add(query)

Use our Query Builder visual component to edit the SelectQuery.

Stored Procedures (StoredProcQuery)

The StoredProcQuery query defines a parameterized query for the server stored procedure. Add QueryParameter instances to the StoredProcQuery.Parameters collection:

C#

using DevExpress.DataAccess.Sql;
//...
StoredProcQuery spQuery = new StoredProcQuery("QueryName", "stored_procedure_name");  
spQuery.Parameters.Add(new QueryParameter("@First",typeof(int), 0 ));  
spQuery.Parameters.Add(new QueryParameter("@Second",typeof(string), "Value" ));  
spQuery.Parameters.Add(new QueryParameter("@Third",typeof(string), "Value"));  
sqlDataSource.Queries.Add(spQuery);  

VB.NET

Imports DevExpress.DataAccess.Sql
'...
Dim spQuery As New StoredProcQuery("QueryName", "stored_procedure_name")
spQuery.Parameters.Add(New QueryParameter("@First",GetType(Integer), 0))
spQuery.Parameters.Add(New QueryParameter("@Second",GetType(String), "Value"))
spQuery.Parameters.Add(New QueryParameter("@Third",GetType(String), "Value"))
sqlDataSource.Queries.Add(spQuery)

The QueryParameter type can be used with the SelectQuery class for server-side filtering, and to convert external parameter values (Reporting Parameter) to query parameters:

C#

QueryParameter parameter = new QueryParameter() {  
        Name = "catID",  
        Type = typeof(Expression),  
        Value = new Expression("[Parameters.catID]", typeof(System.Int32))  
    };  
query.Parameters.Add(parameter);  
query.FilterString = "CategoryID = ?catID"; 

VB.NET

Dim parameter As New QueryParameter() With {
    .Name = "catID", 
    .Type = GetType(Expression), 
    .Value = New Expression("[Parameters.catID]", GetType(System.Int32))}
query.Parameters.Add(parameter)
query.FilterString = "CategoryID = ?catID"

prior to v20.1

If a stored procedure uses temporary tables, you may encounter a problem when the schema of the query results is unavailable. To address the problem, turn off the FMTONLY flag for the statement that is never executed in this case, as described in the following Support Center ticket: T141620: “Invalid object name ‘#temp01’” when using a stored procedure with temporary tables as an Xtrareport data source.

Custom SQL (CustomSqlQuery)

CustomSqlQuery allows you to specify arbitrary SQL statements. Assign the query text to the Sql property:

C#

using DevExpress.DataAccess.Sql;
//...
CustomSqlQuery query = new CustomSqlQuery();  
query.Name = "q1";  
query.Sql = "Select top 10 * from Products";  
sqlDataSource.Queries.Add(query); 

VB.NET

Imports DevExpress.DataAccess.Sql
'...
Private query As New CustomSqlQuery()
query.Name = "q1"
query.Sql = "Select top 10 * from Products"
sqlDataSource.Queries.Add(query)

Thw execution of custom SQL queries is disabled for security reasons. To enable the CustomSqlQuery execution, set the static AllowCustomSqlQueries property to true. You can handle the ValidateCustomSqlQuery or ValidateCustomSqlQueryGlobal events to implement your own query validation logic. To disable query validation, use the DisableCustomQueryValidation property.