Overview
The DatasetDB in the Dataset Module is the object that allows the connection of the solution with external SQL database.
On this page:
DatabaseDB Configuration
Utilities
There are two utilities you can access through buttons in this page.
- SQLite Admin: Simple configuration tools for SQL databases.
- Visual QueryBuilder: Opens our tool for building SQL queries interactively. Learn more at Visual SQL Query Builder.
Creating a New DB (Database connection)
To create a new DB connection, follow the steps below:
Access Datasets → DBs.
Click the plus icon.
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.
Press Ok.
ConnectionString example for Microsoft SQL Express:
- DataSource: .\SQLEXPRESS
- Initial Catalog: myDatabase (or the name of your database)
When using SQLite databases, theDataset 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.
Any user can create new connections in the solution. However, only the Administrator can configure database login passwords.
Configuring the ConnectionString
To configure a DB, follow the steps below.
Access Datasets→ DBs.
Double-click the property you wish to edit on the row corresponding to the channel you want to modify.
Edit the property field.
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 needed to use another database for the pre-defined connections, follow these steps below:
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.
- 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:
If a column is not visible on the grid, enable it by right-clicking the grid header and selecting it from the list.
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
In the documentation in can access guides presenting step-by-step how to connect to specific databases. Below, you find the existing guides:
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 confirmed 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:
DB | Database | Path Location | Usage |
|---|---|---|---|
Retentive | SQLite | <ProjectNameAndPath>.dbRetentiveDev | Stores values for Retentive Tags. |
RuntimeUsers | SQLite | <ProjectNameAndPath>.dbRuntimeUsersDev | Stores dynamically created Users. |
AlarmHistorian | SQLite | <ProjectNameAndPath>.dbAlarmHistorianDev | Stores Alarm and AuditTrail records. |
TagHistorian | SQLite | <ProjectNameAndPath>.dbTagHistorianDev | Stores Tag Historian and Annotations. |
In this section: