Query

The Query control is used to perform SQL data commands on the data configured for the DataSet and Connection controls. Delete, Insert, Select and Update commands can be configured for data in your project.

The Properties for the Query control are used to construct the SQL Queries for the data.

Properties

The key properties for the Query control include:

  • Delete/Insert/Select/Update SQL. Specifies the SQL command to be performed on the details, including credentials for connecting to the application, server or site.

  • DataBindings. Specifies the configured DataBindings for the Query.

  • DeleteCommandExecuteType. Determines the execution method of a configured Delete SQL command - NonQuery, Reader, Scalar or XMLReader.

  • Connection. Specifies the associated connection for the Query.

  • DataSet. Specifies the DataSet for the Query.

Adding a Query

When you drag an Query control from the Toolbox to a form in the Flow Designer, it will appear as an entry in the design space. You can select the control and configure it using the Properties pane.

To configure an SQL command for the Query control, use the relevant SQL field in the Command section of the Properties pane. For example, to configure a Select command, click the drop down in the Select SQL field.

The panel that is opened is used to enter the SQL query. As displayed, press Enter to begin a new line and press CTRL + Enter to accept the entered text.

Using SQL Parameters

The SQL command for the Query control supports the use of parameters. For example, you might want to generate data controls with a query for lithology records from a set of drillholes (sites).

To do this, you could add a parameter @site with a value of one of the sites from a project or a wildcard string.

To use a fixed initial value to return the required data,

  • Parameters should be names with the @ prefix used for SQL parameters - e.g. @site

  • If an initial value is set, string values do not need to be enclosed in single quotes.

  • Ensure the Data | Size property is large enough to accommodate possible string lengths.

It is also important to note that parameter values can be read or set in code using the relevant property. For example: “… _Query.SelectCommand.Parameters.Item(0).Value “

In the example below, the following SQL Command Text was used to collect Site and Lithology information from the GB_LITHOLOGY database table using a wildcard string:

SELECT * FROM GB_LITHOLOGY WHERE SITE_ID LIKE @site

In this, the ParameterName name @site is set to a value to filter by SITE_ID (in this case a wildcard string match: SW%). You'll see from the example that this results in all lithology rows for SITE_IDs with a prefix of SW being returned in the data table on the Flow form.

More information on SQL queries is available in Structured Query Language (SQL).