TL;DR: Are you searching for a way to create complex queries easily? Syncfusion Blazor Query Builder is just what you need — a simple, graphical tool for creating queries with ease. This blog highlights the new features of the Blazor Query Builder added in the Essential Studio® 2024 Volume 1 release.
The Syncfusion Blazor Query Builder is a rich and responsive UI for filtering large amounts of data by creating or editing conditions that can be combined with data visualization controls like DataGrid and Charts to view the filtered data. It outputs structured JSON filters that can be easily parsed to create SQL queries. It allows you to create and group conditions using the AND/OR logic.
This blog will explore the new features introduced to the Blazor Query Builder component in the Essential Studio® 2024 Volume 1 release.
New features
The Blazor Query Builder component delivers the following new features:
Parameter SQL Query support
The Parameter SQL Query allows users to include placeholders or parameters within SQL queries, which can be dynamically filled with values during execution. This enables a more flexible and customizable approach to querying databases.
Instead of hardcoding specific values directly into the SQL query, parameters can be used to represent values that may change based on user input or other conditions.
The following new SQL query types are added in this 2024 volume release:
- Parameter SQL
- Named Parameter SQL
Parameter SQL: Parameters are typically represented by question marks (?) as placeholders in the SQL query string. These placeholders are positional, meaning they are replaced with parameter values based on their order when the query is executed.
Refer to the following example.
SELECT * FROM users WHERE username = ? AND age > ?
In this query, the first ? represents the value for the username parameter, and the second ? represents the value for the age parameter. When executing the query, the parameter values will be provided in the same order as the placeholders.
Named Parameter SQL: Parameters are represented by names rather than positions. Placeholders are specified using a syntax that includes a prefix followed by the parameter name, such as :parameter_name.
Refer to the following example.
SELECT * FROM users WHERE username = :username AND age > :min_age
In this query, the :username and :min_age are named parameters. When the query is executed, the parameter values will be bound to the named parameters based on their names. Named parameter SQL enhances readability and flexibility since parameter values can be provided in any order, maintaining clarity about the use of each parameter.
The SetParameterSql and SetNamedParameterSql methods streamline the process of importing from both Parameter and Named Parameter SQL, respectively. This can be achieved by integrating SQL queries with parameters directly into the Blazor Query Builder.
Conversely, the GetParameterSql and GetNamedParameterSql methods facilitate exporting to Parameter SQL and Named Parameter SQL, respectively, using the ParameterSql and NamedParameterSql classes. These methods enable the seamless incorporation of defined conditions from the Query Builder into SQL queries with parameters, enhancing flexibility and adaptability in query processing.
Refer to the following code example.
// To set Parameter SQL.
object[] Params = new object[] { "Nancy" };
ParameterSql = new ParameterSql()
{
Sql = "FirstName = ?",
Params = Params
};
QueryBuilderObj.SetParameterSql(ParameterSql);
// To set Named Parameter SQL.
Dictionary<string, object> Params = new Dictionary<string, object>();
Params.Add("FirstName_1", "Nancy");
ParameterNamedSQL = new NamedParameterSql()
{
Sql = "FirstName = :FirstName_1",
Params = Params
};
QueryBuilderObj.SetNamedParameterSql(ParameterNamedSQL);
// To get Parameter SQL.
ParameterSQL paramSQL = QueryBuilderObj.GetParameterSql(QueryBuilderObj.GetValidRules());
// To get Named Parameter SQL.
NamedParameterSql namedSQL = QueryBuilderObj.GetNamedParameterSql(QueryBuilderObj.GetValidRules()); Refer to the following image.
MongoDB Query support
A MongoDB query retrieves data from a MongoDB database, which stores data in JSON-like documents. Expressed in MongoDB Query Language (MQL), these queries operate at the document level, enabling retrieval, updating, or deletion based on specific criteria.
MongoDB queries offer powerful capabilities for precise querying and data aggregation. With optimization techniques enhancing performance, MongoDB remains a popular choice for its scalability and efficiency in various apps.
The SetMongoQuery method simplifies importing MongoDB queries directly into the Blazor Query Builder, facilitating seamless integration for dynamic querying within the app. Conversely, the GetMongoQuery method is used to export MongoDB queries, enabling the incorporation of defined conditions from the Query Builder into MongoDB queries.
Refer to the following code example.
// To set the MongoDB query to the Blazor Query Builder.
QueryBuilderObj.SetMongoQuery('{"$and":[{"Age":29 },{ "$or":[{"FirstName":{"$regex":"^Andre"}}]}]}');
// To get the MongoDB query from the Blazor Query Builder.
string mongoQuery = QueryBuilderObj.GetMongoQuery(QueryBuilderObj.GetValidRules());
Refer to the following image.
Note: For more details, refer to the MongoDB and Parameter SQL queries in the Blazor Query Builder demos.
Clone support
The Blazor Query Builder now allows cloning individual rules and entire groups. Utilizing the Clone options, we can generate an exact duplicate of a rule or group adjacent to the original one. This feature enables users to replicate complex query structures effortlessly.
The QueryBuilderShowButtons directive allows users to toggle the visibility of these cloning buttons, providing convenient control over the cloning process within the Query Builder interface. You can duplicate groups and rules in the Query Builder using the CloneGroup and CloneRule methods, respectively.
Refer to the following code example to enable clone options in the Blazor Query Builder.
<div className='col-lg-12 control-section'>
<SfQueryBuilder DataSource="@dataSource">
<QueryBuilderRule Condition="and" Rules="@rules"></QueryBuilderRule>
<QueryBuilderShowButtons CloneGroup="true" CloneRule="true"></QueryBuilderShowButtons>
<QueryBuilderColumns>
<!== Bind your columns here ==>
</QueryBuilderColumns>
</SfQueryBuilder>
</div>
Refer to the following image.
Note: For more details, refer to the clone support in the Blazor Query Builder demos.
Lock support
Now, you can lock individual rules or entire groups in the Query Builder.
When a rule is locked, it prevents users from modifying its field, operator, and value, effectively disabling these components. Similarly, locking a group disables all elements contained within it.
This feature offers users greater control over their query configurations, ensuring that specific rules or groups remain unchanged. Additionally, users can manage the visibility of locking buttons through the QueryBuilderShowButtons directive, allowing for seamless control over the locking mechanism.
To enforce access restrictions and prevent modifications, use the LockGroup method for all groups and the LockRule method for individual rules in the Query Builder.
Refer to the following code example to enable lock options in the Query Builder.
<SfQueryBuilder DataSource="@dataSource"> <QueryBuilderRule Condition="and" Rules="@rules"></QueryBuilderRule> <QueryBuilderShowButtons LockGroup="true" LockRule="true"></QueryBuilderShowButtons> <QueryBuilderColumns> <!== Bind your columns here ==> </QueryBuilderColumns> </SfQueryBuilder> </div>
Refer to the following image.
Note: For more details, refer to the lock options in the Blazor Query Builder demos.
Conclusion
Thanks for reading! We hope you enjoyed this quick introduction to the new features of our Blazor Query Builder component. If you want to give it a try, please download the latest available version of Essential Studio® 2024 Volume 1.
Experience the convenience of constructing database queries with our Blazor Query Builder component. It enables users to create, modify, and manage queries effortlessly without the need to write code manually. Enjoy the streamlined process of building complex queries with ease. Also, provide your valuable feedback in the comments section below.
You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!
