Page History

Overview

The DVs DatasetDB in the Dataset module make it easier for you to communicate with external databases, enhancing data integration and accessibilityModule is the object that allows the connection of the solution with external SQL databases.

On this page:

Table of Contents

maxLevel	3
style	none

Tip

title	Solution Examples

Datasets Examples

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)

DBs

The customization properties available on Dataset DB enable users to customize connections to external databases.

Create A New Database DB

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.
Click
Press Ok.
Configure A Database DB
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→
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.

Parameter

Property	Description
ID

Name

Enter

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

. The system will warn you if the name is not valid

to easily reference and identify it within the system.

Provider

Identifies

Defines the

Provider

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

which

the type of dataset

is

used in this connection to ensure compatibility and proper data handling.

Connection String

ConnectionString

Establishes the connection to the database by entering

Enter

the server path and instance

needed to connect with the database, the selected Database Provider

. The selected database provider defines the syntax.

It is a

Uses third-party

component, and any additional parameter supported by the provider can usually be used. It supports third-party

components and macros

.

You can use macros on the connection string. For the filename in an SQLite connection string, you can use <projectName>, which will be replaced by the solution's name, for example.

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

Logon Name

You need to provide a valid login name for the database.

Logon Password

You need to provide the password that corresponds to the database login. (Only accessible by Administrators)

Server IP

It is an optional parameter. Provide an IP

or DNS name for a computer to be used as

a Secure Gateway.

Description

Description for the database connection.

Please check the Connecting to SQL Server and Connecting to Excel for additional information.

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

Solution Test Databases Example

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 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 Validation Development Mode:

DB	Database	Path Location	Usage
Retentive	SQLite	<ProjectNameAndPath>.

dbRetentiveTest

dbRetentiveDev	Stores values for Retentive Tags.
RuntimeUsers	SQLite	<ProjectNameAndPath>.

dbRuntimeUsersTest

dbRuntimeUsersDev	Stores dynamically created Users.
AlarmHistorian	SQLite	<ProjectNameAndPath>.

dbAlarmHistorianTest

dbAlarmHistorianDev	Stores Alarm and AuditTrail records.
TagHistorian	SQLite	<ProjectNameAndPath>.

dbTagHistorianTest

dbTagHistorianDev

Stores Tag Historian and Annotations.

In this section

...

:

Page Tree

root	@parent
spaces	V10

Space shortcuts

Page tree

Versions Compared

Old Version 10

New Version Current

Key