Page History

Execution Process and Databases Connections

The Dataset module, specifically the "TRunModule.exe (Dataset)" process, reads all project configurations relevant to this module but does not directly interface with the databases. Database access services are provided by TServer, with the Dataset module consuming these services.

The execution of the Dataset module, i.e., executions of configurations made in Queries, Tables, and Files, runs in an independent process "TRunModule.exe (Dataset)" that reads all the project configurations related to this module but does not directly execute the interface with the databases. Within TServer, there are services to access the database, and connections and calls to the database occur in TServer. The Dataset module merely consumes this bank access service from TServer

Connections with the Database.

For each configured DB, there's only a single access connection created for this database, meaning that there's only one execution thread for each DB. If parallel execution of commands to the same database is needed, it's possible to create more than one DB pointing to the same database.

For each configured database (DB), there's only one access connection. This signifies a single execution thread per DB. However, for parallel execution of commands to the same database, additional DBs can be configured, all pointing to the same database.

The Dataset module is a vital component of the system, functioning independently via the process "TRunModule.exe (Dataset)". It's important to understand that this module does not directly interact with the databases, but rather, it reads project configurations related to the module and utilizes services within TServer to access databases. As a result, all database connections and calls occur in TServer.

In terms of database connections, for each database configured, there's a single access connection created. This design follows a one-thread-per-DB approach. However, should the need arise for parallel command execution to the same database, you have the option to create more than one DB pointing to the same database.

Database Interactions: Synchronous, Asynchronous and

Distributed Access

Synchronous and Asynchronous Access

The Dataset module provides two key methods for executing data requests: Synchronous and Asynchronous. Understanding the differences between these methods, their default behaviors, and when to use each one is crucial for optimizing performance and avoiding potential bottlenecks in your project.

Asynchronous execution is generally more performant and can help avoid potential locks. When properties in both Tables and Queries, such as the "Select" property,

are triggered, the execution becomes Asynchronous.

After the trigger

, the execution

continues normally

while in parallel,

the property

is propagated from the trigger point (be it a screen or a script) to the server

. It then

synchronizes with the Dataset module

, which calls upon the necessary

services from TServer to interface with the database. Upon TServer's

return

with the execution of the Select for the Dataset module, the result

is updated in the properties and attributes of the module

. It then undergoes mapping treatment with tags

and is subsequently made available to the entire project.

On the other hand, certain methods execute Synchronously. For example, when calling the SelectCommand method, the execution

is paused at this point until the Dataset module

carries out the service call

to TServer

, which interfaces with the database

and returns the result to the Dataset module. The Dataset module then

performs tag mapping

and

returns the result

, allowing the execution to resume.

It's important to

exercise caution when making synchronous calls within mono-threaded code-behind screens

, whether they are WPF or HTML5

. While synchronous execution can be appropriate in certain cases, overuse can lead to performance issues. Mechanisms like

the Script task or Action Server Request can

facilitate asynchronous execution without

the need to use

attributes,

particularly useful when the result needs to be handled even after an asynchronous execution

-------------------

The system provides both synchronous and asynchronous methods within Tables and Queries. A trigger in the properties, such as the "Select" property, results in asynchronous execution. This means the execution proceeds normally post-trigger, and in parallel, this property is propagated from its point of origin (screen or script) to the server. It is then synchronized with the Dataset module, which in turn calls the necessary TServer's bank access service. Once the TServer returns with the Select execution for the Dataset module, the module will update the properties and attributes with the result, perform a mapping treatment with tags, and make the result available to the entire project.

In contrast, the system's available methods execute synchronously. For example, calling the SelectCommand method will lock the execution at this method until the Dataset module executes the TServer service call, which interfaces with the database to return the result to the Dataset module. The Dataset module then processes the mapping with tags and provides the result in the form of the method's return, allowing the execution to continue.

This distinction is crucial when considering synchronous calls within code-behind screens, which are mono-thread, whether they are WPF or HTML5 screens. Mechanisms like Script task or Action Server Request can help create an asynchronous execution without needing to use the attributes, especially when there's a need to handle the result even after an asynchronous execution.

---------------

The properties in Tables and Queries can trigger execution, such as triggering the "Select" property, which makes the execution asynchronous. This means that after the trigger in the property, the execution continues normally. In parallel, this property propagates from its trigger source (screen or script) to the server, then synchronizes with the Dataset module. The module then makes necessary calls to TServer's bank access service. After TServer's return with the Select execution for the Dataset module, the result gets updated in the module's properties and attributes, is processed for tag mapping, and is finally made available to the entire project.

Contrarily, available methods execute synchronously. For instance, calling the SelectCommand method locks the execution at this method while the Dataset module executes the call of the TServer service, interfaces with the database, and returns the result to the Dataset module. The Dataset module then processes the tag mapping and provides the result in the form of the method's return, allowing the continuation of the execution.

Special caution is required with synchronous calls within code-behind screens (mono-thread, WPF or HTML5 screens). Mechanisms like using Script task or Action Server Request can help create an asynchronous execution without needing to use attributes, when there's a need to handle the result even after an asynchronous execution.

Usage of Client-Type Tags in the Dataset Module's Tag Mapping

It's possible to use client-type tags in the Dataset module's tag mapping. When executing a synchronous or asynchronous command, the assignment in the tags will be executed by the Dataset module (not by TServer) in the original domain of the call. Therefore, if the call was executed on a client (screen or client script), after the return of the result from TServer to the Dataset module, the mapping will be executed between received values and tags, and Client domain Tags can be used in the mapping. It's important to understand and consider if the project's need is an execution that makes the value available to the entire project, in this case using server domain tags, or only for the client's scope, in this case using client domain tags.

----------------

Client-type tags can be used in the Dataset module's tag mapping. During execution of a synchronous or asynchronous command, the assignment in the tags gets executed by the Dataset module (not by TServer) in the original domain of the call. Hence, if the call was executed on a client (screen or client script), after the result's return from TServer to the Dataset module, the mapping between received values and tags gets executed. Client domain Tags can be used in the mapping, which allows for specific control over the scope of availability of the value - either for the entire project (using server domain tags) or just for the client's scope (using client domain tags).

---------

Tags Domain Client can be used in the Dataset module's tag mapping. When executing a synchronous or asynchronous command, the assignment in the tags is executed by the Dataset module (not by TServer) in the original domain of the call. This design allows for the use of client-type tags in the Dataset module's tag mapping. When a call is executed on a client (screen or client script), the mapping is executed between received values and tags after the return of the result from TServer to the Dataset module. Depending on the project's needs, you can use server domain tags for execution that makes the value available to the entire project, or client domain tags for client's scope only.

Attributes and Server Domain Properties (Concurrency Management)

Attributes and Server Domain Properties (concurrence management)
It's important to know and remember that the module's attributes are all server domain. Therefore, if there are multiple Tasks or clients using the same attributes, there may be competition and confusion in execution. For instance, a client sets the SQLStatement of a query, and at the same time, a Task also sets this SQLStatement with a different value from the client. During the command execution (for example, a select – triggering the Select property which is also a server domain), the last value in the SQLStatement is what will be used in the operation.

-----------

All module attributes are server domain. Therefore, if multiple Tasks or clients are using the same attributes, there might be competition and confusion in execution. For example, if a client and a Task both set the SQLStatement of a query simultaneously with different values, the last value in the SQLStatement will be used in the operation.

--

You must remember that all the module's attributes are server domain. This can lead to competition and confusion in execution when multiple Tasks or clients are using the same attributes. For instance, a client and a Task may set the SQLStatement of a query simultaneously but with different values. During command execution, the last value in the SQLStatement is what will be used in the operation.

Use of Tags of type DataTable

It's important to know and remember that the module's attributes are all server domain. Therefore, if there are multiple Tasks or clients using the same attributes, there may be competition and confusion in execution. For instance, a client sets the SQLStatement of a query, and at the same time, a Task also sets this SQLStatement with a different value from the client. During the command execution (for example, a select – triggering the Select property which is also a server domain), the last value in the SQLStatement is what will be used in the operation.

----------

The return of Select methods is a .Net DataTable object. This can be manipulated directly in the script or assigned to a Tag of the DataTable type, allowing the result to propagate to other modules like clients or reports.

The Select methods return a .Net DataTable object. If you're not using mapping to assign each value from a specific row's columns to Tags, you can manipulate this returned dataTable directly in the script. Alternatively, you can assign it to a Tag of the DataTable type, which allows the result to propagate to other modules, like clients or reports, and be easily presented in a dataGrid, PieChart, or even for XY chart presentation using the CallBack GetSamples.

(Meint TK methods to Convert on Datatable and Array of Templates)

Dataset Module Properties

