Overview
The Database section DatasetDB in the Dataset module facilitates communication with external databases. Users create Dataset DBs within the Datasets → DBs section by configuring connection parameters. These connections enable seamless interaction with external databases, enhancing data integration and accessibilityModule is the object that allows the connection of the solution with external SQL databases.
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.
| Info |
|---|
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:
| Info |
|---|
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 |
Dataset DB Configuration Properties enable users to customize connections to external databases. Key fields include Name validation, Provider technology, and Database type. The flexible ConnectionString supports third-party components and macros, while secure access is managed through LogonName and LogonPassword. Users can optionally specify a Secure Gateway via ServerIP, and a Description field provides additional context.
Resources
Dataset DB Configuration Properties
Column
Description
Name
Enter a name for the database configuration. The system allows you to know if the name is not valid.
Provider
Identifies the Provider technology used in this connection
Database
Identifies to which type of dataset is this connection
ConnectionString
Enter the information needed to connect with the database. The syntax is defined by the Database Provider used, it is a third party component, any additional parameter supported by the provider can be used normally.
You use macros on the connection string too.
Example: for the filename in a SQLite connection string, use <ProjectName> that is replaced by the name of the project.
LogonName
Enter a valid login name for the database.
LogonPassword
Enter the password that corresponds to the database login. (Only accessible by Administrators)
ServerIP
or DNS name for a computer to be used as a |
Description
Enter a description for the database connection.
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
Please check the Connecting to SQL Server and Connecting to Excel for additional information.
ConnectionString example for SQL Express
Project Test Databases and Example
Assume you need to transition the AlarmHistorian configuration to a Microsoft SQL production database within your organization. During the development and testing phases of the application, you may prefer not to wait to publish alarm events to that database yet. In other platforms, it would be necessary to manually switch connections between test and production environments or devise workarounds to handle this situation. In our framework, we We provide a built-in, optional feature: . You can configure the AlarmHistorian Alarm Historian DB to target the production database, regardless of its current availability or intended use. Subsequently, run the project solution in Test Mode, storing data in the local SQLite file <projectName>.dbAlarmHistorianTest.
When running the Project solution in Validation Modevalidation mode, there is a configuration , which is true 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 Those are database files that can be enabled to use when running in Validation Development Mode:
Database files used when running in Validation 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
...:
| Page Tree | ||||
|---|---|---|---|---|
|