Page History

Perform direct table-level operations with automatic CRUD, tag mapping, and access control.

Reference → Modules → Datasets → UI → DBs | Queries | Query Editor | Tables | Files | Monitor

Configuration Properties

Creating Tables

1. Navigate to Datasets → Tables
2. Click Plus icon
3. Configure:
   • Name: Unique identifier (use underscores)
   • Description: Documentation
4. Click OK
5. Set properties:
   • DB: Select database
   • TableName: Target table
   • Access: Permission level

Access Levels

WhereCondition Configuration

Filter data using SQL WHERE syntax:

Static Conditions

sql

Status = 1
Department = 'Production'
Timestamp > '2024-01-01'

Dynamic with Tags

sql

BatchID = {Tag.Production/Batch_ID}
Temperature > {Tag.TankFarm/Tank1/Temp}
Operator = '{Tag.Current_Operator}'

Complex Conditions

sql

Status IN (1, 2, 3) 
  AND Department = '{Tag.Department}'
  AND Timestamp > {Tag.StartDate}

Tag Mapping

Configuration

1. Click Mapping ellipsis (...)
2. Assign columns to tags:

Mapping Behavior

• First row mapped on SELECT
• All rows available via DataTable
• Bidirectional for updates
• Auto-refresh on changes

Tag Domain Mapping

Tag mapping occurs in the original call domain:

Client-Initiated Calls:

• From displays or client scripts
• Mapping uses client domain
• Results visible to specific client
• Isolated from other clients

Server-Initiated Calls:

• From server scripts or tasks
• Mapping uses server domain
• Results visible project-wide
• Shared across all clients

Domain Selection Strategy

Execution Methods

Select Operations

csharp

// Trigger select
@Dataset.Table.ProductionTable.Select = true;

// Get all results
DataTable data = @Dataset.Table.ProductionTable.SelectCommand();

// Check row count
int rows = @Dataset.Table.ProductionTable.RowCount;

Insert Operations

csharp

// Set values in mapped tags
@Tag.ProductName = "Widget-A";
@Tag.Quantity = 100;
@Tag.Status = 1;

// Execute insert
@Dataset.Table.ProductionTable.Insert = true;

Update Operations

csharp

// Modify mapped tags
@Tag.Quantity = 150;
@Tag.Status = 2;

// Execute update (uses WhereCondition)
@Dataset.Table.ProductionTable.Update = true;

Delete Operations

csharp

// Set identifying values
@Tag.RecordID = 12345;

// Execute delete (uses WhereCondition)
@Dataset.Table.ProductionTable.Delete = true;

Advanced Operations

Batch Processing

csharp

public void BatchUpdate()
{
    DataTable table = @Dataset.Table.MyTable.SelectCommand();
    
    foreach(DataRow row in table.Rows)
    {
        row["Status"] = "Processed";
        row["ProcessDate"] = DateTime.Now;
    }
    
    // Update all modified rows
    @Dataset.Table.MyTable.UpdateCommand(table);
}

Dynamic WhereCondition

csharp

// Change filter at runtime
@Dataset.Table.MyTable.WhereCondition = 
    string.Format("Date = '{0}'", DateTime.Today);

// Execute with new filter
@Dataset.Table.MyTable.Select = true;

Transaction Support

csharp

try
{
    @Dataset.BeginTransaction();
    
    @Dataset.Table.Table1.Insert = true;
    @Dataset.Table.Table2.Update = true;
    
    @Dataset.CommitTransaction();
}
catch
{
    @Dataset.RollbackTransaction();
}

DataTable Usage Patterns

Pattern 1: Direct Script Processing

csharp

DataTable result = @Dataset.Query.Query1.SelectCommand();
// Process data locally without server domain impact
foreach(DataRow row in result.Rows) {
    // Local processing
}

Pattern 2: Tag Distribution

csharp

// Assign to DataTable tag for module sharing
@Tag.MyDataTable = @Dataset.Query.Query1.SelectCommand();
// Now available to displays, reports, etc.

Pattern 3: Mapped Navigation

csharp

// Configure mapping, then navigate rows
@Dataset.Query.Query1.Select();
@Dataset.Query.Query1.Next(); // Moves to next row

DateTime Handling

MappingDateTime Options

Example Configuration

csharp

// Store as UTC, display as local
@Dataset.Table.EventLog.MappingDateTime = "UTC";

// Tag receives UTC, display converts
@Tag.EventTime = // Automatically converted

Display Integration

DataGrid Binding

xml

<DataGrid DataSource="{Dataset.Table.ProductionTable}" />

ComboBox Population

csharp

// Fill combo with table data
@Display.ComboBox.DataSource = 
    @Dataset.Table.LookupTable.SelectCommand();

Performance Optimization

Memory & Traffic Optimization

Control Data Volume:

• Always use WHERE clauses to filter results
• Implement pagination for large datasets
• Use SELECT specific columns instead of SELECT *
• Consider indexed views for complex queries

Resource Planning:

Small Query: < 1,000 rows = Minimal impact
Medium Query: 1,000-10,000 rows = Monitor memory usage
Large Query: > 10,000 rows = Implement pagination

Optimize WhereCondition

sql

-- Use indexed columns
ID = {Tag.ID}  -- Good if ID is indexed

-- Avoid functions on columns
YEAR(Date) = 2024  -- Bad
Date >= '2024-01-01' AND Date < '2025-01-01'  -- Good

Batch Operations

csharp

// Instead of multiple single updates
for(int i = 0; i < 100; i++)
{
    @Tag.ID = i;
    @Dataset.Table.MyTable.Update = true;  // Slow
}

// Use batch update
DataTable batch = PrepareData();
@Dataset.Table.MyTable.UpdateCommand(batch);  // Fast

Best Practices Checklist

•  Use WhereCondition - Filter at database level
•  Set appropriate Access - Minimum required permissions
•  Map key columns - For updates and deletes
•  Handle nulls - Check before operations ?
•  Use transactions - For related updates
•  Limit result sets - Add TOP/LIMIT in WhereCondition
•  Index columns - Used in WhereCondition
•  Use appropriate domains - Client for user, Server for shared
•  Pool connections - Reuse database connections

Troubleshooting

No data returned:

• Check WhereCondition syntax
• Verify table exists
• Confirm permissions
• Test in SQL tool

Insert/Update fails:

• Check Access level
• Verify required columns
• Review data types
• Check constraints

Mapping not working:

• Confirm column names
• Check tag types match
• Verify tag exists
• Review case sensitivity
• Verify domain selection
• Check tag existence
• Review mapping configuration
• Confirm execution context

Performance issues:

• Add database indexes
• Limit WhereCondition scope
• Use pagination
• Check network latency
• Use asynchronous execution
• Check execution plan

Related Documentation

• Datasets DBs (Reference)
• Datasets Queries (Reference)
• Datasets Engine (Reference)

In this section...

Tables

To create and configure a Database Table follow these steps:

1. Access Datasets / Tables.
2. Click the plus icon.

3. Enter a Name and Description for the table.

4. Click Ok.
   

To edit a Dataset Table, double-click the property you wish to edit on the row corresponding to the table you want to modify on the data grid. The table below describes each available property.

Column

Description

Name

The Table's name inside the plataform solution.

DB

Defines the database connected to the Table.

Table Name

The Table's name in the Database you want to access.

WhereCondition

Specify the parameters that will filter the data using SQL syntax. For example: "ColumnName = {tag.tagInt}".

Access

Defines the access permissions for the table.

Mapping

Click the button with three dots (...) to select the tags that you want to populate with data in the first row of the table with data from specific columns.

MappingDateTime

Specify how the time in Database will be interpreted (UTC or Local), when mapping to DateTimeOffset .NET variables, or to Tags in the application using the Mapping column.

Description

Table's description.

Datasets Table Columns

In this section: