This section presents information about Datasets and SQL.
What is the Dataset Module
The Dataset Module enables connecting to an existing external database. Different providers can be used — such as SQL Server, Oracle, SQLite, PostgreSQL, and others —, and can be simply and quickly configured.
The Dataset Module has many features specifically created for real-time applications.
- Allows concurrent connections, using multi-threading, with many data sources.
- Supports various database technologies, including ADO.NET, ODBC, OleDB, and native interfaces with key Databases in the market.
- Built-in editor for SQLite. Easily create SQLite databases using macros like <ProjectName>-Test.db.
- Visual Query Builder to create and edit queries.
- Easily adds real-time tags embedded in the query strings.
- Manages file and recipes in ASCII, Unicode or XML files.
- Built-in Networking Gateway feature, allows to safely cross security zones.
- Virtualizes the names for queries and tables, creating application that are agnostic to the storage location.
Key Concepts
Dataset DBs
To each external database the Module Database will communicate, a connection needs to be created with certain parameters. Each connection, created on Datasets → DBs is called in this Module as Dataset DB.
Dataset Queries
In the context of this module, when we refer to a Dataset Query, we mean not only the SQL query string, but the Project object that has a logical name, the SQL query related to that logical name, and other parameters as defined on Datasets → Queries. There are many ways to automatically map the result of query execution with Tags.
Dataset Tables
Similar to the queries, a Dataset Table refers to a logical name, created in the project, to setup the access to a specific Table in a connected database. The tables in use are listed on Datasets → Tables. The Tags in the real-time database can easily be mapped to columns in the tables to insert, update or read operations.
Dataset Files
A Dataset File is a logical name that defines parameter to read and write from files in ASCII, Unicode or XML formats.
Configuration Workflow
The typical configuration workflow for the Dataset Module has the following sequence:
Dataset Module Configuration Workflow | ||
|---|---|---|
Action | Where | Comments |
Create the required database connections (DBs) | Datasets → DBs | Collect the information to connect with the databases required to your Project. Use the built-in SQLite database as a temporary development tool if one of your connected database is not available yet. The virtualization model with logical names for queries and tables will make your project work directly with the new connection with the production database, without having to change anything on the Project Configuration other than that database connection, |
Prepare the Queries the Project uses | Datasets → Queries | Either using the Visual Query Editor, or getting the query string from IT or plant facilitator, collect and create the logical names |
Modify the Query to add real-time tags | Datasets → Queries | Easily modify the query with the parameters that need be connected with real-time values. For instance, a query that has the text. |
Prepare the Tables the Project uses | Datasets → Tables | When you need to Insert or Modify data, you need to access the Database Table directly. In some cases, all the information you need is one table, so there is no needing to create a Query. You can easily connect the contents what are inserted in the table with Tags in the Project. |
Configure the Stored Procedures | Datasets → Queries | The Module Database can execute Stored Procedures; just define it using the same interface for the queries. |
Configure data exchange with Files | Datasets → Files | If necessary to exchange values of Tags with plain text or XML files, set that configuration. |
Use your Dataset logical objects | All Project | The logical object names created for Queries, Tables and Files can be used in any part of the project. Examples: Script calculation, Display visualization, and others |
Creating DB Connections
When using SQLite databases, the Module Dataset can automatically create the Database if necessary; for other ones, the Database itself must already exist before you set your connection.
Users with any Permission groups can create new connections in the Project, but only the Administrator can configure databases password logins.
| See Security, Users and Roles for information on Project permissions. |
To create a new Database connection:
- Go to Datasets → DBs.
- Click Create New. The Create New Database Connection window displays.
Enter or select information, as needed.
Click OK. The database is added as a new row in the table.
Edit the row fields to modify the required settings.
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. You use macros on the connection string. 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 | Optionally, an IP or DNS name for a computer to be used as a Secure Gateway. |
Description | Enter a description for the database connection. |
Customizing Pre-defined Databases
There are four database connection already created in any new Project:
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. |
Any of the them can be customized to any type of database.
The selection of best storage location depends on all kind of factors, from internal company procedures to the volume of data and how the data shall be used. Therefore, that is decision to each Project according to its requirements.
If needed to use another database for the pre-defined connections, execute the following steps:
- Rename or Delete the previous DB. This step is necessary, as the system would not allow to create two objects with the same name.
- Crate a new DB with the same name of the previous DB, with the required Database and connection strings.
- That is all!
ConnectionString example for SQL Express
- Data Source: The server path and instance that will have the databases.
- Initial Catalog: The name of the database that will be used.
Additional Settings for Tag Historian and AlarmHistorian
- Store and Forward: Enabling this option will cause the system to store the data locally if communication with the database is lost, and forward the data to synchronize once the connection is back again.
| For more configuration about Store and Forward, check the section Archiving Process at Historian, Time Series Data. |
Project Test Databases
As explained on Execute, Runtime Environment document, it is possible to put a Project configuration to execute either in Test Mode or Startup Mode.
The Test Mode is an execution environment specifically created to simply develop and test of Projects.
When running the Project in Test Mode, there is a configuration, which is true by default, that can override the connection of the pre-defined DB, using testing ones instead.
Those are database files that can be enabled to use when running in Test Mode:
Database files used when running in Test Mode | |||
|---|---|---|---|
DB | Database | Path Location | Usage |
Retentive | SQLite | <ProjectNameAndPath>.dbRetentiveTest | Stores values for Retentive Tags. |
RuntimeUsers | SQLite | <ProjectNameAndPath>.dbRuntimeUsersTest | Stores dynamically created Users. |
AlarmHistorian | SQLite | <ProjectNameAndPath>.dbAlarmHistorianTest | Stores Alarm and AuditTrail records. |
TagHistorian | SQLite | <ProjectNameAndPath>.dbTagHistorianTest | Stores Tag Historian and Annotations. |
Use Case
Let us say you replace the AlarmHistorian configuration to Microsoft SQL production database in the company.
When Developing and Testing the application you do not want to publish Alarm events in that database yet.
With other platforms, you have to keep changing manually the connections from Test to Production, or embed workarounds to deal with it.
In our framework, that is an optional built-in feature: define the AlarmHistorian DB to point to Production database, event that is not available yet or that you do not want to use yet, and Run the Project in Test Mode, storing data in the local SQLite file <projectName>.dbAlarmHistorianTest
Dataset Tables Configuration
To configure database tables:
- Go to Datasets → Tables.
- Enter the field values as needed.
Dataset Table Configuration Properties | |
|---|---|
Field / Column | Description |
Name | Enter a name for the table configuration. The system lets you know if the name is not valid. |
DB | Select the database connecton. |
TableName | Select or type the table name in the Database you want to access |
WhereCondition | Specify the parameters that will filter the data using SQL syntax. E.g. " |
Access | Select the access permissions for the table. |
Mapping | Click "..." to select the tags that you want to populate with data in the first row of the table with data from specific columns. |
MappingDateTime | Select the time reference (UTC or Local). |
Description | Enter a description for the table configuration. |
Dataset Queries Configuration
You can configure queries to perform more advanced functions with SQL statements to work with data from external databases.
To configure Dataset queries:
- Go to Datasets → Queries.
Enter the field values as needed.
Dataset Query Configuration Properties | |
|---|---|
Column | Description |
Name | Enter a name for the query. The system allows you to know if the name is not valid. |
DB | Select the database configuration. |
SqlStatement | Enter the query using SQL syntax. |
Mapping | Click "..." to select the tags that you want to populate with data from specific columns returned by the query. |
MappingDateTime | Select the time reference (UTC or Local). |
Description | Enter a description for the table configuration. |
Dataset Files Configuration
To configure dataset files:
- Go to Datasets → Files.
- Enter the field values as needed.
Dataset File Configuration Properties | |
|---|---|
Column | Description |
Name | Enter a name for the file configuration. The system allows you to know if the name is not valid. |
FileName | Enter the full path to the file. The file path can have Tag values embedded using curly brackets syntax. E.g.: When executing, the area in curly brackets is replaced by the value of the Tag. |
FileType | Select the type of file. |
Objects | Click "..." to select the tags that you want to populate with data from the file with data from specific columns. |
Description | Enter a description for the file configuration. |
XMLSchemaType | Represents the schema type of an XML file, which can be: a TagList, XML that contains a tag list with the tag name and tag value; or a TagObject, XML that contains the entire tag tree and its children. |
Using Dataset Objects
The created Dataset object, Dataset Tables, Dataset Queries, Dataset Files, can be used by the other Modules in any part of your Project.
Some examples:
DataGrid on Displays
When used in conjunction with graphical Displays, the execution of the queries are automatic. For instance, when showing data on a DataGrid, it will execute the query automatically when opening the display.
| More information about this at DataGrid component. |
Saving the result of a query to a Tag or .NET variable
Let us say your application has a Tag of type DataTable named Test, a Dataset Query with name Query1, you can populate that Tag executing:
@Tag.Test = @Dataset.Query.Query1.SelectComand() |
Executing Stored Procedures
Both Queries and Stored Procedure are define at the Datasets → Queries table.
In order to execute the Stores Procedure, use the ExecuteCommand() method.
E.g.: Dataset.Queries.Query1.ExecuteCommand()
When passing parameters, you can use the syntax @null@ to pass a null as parameter. See example below:
Exec TestOutput @return_Value = {Tag.ReturnValue} RETURN_VALUE, @vcrPrefix = @null@, @intNextNumber = {Tag.NextNumber} OUTPUT, @vcrFullLicense = {Tag.NextLicense} OUTPUT |
The Dataset Namespace
The Dataset namespace exposes properties and methods of the .NET objects used by the Dataset Module execution.
This section describes only some commonly used properties.
| For the full list properties and methods, go to the Namespaces Reference. |
Examples:
Dataset Module Properties examples | ||
|---|---|---|
Property | Type | Description |
| Boolean | Flag indicating if the Module Dataset has started. |
| String | Message OK or error when initiating the Module. |
| DataTable | Executes the Query1 return a DataTable object the values. |
| Dataset Objects methods examples | ||
|---|---|---|
| Method | ReturnType | Description |
Dataset.Query.Query1.SelectCommand() | DataTable | Executes the Query1 return a DataTable object the values. |
Dataset.Table.Table1.SelectCommand() | DataTable | Executes a Select command on the Dataset Table Table1. |
Dataset.File.File1.SaveCommand() | Integer | Save the file configured at Edit→ Dataset → Files→ File1. |