Configure database connections and execute queries.

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

This Tutorial Teaches you to:

  • Configure database connections

  • Create queries with parameters

  • Display data in tables

Prerequisites:

1. Configure Database Connection

  1. Navigate to Datasets → DBs
  2. 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 (your database name);
  1. Click Test → Should show "OK"


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
  2. Click Plus icon
  3. Configure:
    • Name: ActiveOrders
  4. Click OK
  5. Change the DB column to TagHistorian
  6. 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 → Draw
  2. Add DataGrid control
  3. Double-click and go to Settings
  4. Set properties:
    • DataSource: @Dataset.Query.ActiveOrders
  5. Run solution 
  6. 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();

}


Debug

You can use the Debugging Scripts Code to debug this piece of code and view the values in the "results" variable


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

Explanation - to understand concepts

→ Modules / Business Operations / Datasets Module

Tutorials - to learn by doing

→ Tutorials /  Business Operations / Datasets Module Tutorial

How-to Guides - to accomplish specific tasks

→ How-to Guides / I Business Operations / Datasets Module How-to Guide

Reference - technical details

→ Technical Reference /  Business Operations / Datasets Module Reference

In this section...