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...

Creating a New DB (Database connection)

To create a new DB connection, follow the steps below:

1. Access Datasets / DBs.

2. Click the plus icon.

3. Fill in the Name field and select a Provider and a Database. In addition, provide a meaningful description to make it easier to execute maintenance in the future.

4. Press Ok.

Image Removed

When using SQLite databases, the Dataset Module can automatically create the database locally if it doesn't already exist. For other database types, the database itself must already exist before you set your connection.

Configuring the ConnectionString

To configure a DB, follow the steps below.

1. Access Datasets/ DBs.

2. Double-click the property you wish to edit on the row corresponding to the channel you want to modify.

3. Edit the property field.

Image Removed

ConnectionString example for Microsoft SQL Express:

• DataSource: .\SQLEXPRESS
• Initial Catalog: myDatabase (or the name of your database)

ConnectionString Macros

When defining the ConnectionString, the following macros are available, independently of the select provider:

ConnectionString Macro

Macro

Description

_ExecutionPath_

Working directory for the solution. Unless otherwise configured, or modified, it is the folder where the solution file is located.

_ExecutionPathAndName_

Working directory for the solution, and solution name. 

_ProductPath_

Path where the product is installed.

_SolutionName_

Name of the solution, without path and without extension. Example: Solution1

_SolutionPath_

Path of the solution file.

_SolutionPathAndName_

Path of the solution and its name (without extension)

_Transfers_

Path of the default folder to transfers, usually the folder Transfers under the product folders in Public Documents.

_ThirdParty_

Path for the ThirdParty folder, usually located under MyDocuments/<productName>/ThirdParty

_WpfControls_

Path for the WpfControls folder, located in the product Installation folder, sub-directly WpfControls

Pre-defined Databases

There are four database connections preloaded into any new solution.

Datasets DB - Pre-defined database connections

DB

Database

Path Location

Usage

Retentive

SQLite

<ProjectNameAndPath>.dbRetentive

Stores values for Retentive Tags.

RuntimeUsers

SQLite

 <ProjectNameAndPath>.dbRuntimeUsers

Stores dynamically created Users.

AlarmHistorian

SQLite

 <ProjectNameAndPath>.dbAlarmHistorian

Stores Alarm and AuditTrail records.

TagHistorian

SQLite

<ProjectNameAndPath>.dbTagHistorian

Stores Tag Historian and Annotations.

If you need to use another database for the pre-defined connections, follow these steps below:

1. Rename or delete the previous DB. This step is necessary, as the system would not allow the creation of two objects with the same name. 

2. Create a new DB with the same name as the previous DB, with the required database and connection settings.

DB Configuration Table

The following table describes each available property you can configure when editing a DB:

Property

Description

ID

Identifies each database entry uniquely to distinguish between different database configurations.

VersionID

Specifies the version of the database configuration to track changes and manage different versions effectively.

Name

Provides a name for the database configuration to easily reference and identify it within the system.

Provider

Defines the provider technology used in the connection, such as System.Data.SQLite, which determines the specific database type and driver.

Database

Identifies the type of dataset used in this connection to ensure compatibility and proper data handling.

ConnectionString

Establishes the connection to the database by entering the server path and instance. The selected database provider defines the syntax. Uses third-party components and macros for flexibility. Utilizes the interface to define fields like Provider, Data Source, and Additional Parameters. For example, Provider=System.Data.SQLite;Data Source=_ExecutionPathAndName_.dbAlarm.

CustomOptions

Provides additional configuration options for the database connection or operations to customize behavior and performance.

LogonPassword

Provides the corresponding password for the database login. This property is only accessible by administrators to ensure security.

FilterColumns

Lists columns used to filter data during database operations to optimize queries and improve data retrieval efficiency.

ServerIP

Specifies an optional IP address or DNS name for a computer to be used as a secure gateway, in case the database is not running in the local computer, enhancing security and access control.

The TWebServices must be installed on the remote computer. 

Level

Assigns the security or access level for the database entry to manage permissions and protect sensitive data.

Category

Categorizes the database entry for organizational purposes to facilitate management and reporting.

LockState

Indicates the current lock status of the database entry to manage concurrent access and ensure data integrity.

LockOwner

Identifies the user or process holding the lock on the database entry to track changes and maintain accountability.

DateModified

Records the date and time when the database entry was last modified to maintain a history of changes and updates.

DateCreated

Records the date and time when the database entry was created to track the creation and initial configuration.

Description

Provides a brief description of the database connection to give context and additional information for reference.

Additional Connection Guides

Below you can access guides that present step-by-step instructions on how to connect to specific databases.

Execution Profile for Development

Assume you must transition the Alarm Historian configuration to a Microsoft SQL production database within your organization. During the development and testing phases of the application, you may prefer to wait to publish alarm events to that database. In other platforms, it would be necessary to manually switch connections between test and production environments or devise workarounds to handle this situation. We provide a built-in, optional feature. You can configure the Alarm Historian DB to target the production database, regardless of its current availability or intended use. Subsequently, run the solution in Test Mode, storing data in the local SQLite file <projectName>.dbAlarmHistorianTest.

When running the solution in validation mode, there is a configuration set by default that can override the connection of the pre-defined DB, using testing ones instead.

That configuration is defined in Runtime Execution Profiles.

The following table lists the database files that can be enabled to use when running in Development Mode:

In this section: