Page History

Configure database connections and execute queries.

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

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

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: SQLite Data Provider
Database: .Net Framework Data Provider for SQLite
ConnectionString: [Auto-configured]

Option B: SQL Server

Name: ProductionDB

Provider:

SqlClient

Connection String: Use builder or:

 SqlClient Data Provider
Database: SQL Server Database
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;

 (your database name);

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

Info
title Info
These tables will not be used further in this Tutorial, once there is no data available. If you have a database table with data, feel free to use, if not you can use the Table created on the Historian Module Tutorial

---

2. Create Simple Query

1. Go to Datasets → Queries

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

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

1. Click OK
2. Change the DB column to TagHistorian
3. Double-click SqlStatement to edit:
   1. SELECT "TankFarm/Tank1/Temperature" FROM ProcessData

3. Add Dynamic Parameters

1. Go to Datasets → Queries
2. Double-click SqlStatement to edit
3. Edit SQL with tag placeholders:
   1. SELECT "TankFarm/Tank1/Temperature" FROM ProcessData WHERE UTCTimestamp_Ticks >= (strftime('%s', {Tag.TankFarm/Tank1/Temperature.Timestamp}, '-10 days') * 10000000) + 621355968000000000
4. It is important to remember that historian data is in UTCTicks, thats why the time calculation must to be done

4. Display Query Results

1. Open Displays → MainpageDraw
2. Add DataGrid control
3. Double-click and go to Settings
4. Set propertiesConfigure:
   • Data SourceDataSource: @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

1. Run solution 
2. Verify data appears in grid

5. Execute from Script

Create button to run query:

1. Add button to display
2. Double-click and go to Dynamics:
3. Check Action and configure:
   1. Event: MouseLeftButtonDown
   2. Action: RunScript
4. Click New
5. The CodeBehind display will automatically open
6. Add the following script:

 csharp

public void MouseLeftButtonDown1(object sender, System.Windows.Input.InputEventArgs e)

{

DataTable results = @Dataset.Query.ActiveOrders.SelectCommand();

}


6. Test Your Configuration

1. Start runtime
2. Open Datasets → Datasets Monitor
3. Verify:
   • LastStatus
   • RowCount
   • No errors in LastStatusMessage
4. Check DataGrid displays:
   • Data refreshes automatically

In this section...