Page History

Datasets → Tutorial | Concept | How-to Guide | Reference

Configuration Properties

Creating Queries

1. Navigate to Datasets → Queries
2. Click Plus icon
3. Configure:
   • Name: Unique identifier (use underscores)
   • DB: Select database
   • Description: Documentation
4. Click OK
5. Double-click SqlStatement to open editor

SQL Statement Configuration

Static Queries

sql

SELECT * FROM Production WHERE Status = 1
UPDATE Inventory SET Count = 100 WHERE ID = 5
DELETE FROM TempData WHERE Age > 30

Dynamic Parameters

Embed tag values using double curly braces:

sql

SELECT * FROM Production 
WHERE BatchID = {{Tag.Production/Batch_ID}}
  AND Temperature > {{Tag.TankFarm/Tank1/Temp}}
  AND Status = {{Tag.Equipment/MotorRunning}}

DateTime Format

Default format: yyyy-MM-dd HH:mm:ss (ISO 8601)

sql

SELECT * FROM History 
WHERE Timestamp > '{{Tag.StartDate}}'

Tag Mapping

Configure result set mapping to tags:

1. Click Mapping ellipsis (...)
2. Assign columns to tags:

Mapping Options

• Output: Column value → Tag
• Input: Tag → Query parameter
• Input/Output: Bidirectional

Stored Procedures

Configuration

sql

-- Simple execution
EXEC GetProductionData @Date = '{{Tag.QueryDate}}'

-- With output parameters
EXEC TestOutput 
  @return_Value = {{Tag.ReturnValue}} RETURN_VALUE,
  @vcrPrefix = @null@,
  @intNextNumber = {{Tag.NextNumber}} OUTPUT,
  @vcrFullLicense = {{Tag.NextLicense}} OUTPUT

Null Parameters

Use @null@ syntax for null values:

sql

EXEC UpdateBatch @ID = {{Tag.BatchID}}, @Notes = @null@

Execution Methods

Asynchronous (Properties)

csharp

// Trigger query execution
@Dataset.Query.MyQuery.SqlStatement = "SELECT * FROM Data";
@Dataset.Query.MyQuery.Select = true;

// Check status
if (@Dataset.Query.MyQuery.ExecutionStatus == "OK")
{
    // Process results
}

Synchronous (Methods)

csharp

// Execute and wait for results
DataTable result = @Dataset.Query.MyQuery.SelectCommand();

// With status check
string status;
@Dataset.Query.MyQuery.ExecuteCommandWithStatus(out status);

Script Example

csharp

public void ExecuteQuery()
{
    // Build dynamic query
    string sql = string.Format(
        "SELECT * FROM Production WHERE Line = '{0}'",
        @Tag.ProductionLine
    );
    
    @Dataset.Query.ProductionQuery.SqlStatement = sql;
    
    // Execute with status
    string status;
    @Dataset.Query.ProductionQuery.ExecuteCommandWithStatus(out status);
    
    if (status == "OK")
    {
        @Tag.QueryResult = "Success";
    }
}

Query Editor

Built-in SQL editor features:

• Syntax highlighting
• Auto-completion
• Schema browsing
• Parameter validation

Access via:

• Double-click SqlStatement column
• Right-click → Edit Query

Visual Query Builder

Graphical query construction:

1. Navigate to Datasets → DBs
2. Click Visual Query Builder
3. Drag tables to canvas
4. Connect fields visually
5. Generate SQL automatically

Testing Queries

Data Explorer

1. Go to Data Explorer → SQL Queries
2. Select query from list
3. Set parameter values
4. Click Execute
5. Review results in grid

Runtime Testing

csharp

// Test mode execution
@Dataset.Query.TestQuery.SqlStatement = 
    "SELECT COUNT(*) FROM Production";
@Dataset.Query.TestQuery.Select = true;

Best Practices Checklist

•  Use parameters - Prevent SQL injection
•  Name meaningfully - ProductionData not Query1
•  Document queries - Use description field
•  Test thoroughly - Use Data Explorer
•  Handle errors - Check execution status
•  Optimize performance - Index, limit results
•  Use appropriate execution - Async for UI

Performance Tips

Query Optimization

sql

-- Use TOP/LIMIT
SELECT TOP 100 * FROM LargeTable

-- Use indexes
CREATE INDEX idx_timestamp ON Data(Timestamp)

-- Parameterize queries
SELECT * FROM Table WHERE ID = {{Tag.ID}}

Bulk Operations

sql

-- Use transactions
BEGIN TRANSACTION
UPDATE Table1...
UPDATE Table2...
COMMIT

Troubleshooting

Query not executing:

• Verify DB connection
• Check SQL syntax
• Confirm tag names
• Review permissions

Slow performance:

• Add indexes
• Limit result sets
• Use async execution
• Check network latency

Mapping issues:

• Verify column names
• Check tag types
• Review data types
• Confirm tag existence

Creating Queries

 To create and configure a Dataset Query, follow the steps below:

1. Access Datasets / Queries.
2. Click the plus icon.

3. Enter a Name and Description for the Query

4. Click Ok.

5. Define the DB (Database Connection), and Go To SQL Query editor to define the Query (you can navigate using the tree or double-click the SqlStatement column)

   Info
   The use of spaces is not allowed in the Name. Instead, use underscores. For example My_Query.

   Image Removed

You can modify the Query settings after its creation. Double-click the property you wish to edit on the row corresponding to the Query you want to modify. The following table lists all possible configuration properties.

Column

Description

Name

The Query's name. The system will warn you if you choose an invalid name.

DB

Defines the database where the Query will be executed.

SqlStatement

Specify the Query using SQL syntax. You can embed Tags directly on the SQL statement, embracing the tag with curly brackets. For example, ColumnName = {tag.tagInt}.

Alternatively, you can update the SQL Statement with the Script module accessing the property Dataset.Query.QueryName.SqlStatement. If you access that property from multiple tasks using this approach, be aware of potential concurrency issues. For more information, access Advanced Dataset Guide.

Mapping

Click the three dots button (...) to select the tags you want to populate with data from specific columns the Query returns.

MappingDateTime

Specify how the time in Database will be interpreted (UTC or Local), when mapping to DateTimeOffset .NET variables.

Description

Query's description.

Editing Queries

The queries are ending using the Built-In SQL Query Editor, which assists doing the hight of the SQL Syntax. 

Image Removed

→ Learn more at the Query Editor page.

There is also the tool VisualQueryBuilder which allows to explore databases, and drag-drop tables and visually connect fields to build the Query. 

The VisualQueryBuilder is started navigating to Datasets → DBs, and clicking on the Visual Query Builder button. 

→ See Visual SQL Query Builder for more information on that tool.

Testing Queries

The best place to test your queries is the DataExplorer SQL Queries tool.

Image Removed

Any query used in the solution, or even new queries you are modifying in that environment can be executed. 

The results are presented in a DataGrid object in which you can customize the columns.

In this section...