Datasets Module (How-to Guide)

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

Configuration Workflow

1. Define Database Connections - Configure DatasetDB objects
2. Create Queries - Build SQL statements with parameters
3. Map Tables (Optional) - Link database tables to tags
4. Configure Files (Optional) - Setup text/XML file I/O
5. Test and Display - Verify queries and show results

Step 1: Define Database Connections

Using Default SQLite Databases

Every solution includes four pre-configured SQLite databases:

Adding External Database

1. Navigate to Datasets → DBs
2. Click Plus icon
3. Configure connection:
   • Name: Database identifier
   • Provider: Connection type
   • Database: Database system
   • Description: Documentation
4. Click OK
5. Configure ConnectionString properties

Connection Examples

Testing Connection

1. After configuring, click Test button
2. Provide credentials if required
3. Verify successful connection

Step 2: Create Queries

Building a Query

1. Go to Datasets → Queries
2. Click Plus icon
3. Enter Name (no spaces, use underscores)
4. Select DB from dropdown
5. Click OK
6. Double-click SqlStatement to open editor

SQL Statement with Parameters

Use curly brackets to embed tag values:

Static Query:

sql

SELECT * FROM Products WHERE Price > 100

Dynamic Query with Tags:

sql

SELECT * FROM Products 
WHERE Price > {{Tag.MinPrice}} 
AND Category = '{{Tag.Category}}'

Query Editor Features

• Syntax highlighting
• IntelliSense support
• Parameter validation
• Test execution

Visual Query Builder

For complex queries without SQL knowledge:

1. Go to Datasets → DBs
2. Click Visual Query Builder
3. Drag tables and connect fields visually

Step 3: Map Database Tables

Creating Table Mapping

1. Navigate to Datasets → Tables
2. Click Plus icon
3. Configure:
   • Name: Table identifier
   • DB: Database connection
   • TableName: Actual database table
   • WhereCondition: Filter criteria
   • Access: Read/Insert/ReadWrite
4. Click OK

Mapping Tags to Columns

1. Select table in grid
2. Click Mapping button (...)
3. Map database columns to tags:
   • Left: Database columns
   • Right: Solution tags
4. Configure DateTimeMode (UTC/Local)

Step 4: Configure Files (Optional)

Setting Up File I/O

1. Go to Datasets → Files
2. Click Plus icon
3. Configure:
   • Name: File identifier
   • FileName: Path with optional tag values
   • FileType: ASCII/Unicode/XML
   • Objects: Tags to read/write
4. Click OK

Dynamic File Names

Use tags in file paths:

C:\Recipes\Recipe_{{Tag.RecipeID}}.xml

Step 5: Test and Display Data

Testing Queries

1. Go to DataExplorer → SQL Queries
2. Select database and query
3. Click Execute
4. Review results in grid

Displaying in Runtime

Using DataGrid Control:

1. In Displays → Draw
2. Add DataGrid control
3. Set DataSource property:

   Dataset.Query.QueryName

Using Scripts:

csharp

// Execute query
DataTable result = @Dataset.Query.MyQuery.SelectCommand();

// Process results
foreach(DataRow row in result.Rows)
{
    @Tag.Value = row["ColumnName"];
}

Runtime Execution

Synchronous vs Asynchronous

Synchronous - Blocks until complete:

csharp

DataTable dt = @Dataset.Query.MyQuery.SelectCommand();

Asynchronous - Non-blocking:

csharp

@Dataset.Query.MyQuery.ExecuteAsync();
// Check LastStatus property for completion

Store and Forward

For critical data operations:

1. Enable in database connection
2. System buffers data during connection loss
3. Automatic forward when connection restored

Common Issues

Connection Failed

• Verify server address and port
• Check firewall settings
• Confirm credentials
• Test network connectivity

Query Syntax Error

• Validate SQL syntax for your database type
• Check table and column names
• Verify data types in WHERE conditions
• Test in SQL tool first

No Data Returned

• Check WHERE conditions
• Verify table has data
• Confirm user permissions
• Review date/time filters

Performance Issues

• Add database indexes
• Limit result sets (TOP/LIMIT)
• Use stored procedures
• Enable connection pooling

Best Practices Checklist

• Use parameterized queries - Prevent SQL injection with tag parameters
• Test in DataExplorer - Verify queries before runtime use
• Handle errors - Check LastStatus property
• Limit result sets - Use WHERE and LIMIT clauses
• Document connections - Use description fields
• Secure credentials - Only admins set passwords
• Plan database schema - Design before implementation

Security Considerations

SQL Injection Prevention

• Use parameterized queries with {{Tag}} syntax
• Never concatenate user input directly
• Validate tag values before execution

Network Gateway

For restricted databases:

1. Set ServerIP in DB configuration
2. Install TWebServices on gateway machine
3. Route through secure connection

Next Steps

• [DataGrid Control →] Display query results
• [Scripts Module →] Process database data
• [Reports Module →] Generate database reports

In this section...