Datasets Tables (Reference)

Datasets → Tutorial | Concept | How-to Guide | Reference

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

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();
}

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();

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

Performance Tips

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

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

Performance issues:

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

In this section...