Page History

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

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)

In this section...