Datasets SQL Query Tutorial

Configure database connections and execute queries

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

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

2. Create Simple Query

1. Go to Datasets → Queries
2. Click Plus icon
3. Configure:
   • Name: ActiveOrders
   • DB: TagHistorian
4. Click OK
5. 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. Set properties:
   • DataSource: @Dataset.Query.ActiveOrders
4. Run solution 
5. 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: RuScript
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
   • RowCcount
   • No errors in LastStatusMessage
4. Check DataGrid displays:
   • Data refreshes automatically

In this section...