Dataset SQL Query (Tutorial)

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

Configure Database DB Connection

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

SQLite:

• Name: ProductionDB

SQL Server:

• Name: ProductionDB
• Provider: SqlClient
• Connection String: Use builder or:

  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 → Should show "Connected"

Create Simple Query

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

Get Current Orders:

• Name: ActiveOrders
• Database: ProductionDB
• SQL:

sql

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

• Test Query → View results

Parameterized Query

Create query with parameters:

Production by Date:

• Name: ProductionReport
• SQL:

sql

  SELECT 
    ProductID,
    SUM(Quantity) as Total,
    AVG(Quality) as AvgQuality
  FROM Production
  WHERE ProductionDate BETWEEN @StartDate AND @EndDate
    AND Line = @LineNumber
  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

Display Query Results

1. Open Displays → Mainpage
2. Add DataGrid control
3. Configure:
   • Data Source: @Dataset.Query.ActiveOrders
   • Auto-refresh: 30 seconds
   • Allow sorting: 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

Create insert query:

sql

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

Execute from script:

csharp

@Dataset.Query.LogEvent.Execute(
    "@EventType", "Alarm",
    "@Description", "High Temperature",
    "@User", @Client.UserName,
    "@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:

csharp

public void UpdateProductionMetrics()
{
    // Run query every hour
    @Dataset.Query.ProductionReport.Execute();
    
    // Store results in tags
    DataTable dt = @Dataset.Query.ProductionReport.ResultData;
    @Tag.TotalProduction = dt.Compute("SUM(Total)", "");
}

Next Steps

• [Create Dashboards] - Display SQL data
• [Reports Module] - Database reports
• [Enrich Data & Scripts] - Process query results

In this section...