Datasets Engine (Reference)

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

Overview

The Dataset Engine manages all database operations through a multi-layered architecture that ensures security, performance, and scalability. This reference covers essential practices for production deployments, including concurrency management, security hardening, and performance optimization.

The engine orchestrates:

• Synchronous and asynchronous database operations
• Connection pooling and thread management
• Client/server domain tag mapping
• Query execution through TServer services
• Result set propagation and distribution
• Multi-database coordination

Understanding the engine helps when:

• Optimizing database performance
• Managing concurrent operations
• Implementing client-specific data
• Troubleshooting connection issues

Architecture & Execution Model

Process Separation

The Dataset Module operates through a distributed architecture:

TRunModule.exe (Dataset) handles:

• Reading all dataset configurations
• Managing tag mapping and updates
• Request coordination and queuing
• Result distribution to displays, reports, and scripts

TServer provides:

• Actual database connections
• SQL execution services
• Connection pooling
• Transaction management
• Network gateway capabilities

This separation enables secure database access through controlled gateways and maintains network security policies.

Connection Management

Single Thread per DB:

• Each DB configuration creates ONE connection
• Single execution thread per database
• Sequential command execution ensures consistency

Parallel Execution Strategy: For concurrent operations to the same database, create multiple DB connections:

DB1 → Production Database (Thread 1)
DB2 → Production Database (Thread 2)  
DB3 → Production Database (Thread 3)

Execution Methods

Asynchronous Execution (Default)

Trigger: Property changes (Select, Insert, Update, Delete)

Flow:

1. Property triggered (screen/script)
2. Request propagated to server
3. Dataset Module receives request
4. TServer executes database operation
5. Results returned to Dataset Module
6. Tags mapped and updated
7. Execution continues in parallel

Advantages:

• Non-blocking operation
• Better UI performance
• Prevents interface freezing
• Allows parallel operations

Use Cases:

• Display queries
• Background updates
• Report generation
• Real-time monitoring

Synchronous Execution

Trigger: Method calls (SelectCommand, ExecuteCommand)

Flow:

1. Method called
2. Execution PAUSES
3. Dataset Module calls TServer
4. Database operation completes
5. Results returned
6. Tags mapped
7. Execution RESUMES

Advantages:

• Guaranteed completion
• Sequential logic support
• Immediate results
• Transaction support

Risks:

• Can freeze UI if called from screen
• Blocks thread execution
• Performance bottlenecks

Use Cases:

• Script tasks
• Sequential operations
• Transaction requirements
• Data validation

Concurrency & Domain Management

Tag Domain Mapping

Tag mapping occurs in the original call domain:

Client-Initiated Calls:

• From displays or client scripts
• Mapping uses client domain
• Results visible to specific client
• Isolated from other clients

Server-Initiated Calls:

• From server scripts or tasks
• Mapping uses server domain
• Results visible project-wide
• Shared across all clients

Domain Selection Strategy

Preventing Concurrency Conflicts

All Dataset Module properties exist in the server domain, creating shared resource risks:

Example Risk:
1. Client A sets: SQLStatement = "SELECT * FROM Orders WHERE Status='Open'"
2. Client B sets: SQLStatement = "SELECT * FROM Orders WHERE Status='Closed'"  
3. Execute command runs with Client B's statement (last write wins)

Prevention Strategies:

1. Dedicated Query Objects: Create separate query objects for different operations
2. Synchronization Patterns: Use semaphores or locks in scripts
3. Client-Side Processing: Execute queries in scripts with local variables

Data Management Strategies

DataTable Usage Patterns

Pattern 1: Direct Script Processing

csharp

DataTable result = @Dataset.Query.Query1.SelectCommand();
// Process data locally without server domain impact
foreach(DataRow row in result.Rows) {
    // Local processing
}

Pattern 2: Tag Distribution

csharp

// Assign to DataTable tag for module sharing
@Tag.MyDataTable = @Dataset.Query.Query1.SelectCommand();
// Now available to displays, reports, etc.

Pattern 3: Mapped Navigation

csharp

// Configure mapping, then navigate rows
@Dataset.Query.Query1.Select();
@Dataset.Query.Query1.Next(); // Moves to next row

Memory & Traffic Optimization

Control Data Volume:

• Always use WHERE clauses to filter results
• Implement pagination for large datasets
• Use SELECT specific columns instead of SELECT *
• Consider indexed views for complex queries

Resource Planning:

Small Query: < 1,000 rows = Minimal impact
Medium Query: 1,000-10,000 rows = Monitor memory usage
Large Query: > 10,000 rows = Implement pagination

Security Implementation

SQL Injection Prevention

Never Do This:

sql

string query = "SELECT * FROM Users WHERE Name = '" + userInput + "'";

Always Do This:

sql

execute GetUserData @userName={Tag.UserInput}, @userId={Tag.UserId}

The platform's parameterization:

• Treats all parameters as values, not code
• Prevents malicious SQL execution
• Maintains data type integrity
• Supports all major database platforms

Network Security Architecture

Gateway Configuration for Restricted Databases:

1. Identify Restriction: Database accessible only from specific servers
2. Install Gateway: Deploy platform with TWebServer on authorized machine
3. Configure ServerIP: Point Dataset connections to gateway machine
4. Result: Secure database access through controlled gateway

Benefits:

• Maintains network security policies
• Centralizes database connections
• Enables audit logging
• Supports DMZ architectures

Database-Specific Configuration

Platform Compatibility

Time Zone Management

Default Behavior:

• Platform stores all DateTime values as UTC
• Historian and Alarm data always in UTC
• Automatic conversion for display

Configuring External Databases:

DateTimeMode Settings:
- UTC: No conversion needed
- LocalTime: Platform converts automatically
- Custom: Handle in SQL statements

Local Time Queries:

sql

-- Adjust for local time zone (example: EST -5 hours)
WHERE Timestamp >= DATEADD(hour, -5, @Tag.StartTimeUTC)

Performance Optimization

Query Optimization Checklist

? Indexes: Ensure indexes on filtered and joined columns ? Statistics: Update database statistics regularly ? Query Plans: Review execution plans for bottlenecks ? Connection Pooling: Enable for frequent operations ? Batch Operations: Group multiple operations when possible

Performance Monitoring

Key Metrics to Track:

• Query execution time
• Memory consumption
• Network latency
• Connection pool usage
• Cache hit rates

Diagnostic Properties:

@Dataset.Query.Query1.Error         // Last error message
@Dataset.Query.Query1.ExecutionTime // Query duration
@Dataset.Query.Query1.RowCount      // Result size

Optimization Patterns

Good: Asynchronous from screen

csharp

@Dataset.Table.MyTable.SelectCommand = "SELECT * FROM Data";

Bad: Synchronous from screen (blocks UI)

csharp

@Dataset.Table.MyTable.SelectCommandWithStatus();

Error Handling & Recovery

Error Detection Methods

Method 1: Property Monitoring

csharp

@Dataset.Query.Query1.SelectCommand();
if (@Dataset.Query.Query1.Error != "") {
    // Handle error
}

Method 2: Status Methods

csharp

string status;
DataTable result = @Dataset.Query.Query1.SelectCommandWithStatusAsync(out status);
if (status != "OK") {
    // Handle error
}

Common Error Scenarios

Backup & Recovery

SQLite Backup Strategies

Option 1: Command Line Backup

bash

sqlite3 source.db ".backup backup.db"

• Simple and reliable
• Requires database file access
• Best for scheduled maintenance

Option 2: Online Backup API

• Backup while database is active
• Support for incremental backups
• Progress monitoring capability

Option 3: File System Copy

• Only when database is offline
• Fastest for large databases
• Requires downtime

Backup Best Practices

1. Schedule: Automate backups during low-activity periods
2. Verify: Test restore procedures monthly
3. Rotate: Maintain multiple backup generations
4. Secure: Store backups in separate physical location
5. Document: Maintain restore procedure documentation

Store & Forward Limitations

The Store & Forward feature has specific requirements:

• Applies Only To: Historian and Alarm databases
• Required Schema: Must include control columns
• Not Available For: Generic application databases
• Alternative: Implement custom buffering for generic databases

For Store & Forward configuration, see Historian Archiving Process documentation.

Best Practices Summary

Design Principles

1. Isolation: Use dedicated query objects for different operations
2. Filtering: Always limit result sets with WHERE clauses
3. Security: Use parameterized queries exclusively
4. Monitoring: Track performance metrics and errors
5. Planning: Design for concurrent access from the start

Production Checklist

Before deploying to production:

? Parameterized all dynamic queries ? Implemented error handling for all operations ? Tested concurrent access scenarios ? Configured appropriate timeouts ? Established backup procedures ? Documented recovery processes ? Verified timezone handling ? Optimized query performance ? Planned for data growth ? Secured network access

Troubleshooting Guide

Slow queries:

• Check execution plan
• Add appropriate indexes
• Reduce result set size
• Use asynchronous execution

Connection issues:

• Verify TServer running
• Check connection string
• Review firewall rules
• Monitor connection pool

Tag mapping problems:

• Verify domain selection
• Check tag existence
• Review mapping configuration
• Confirm execution context

Thread blocking:

• Avoid synchronous in UI
• Use Script Tasks
• Implement async patterns
• Monitor thread pool

Related Documentation

• Datasets SQL Query (Tutorial)
• Datasets Module (Concept)
• Datasets Module (How-to Guide)
• Historian Archiving Process
• TServer Configuration Guide

In this section...