As mentioned, there are several server domain properties within the Dataset module. There is a property called AsyncContents, which is a .Net DataTable type. When the Select property is triggered, initiating an asynchronous execution, after the return, the values of each column of the 1st row are assigned to the tags that were configured in the Mapping. When the Next property is triggered, the Dataset module navigates to the next row and assigns the column values to the tags, even those configured in the Mapping. The table used for this navigation is stored in the AsyncContents property and is updated only when the select is executed. It's possible to modify the CursosIndex to jump or go back to a specific position in the row navigation.
For synchronous commands, the method returns the dataTable, but it's also stored in the LocalContents property; it will be overwritten with the result of each executed command.

-------------

As mentioned, there are several server domain properties within the Dataset module. There is a property called AsyncContents, which is a .Net DataTable type. When the Select property is triggered, initiating an asynchronous execution, after the return, the values of each column of the 1st row are assigned to the tags that were configured in the Mapping. When the Next property is triggered, the Dataset module navigates to the next row and assigns the column values to the tags, even those configured in the Mapping. The table used for this navigation is stored in the AsyncContents property and is updated only when the select is executed. It's possible to modify the CursosIndex to jump or go back to a specific position in the row navigation. For synchronous commands, the method returns the dataTable, but it's also stored in the LocalContents property; it will be overwritten with the result of each executed command.

-----------

There are several server domain properties within the Dataset module, including a .Net DataTable type property called AsyncContents. When the Select property is triggered, initiating an asynchronous execution, the column values of the first row are assigned to the tags configured in the Mapping. The table used for this navigation is stored in the AsyncContents property and is

====================================================================

Navigation Through AsyncContents Property

The AsyncContents property is used for navigation through the DataTable. If Select is triggered again while AsyncContents still contains data, the Dataset module navigates to the next row, and the column values are assigned to the tags configured in the Mapping. This navigation continues until the last row. After the last row, if Select is triggered again, the module will request a new execution to TServer.

Use of Other Asynchronous Properties

Other asynchronous properties like AsyncFirst, AsyncNext, AsyncPrevious, and AsyncLast can be used to navigate through the rows of AsyncContents without triggering a new Select. They can also be used to reset the navigation (AsyncFirst) or to navigate to a specific row (AsyncLast).

Control over Data Navigation

A property named AsyncEOF (End of File) indicates if the navigation has reached the end of the AsyncContents. This property is set to true when the last row is navigated. In addition, a property named AsyncBOF (Beginning of File) indicates if the navigation is at the beginning of the AsyncContents. Both properties can be used to control the data navigation.

Refreshing the AsyncContents

The AsyncRefresh property can be used to refresh the AsyncContents. When triggered, it requests a new Select execution to TServer, replacing the current AsyncContents.

Adjustments to Asynchronous Behavior

If there's a need to adjust the asynchronous behavior, the MaxRows property can be used. It defines the maximum number of rows to be returned in each Select execution, which can be used to limit the number of rows in the AsyncContents.

Synchronous and Asynchronous Execution in Scripts and Actions

Scripts and Actions can be executed synchronously or asynchronously. Using the attribute "Async" followed by the method name will make the execution asynchronous, similar to the behavior of the properties. If the method name is used without the "Async" prefix, the execution will be synchronous.

Asynchronous Execution in Screens

The "Async" prefix can also be used in the screens, making the execution asynchronous. This allows the screen to remain responsive while the execution is taking place.

Impact on Tag Mapping

The way of execution, whether synchronous or asynchronous, affects the Tag Mapping. For synchronous execution, the tags are assigned after the method's return. For asynchronous execution, the tags are assigned during the asynchronous execution, and the method's return is a boolean, indicating if the execution was successfully triggered.

Handling Asynchronous Execution Results

The handling of the result after an asynchronous execution can be done using the AsyncContents property, the Mapping, and the AsyncReturn tag. The AsyncReturn tag is set to true when the asynchronous execution is successful, and to false otherwise. This tag can be used to check the status of the execution.

OnAfterAsyncReturn Event

The OnAfterAsyncReturn event is triggered after the return of an asynchronous execution. This event is executed on the server but can be used to trigger a client script or action, which allows handling the result on the client side.

has completed.

Client vs. Server Tags in Mapping Requests and Results

In the Dataset Module, it's possible to utilize tags from the client domain during tag mapping. This capability provides additional flexibility when managing data in your project.

When executing a synchronous or asynchronous command, the Dataset module, not the TServer, carries out the tag assignment within the original domain of the call. This means that if a call is initiated from a client (e.g., a screen or client script), the mapping operation between the received values and the tags occurs after TServer returns the result to the Dataset module. In this scenario, client domain tags can be used in the mapping process.

