Datasets Queries (Reference)

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

In this section...