Page History

Configure database connections and execute queries.

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

1.

Prerequisites

• Database access credentials
• Basic SQL knowledge

Configure Database Connection

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

SQL Server:

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

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 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 "ConnectedOK"

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

Step 3: 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

Step 4: Link Query to Tags

Bind parameters to tags:

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

2. Click OK
3. Change the DB column to TagHistorian
4. 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 → NewDraw
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

Step 6: 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

Step 7: 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
);

Step 8: 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"];

Step 9: 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)", "");
}

Best Practices

• Use connection pooling
• Parameterize all queries (SQL injection prevention)
• Index frequently queried columns
• Limit result sets
• Handle connection failures
• Use transactions for multiple operations

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...