Page History

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

Step 1: Configure Database

Connection

1. Navigate to Datasets → DBs
2. Add database:

1. Click Plus icon to add database

Option A: Use Default SQLite

Name: ProductionDB
Provider: System.Data.SQLite
Database: SQLite
ConnectionString: [Auto-configured]

Option B: SQL Server

Name: ProductionDB

Provider:

SqlClient

Connection String: Use builder or:

 System.Data.SqlClient
Database: SQLServer
ConnectionString: 
  Data Source=SERVER\INSTANCE;
  Initial Catalog=Production;
  Integrated Security=True

MySQL:

• Name: MySQLData
• Provider: MySQL
• Connection String:

  Server=localhost;Database=scada;Uid=user;Pwd=password;

1. Test connection Click Test → Should show "Connected"

---

Step 2: Create Simple Query

1. Go to Datasets → Queries
2. Add query:

1. Click Plus icon
2. Configure:
   • Name: ActiveOrders

1. • DB: ProductionDB

2. Click OK
3. Double-click SqlStatement to edit:

sql

  SELECT 
    OrderID,
    ProductName,
    Quantity,
    DueDate,
    Status
  FROM Orders
  WHERE Status = 'Active'
  ORDER BY DueDate

• Test Query → View results

1. Save and test query

Step 3: Add Dynamic Parameters

Create query with tag parameters:

Production by Date:

Name:

1. Create new query: ProductionReport
2. Edit SQL with tag placeholders:

sql

  SELECT 
    ProductID,
    SUM(Quantity) as Total,
    AVG(Quality) as AvgQuality
  FROM Production
  WHERE ProductionDate BETWEEN @StartDate {{Tag.Report_StartDate}} 
  AND @EndDate{{Tag.Report_EndDate}}
    AND Line = @LineNumber
  {{Tag.Selected_Line}}
GROUP BY ProductID

Parameters:

• @StartDate: DateTime
• @EndDate: DateTime
• @LineNumber: Integer

Link Query to Tags

Bind parameters to tags:

1. Select parameter
2. Link to tag:
   • @StartDate → Report_StartDate
   • @EndDate → Report_EndDate
   • @LineNumber → Selected_Line

The {{Tag.Name}} syntax embeds tag values at runtime.

Step 4: Display Query Results

1. Open Displays → MainpageDraw
2. Add DataGrid control
3. ConfigureSet properties:
   • Data SourceDataSource: @Dataset.Query.ActiveOrders
   • Auto-refreshAutoRefresh: 30 seconds
   • Allow sortingAllowSorting: Yes
   • Allow filtering: Yes

Create Stored Procedure Call

1. Datasets → Queries
2. Add stored procedure:

Call SP:

• Name: UpdateInventory
• Type: Stored Procedure
• SQL:

sql

  EXEC sp_UpdateInventory 
    @ProductID = @ProductID,
    @Quantity = @Quantity,
    @Location = @Location

Write to Database

1. • True
2. Run solution (F5)
3. Verify data appears in grid

Step 5: Execute from Script

Create button to run query:

1. Add button to display
2. Double-click for code:

csharp

public void RefreshData_Click(object sender, EventArgs e)
{
    // Set parameters
    @Tag.Report_StartDate = DateTime.Today;
    @Tag.Report_EndDate = DateTime.Now;
    @Tag.Selected_Line = 1;
    
    // Execute query
    @Dataset.Query.ProductionReport.Select = true;
    
    // Get results
    DataTable results = @Dataset.Query.ProductionReport.SelectCommand();
    
    // Process data
    if (results.Rows.Count > 0)
    {
        @Tag.TotalProduction = results.Compute("SUM(Total)", "");
    }
}

Step 6: Write Data to Database

Create insert query:

1. New query: LogEvent
2. SQL statement

1. :

sql

INSERT INTO EventLog 
(Timestamp, EventType, Description, UserName, TagValue)
VALUES 
(GETDATE(), @EventType{{Tag.EventType}}, @Description, @User, @Value{{Tag.Description}}, 
 {{Tag.UserName}}, {{Tag.Value}})

1. Execute from script:

csharp

@Dataset.Query.LogEvent.Execute(
    "@EventType",// Set values
@Tag.EventType = "Alarm",;
    "@Description",@Tag.Description = "High Temperature",;
@Tag.UserName =   "@User", @Client.UserName,;
@Tag.Value = @Tag.TankFarm/Tank1/Temp;

//  "@Value", @Tag.Temperature
);

Create Data Table

1. Navigate to Datasets → Tables
2. Create table mapping:

Recipe Table:

• Name: Recipes
• Database: ProductionDB
• Table: RecipeData
• Key Column: RecipeID
• Auto-load: On startup

Access in scripts:

csharp

DataRow recipe = @Dataset.Table.Recipes.SelectRow("RecipeID=101");
@Tag.Recipe_Name = recipe["Name"];
@Tag.Recipe_Temp = recipe["Temperature"];

Schedule Query Execution

Create scheduled task:

Execute insert
@Dataset.Query.LogEvent.ExecuteCommand();

Step 7: Use Stored Procedures

1. Create query: UpdateInventory
2. SQL statement:

sql

EXEC sp_UpdateInventory 
  @ProductID = {{Tag.ProductID}},
  @Quantity = {{Tag.Quantity}},
  @Location = '{{Tag.Location}}'

1. Execute:

csharp

@Tag.ProductID = 101;
@Tag.Quantity = 50;
@Tag.Location = "Warehouse-A";

@Dataset.Query.UpdateInventory.ExecuteCommand();

Step 8: Schedule Automatic Updates

Create scheduled task:

1. Go to Scripts → Tasks
2. Create new task: UpdateMetrics
3. Trigger: Server.Minute
4. Code:

csharp

public void UpdateProductionMetrics()
{
    // Run query every hourminute
    if @Dataset.Query.ProductionReport.Execute();
    
    // Store results in tags(@Server.Minute % 5 == 0) // Every 5 minutes
    {
    DataTable  dt = @Dataset.Query.ProductionReport.ResultData;Select = true;
        
        // Log execution
        @Tag.TotalProductionLastUpdate = dt.Compute("SUM(Total)", "");
}DateTime.Now;
    }
}

Step 9: Test Your Configuration

1. Start runtime (F5)
2. Open Datasets → Monitor
3. Verify:
   • Queries show "Success" status
   • Row counts update
   • No errors in LastStatusMessage
4. Check DataGrid displays:
   • Data refreshes automatically
   • Sorting works
   • Values update with tags

Next Steps

• [Create DashboardsDataGrid Control] - Display SQL dataAdvanced grid features
• [Reports Module] - Database Generate database reports
• [Enrich Scripts & Data & ScriptsEnrichment] - Process query results

In this section...