Datasets DBs Reference

Register and manage database connections.

Reference → Modules → Datasets → UI → DBs | Queries | Query Editor | Tables | Files | Monitor

Configuration Properties

Pre-Defined Databases

Four system databases are included in every solution:

Creating Database Connections

1. Navigate to Datasets → DBs
2. Click Plus icon
3. Configure:
   • Name: Unique identifier
   • Provider: Select from list
   • Database: Choose type
   • Description: Documentation
4. Click OK

ConnectionString Configuration

Structure

Provider=<provider>;Data Source=<source>;Additional Parameters

Examples

SQLite (Local):

Provider=System.Data.SQLite;
Data Source=_SolutionPathAndName_.db

SQL Server Express:

DataSource=.\SQLEXPRESS;
Initial Catalog=myDatabase;
User Id=sa;Password=pwd

PostgreSQL:

Host=localhost;Database=mydb;
Username=user;Password=pass;Port=5432

ConnectionString Macros

Use these macros for portable configurations:

Supported Providers

Database-Specific SQL Syntax

Parameterized Queries (PARAMS prefix)

Dataset query authors can declare bind parameters inline by prefixing the Command Text with a PARAMS block. The runtime parses the block, registers each parameter with the underlying ADO.NET command, and substitutes values at execution time — values never become part of the SQL string and are not subject to SQL-injection vectors.

Syntax

PARAMS @p0='value1'; @p1=123; @p2=@Tag.SomeTag
SELECT * FROM MyTable WHERE Name = @p0 AND Qty > @p1

Rules:

• PARAMS must be the first token; the block ends at the first newline that is not part of a parameter assignment.
• Parameter names start with @ and follow the host database's identifier rules (e.g. @p0, @customerId).
• Right-hand-side values may be string literals (single-quoted), numeric literals, or tag references (@Tag.<name>) resolved at execution time.
• The SQL body that follows references the same names; the provider performs the binding.

When to use

• Untrusted inputs: any value that originates from a Display field, a script argument, an external API, or a user-edited tag — use PARAMS to keep the value out of the SQL string.
• Repeated execution with different values: parameterized commands are prepared once and re-bound per execution, which is faster than re-parsing concatenated SQL.
• Type fidelity: the provider preserves the parameter's CLR type (string, int, DateTime, byte[]) end-to-end, avoiding culture-dependent string conversions.

PARAMS vs string concatenation

String concatenation ("SELECT * FROM T WHERE Name = '" + userInput + "'") is acceptable only when every concatenated fragment is a trusted, code-controlled constant. Any user-, tag-, or network-sourced value MUST go through PARAMS. The PARAMS path also avoids quote-escaping bugs and DateTime locale ambiguity that concatenation invites.

Minimal C# example

// Preferred: feed values as tag references so PARAMS never sees the raw string.
@Tag.FilterName = filterName;       // filterName came from a Display input
@Tag.FilterMinQty = filterMinQty;
@Dataset.Query.SelectCommand.SqlStatement =
    "PARAMS @name=@Tag.FilterName; @minQty=@Tag.FilterMinQty\n" +
    "SELECT Id, Name, Qty FROM Products WHERE Name = @name AND Qty >= @minQty";
@Dataset.Query.SelectCommand.Execute();

// The raw input never enters the SQL string; even a malformed input
// (with quotes, semicolons, or comment tokens) cannot break the parse.

Connection Management

Single Thread per DB

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

Parallel Execution

For concurrent operations to same database:

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

Create multiple DB connections to same database for parallelism.

Connection Pooling

• Reuse existing connections
• Minimize connection overhead
• Configure pool size appropriately

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)

Development vs Production

Execution Profiles

Automatic database switching for development:

Configuration

1. Configure production databases normally
2. Run in Development profile
3. Data automatically redirects to Dev databases
4. No manual switching required

Security Considerations

Password Management

• Only administrators can set LogonPassword
• Passwords encrypted in configuration
• Use Windows Authentication when possible

Remote Access

Configure ServerIP for gateway security:

• Requires TWebServices on remote computer
• Provides secure tunnel for database access
• Isolates database from direct access

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

Utilities

SQLite Admin

Built-in tool for SQLite management:

• Browse tables
• Execute queries
• Import/export data

Visual Query Builder

Interactive SQL query creation:

• Drag-and-drop interface
• Join visualization
• Query testing

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 Checklist

• Use macros - Portable connection strings
• Test connections - Verify before deployment
• Document databases - Clear descriptions
• Secure passwords - Admin-only access
• Use Dev profiles - Protect production data
• Regular backups - Especially SQLite files
• Monitor performance - Check query execution

Troubleshooting

Connection failed:

• Verify provider installed
• Check connection string syntax
• Confirm database exists
• Test network connectivity

Access denied:

• Check credentials
• Verify permissions
• Review firewall rules

Performance issues:

• Add indexes
• Optimize queries
• Check network latency
• Review connection pooling

Related Documentation

• Datasets Query (Reference)
• Datasets Tables (Reference)
• Datasets Engine (Reference)
• MongoDB Database Connector - document database with JSON directives, aggregation pipelines, and a SQL-subset dialect.

In this section...