The choice of tag domain is important for controlling the scope of data availability. If you wish the data to be available project-wide, server domain tags are the appropriate choice. However, if the data's scope should be confined to the client that initiated the call, client domain tags would be the best fit.

This detailed understanding of the functionality of client and server domain tags in the Dataset Module's tag mapping process can enhance your project's data management efficiency and effectiveness. It's crucial to select the correct tag domain based on your project's specific requirements, as it significantly impacts data accessibility and overall project performance.

Managing Data and Concurrency within the Dataset Module

Utilizing DataTable Type Tags 

The Dataset Module's Select methods return a .Net DataTable object. This object can be directly manipulated in the script or assigned to a tag of the DataTable type, enabling its use across different modules like clients or reports.

In scenarios where mapping is not employed to assign specific row column values to Tags, the returned DataTable can be directly manipulated within the script. Alternatively, by assigning the DataTable object to a DataTable type tag, the results can be propagated to other modules. This allows the data to be conveniently presented in data grids, pie charts, or even XY chart presentations via the CallBack GetSamples method.

Furthermore, Scripts provide methods (TK namespace) that facilitate the conversion between an Array of Template Tags and DataTables, and vice versa, or even to .NET objects. Certain graphical elements, such as DataGrids, handle the data source similarly, whether it's a DataTable type tag, the result of a Dataset.SQL.Query command, or an array of Template Tags.

Understanding and efficiently utilizing this aspect of the Dataset Module is pivotal for effective query result management and distribution across your project.

Server Domain Attributes in Dataset Module 

It's crucial to understand that all the attributes within the Dataset Module are server domain. Consequently, when multiple tasks or clients interact with the same attributes, there can be competition, which may cause confusion during execution.

Consider a scenario where a client and a Task are setting the SQLStatement of a query simultaneously, each providing a different value. Given that these attributes are in the server domain, the last value set in the SQLStatement will be the one used during command execution. This rule applies even when triggering a property that is also in the server domain, such as the Select property.

Understanding this aspect of the Dataset Module is essential for managing concurrent operations effectively. It helps avoid potential conflicts and ensures the correct value is used in operations, contributing to more predictable execution and reliable outcomes.

Understanding Dataset Module Properties

The Dataset module consists of numerous server domain properties, including AsyncContents, a .Net DataTable type property. Upon triggering the Select property, which initiates an asynchronous execution, the values from each column of the first row are assigned to the tags configured in the Mapping.

When the Next property is triggered, the Dataset module navigates to the succeeding row, assigning the column values to the respective tags as per the Mapping configuration. The DataTable used for this navigation process is stored in the AsyncContents property, which is updated each time a Select command is executed.

Furthermore, the CursorIndex property can be manipulated to jump to or reverse to a specific position in the row navigation, providing enhanced control over data traversal.

For synchronous commands, the dataTable is returned by the method and simultaneously stored in the LocalContents property. Each execution of a synchronous command overwrites the LocalContents property with its result. It's important to note this behavior to ensure data integrity during synchronous operations.

Understanding these properties and their functions is crucial for effective use of the Dataset module in managing data operations in your project.

Data Management: Store & Forward, Traffic Volume, and SQL Syntax

Store & Forward Considerations

Store & Forward built-in functionality is strictly applicable to databases used for storing Historian and Alarm data. This feature relies on specific control columns found in the schemas of Historian and Alarm, which are not present in generic databases. For comprehensive details on this functionality, please refer to the Store&Forward section in the Historian and Alarm modules' documentation.

Managing Data Traffic Volume

The Dataset>Table's WhereCondition property, much like the Dataset>Query's SQLStatement property, defines the return condition when executing a select command in the database. Consequently, the volume of returned data can vary dramatically - from no row to the entire table (in the absence of a filter or a WhereCondition). There are no software-imposed limitations on the size of the returned DataTable, but larger tables require more allocated memory and prolong the traffic time between the TServer, Dataset module, and the final destination. Be mindful of the scalability of these tables and ensure your hardware can support the anticipated data volume.

Adhering to Database-Specific SQL Syntax

The Dataset>Queries' SQLStatement property enables the execution of a complete SQL statement, which means SQL syntax specific to each database can be used

Store&Forward

Only banks used to store Historian and Alarm can have the Store&Forward functionality enabled because this feature requires the use of some control columns that exist in the schema of Historian and Alarm, and not in generic banks. Please refer to the Store&Forward section of the Historian and Alarm modules for more details.

--

