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