It is possible to include parameters in the following notations to the query:
expression = :param (named notation)
expression = ? (unnamed notation)
expression = @Param (for MS SQL only)
Parameter names can be quoted according to quotation rules of your database server (with double quotes or square brackets)
Some database servers support ordered parameter notation ( "expression = ?1" ), some allow question-prefixed named parameters.
End users can use parameters in any SQL expressions:
Active Query Builder provides detailed information about each parameter's usage in the query via the QueryBuilder.Parameters collection:
- the parameter name,
- prefix symbol ("?", ":" or "@")
- if the left expression is a field, information about this field: field name and database object name.
You can examine this collection before the query execution and prompt the end-user for parameter values. An example of such usage is provided in the sample projects included in the installation packages.
Note: Some of the data access components may lack the support of some of the mentioned parameter notations; please refer to the documentation for your data access components for details.
So, the current scenario of working with parameters is the following
- check the query for parameters before executing it
- check if parameters are defined in notation acceptable by specific SqlCommand implementation
- ask the user for parameter values or specify values according to the program logic.
- fill the SqlCommand.Parameters collection with parameters and their values.
- execute the query.
This scenario is illustrated within the Full-featured demo project. You can review the CreateSqlCommand method in the Helpers.cs and ResultGrid.cs source files.
In the upcoming version 3.6 of Active Query Builder for .NET Professional version which will be available in March 2019, we will add a more strict mode of working with parameters. You'll be able to define a list of allowed parameter names and set an option to throw an error if a non-listed parameter is used in the query. The user will be notified about the allowed parameter names via the code auto-completion: they will be listed in the suggestion list after typing one of the parameter prefix symbols in the text of any SQL expression.