Store&Forward functionality is only applicable to databases used to store Historian and Alarm, as this feature relies on specific control columns present in the schema of Historian and Alarm, but not in generic databases. For further details, please refer to the Store&Forward section of the Historian and Alarm modules.

Data Traffic Volume Between TServer, Dataset Module, and Other Modules

When a select is executed in the bank, the WhereCondition property of the Dataset>Table defines the return condition just like the SQLStatement property of the Dataset>Query. Therefore, the volume of data that can be returned can vary from no row to the entire table (when there's no filter or a WhereCondition). There is no software-side protection concerning the size of the return DataTable, and obviously, the larger the table size, the more allocated memory, the longer the traffic time of this table between TServer and the Dataset module, and then its final destination. Care should be taken with the scalability of these tables' size, as well as having suitable hardware to support the expected data volume.

Specific Syntax Used in the SQLStatement of Database

The SQLStatement property of Dataset>Queries allows for the full SQL statement to be executed, therefore it's possible to use SQL syntax specific to each bank. For example, SQLite supports the LIMIT clause, which in while SQLServer is called uses TOP. Just as care must be taken with the separators of Similarly, different separators are used for table and column names , some use [] others "". As much as possible, the software will try across databases. While the software attempts to normalize and use the correct separators when using the WhereCondition and filters on objects with the like alarm objectobjects, but in SQLStatement, it's up to the user's responsibility to use the correct syntax.syntax.

Security Measures: Preventing SQL Injection

Preventing

SQL Injection Attacks

In When calling Stored Procedures in the database, calling Stored Procedure, there is 's a great significant concern about this part of "SQL injection, " because especially if parameters are passed as plain text in SQL Statement, the "injection" could be possible. Against statements. To mitigate this, we use utilize the .Net API where parameters are added to a list, making it impossible to effectively preventing code injection. Therefore, it's advisable to use we advise using parameters in the Stored Procedures Procedure calls following with the following syntax: 
"execute <StoredProcedureName> <@paramName1>={Tag.Tag1}, <@paramName2>="{Tag.TagStr}", <@paramName3>={Tag.OutValue} OUTPUT, ... , @RETURN_VALUE={Tag.RetValue} RETURNVALUE"
By using this syntax, you effectively parameterize the This approach parameterizes Stored Procedure calls and prevent guards against SQL injection attacks. This way, each Each parameter value is strictly interpreted strictly as a value and not part of the SQL command. This practice is fundamental to the security of your application, preventing unauthorized manipulation of the database.

Using ServerIP to Access Remote Database Providers

, providing a critical layer of security against unauthorized database manipulation.

Network GatewayAccess and Time Zone Handling

Using ServerIP for Network Gateway Database Access

In some scenarios, certain databases might not be accessible from all computers due network topology or security constrains. The ServerIP column can There are times when certain databases are not accessible from any computer. By using the ServerIP column, it's possible to redirect database access commands to a computer that has the required permissions. The prerequisite for this is that the software should also be machine with the necessary permissions, provided that our software is installed on that computermachine. In this scenariosuch cases, only TWebServer would be running. This system is responsible for interfacing with the databaseactive, handling database interaction, receiving commands, establishing connections, etcand so forth. This method provides offers a workaround for situations where database access is restricted to specific machines.

Handling Time Zones

All Date/Time type tags in the our software are treated as UTC. ThereforeConsequently, the dates that are written to and read in from the database are also in UTC. If you want to read/write a date in a local timezone, you should consider local timezone dates are to be read/written, account for the time difference when preparing the SQL statement or when using the WhereCondition property.

Dataset Module Performance

The Dataset module's performance depends on many factors, including database performance, network latency, and the complexity of the SQL queries being executed. The software itself is designed to minimize overhead and execute queries as efficiently as possible, but ultimately the performance of the Dataset module is tied to these external factors. It's important to design your database schema and queries with performance in mind, and consider investing in high-quality hardware and network infrastructure to ensure optimal performance.

Error Handling

Error handling in the Dataset module is straightforward. If an error occurs during the execution of a command, the module's Error property (LastStatus) will be updated with the error message. This property can be monitored to handle errors in your application. Furthermore, if an error occurs during the execution of a synchronous method, the method will return an empty DataTable and the Error property will be updated or you can call methods like SelectCommandWithStatus where the status will be an output parameter in the method.

These are some of the key points you should keep in mind while using the Dataset module. It's a powerful tool that provides a lot of flexibility, but it also requires careful use and understanding of its features and limitations. Always keep best practices in mind when designing and implementing your database interactions, and don't hesitate to seek expert advice if you're unsure about anything.

In this section...