Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Datasets SQL Query (Tutorial) teaches you to:

  • Configure database connections
  • Create queries with parameters
  • Display data in tables
  • Schedule automated queries

Prerequisites:

In this page:

Table of Contents
maxLevel2
minLevel2
indent10px
excludeSteps
stylenone




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



Step

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

Option B: SQL Server

Name: ProductionDB
Provider: SqlClient System.Data.SqlClient Provider
Database: SQL Server SQLServerDatabase
ConnectionString: 
  Data Source=SERVER\INSTANCE;
  Initial Catalog=Production;
 (your Integrated Security=Truedatabase name);

  1. Click Test → Should show "Connected"
Step 2:


Info
titleInfo

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 Tutorials



2. Create Simple Query

  1. Go to Datasets → Queries
  2. Click Plus icon
  3. Configure:
    • Name: ActiveOrders
    • DB:
ProductionDB
    • TagHistorian
  1. Click OK
  2. Double-click SqlStatement to edit:

      sql

      SELECT 
        OrderID,
        ProductName,
        Quantity,
        DueDate,
        Status
      FROM Orders
      WHERE Status = 'Active'
      ORDER BY DueDate
      1. Save and test query

      Step 3: Add Dynamic Parameters

      Create query with tag parameters:

        1. SELECT TankFarm/Tank1/Temperature FROM ProcessData



      3. Add Dynamic Parameters

      1. Go to Datasets → Queries
      2. Double-click SqlStatement to edit
      3. Create new query: ProductionReport
      4. Edit SQL with tag placeholders:

          sql

          SELECT 
            ProductID,
            SUM(Quantity) as Total,
            AVG(Quality) as AvgQuality
          FROM Production
          WHERE ProductionDate BETWEEN {{Tag.Report_StartDate}} 
            AND {{Tag.Report_EndDate}}
            AND Line = {{Tag.Selected_Line}}
          GROUP BY ProductID

          The {{Tag.Name}} syntax embeds tag values at runtime.

            1. SELECT TankFarm/Tank1/Temperature FROM ProcessData WHERE UTCTimestamp_Ticks >= (strftime('%s', {Tag.TankFarm/Tank1/Temperature.Timestamp}, '-10 days') * 10000000) + 621355968000000000
          1. It is important to remember that historian data is in UTCTicks, thats why the time calculation must to be done



          4.

          Step 4:

          Display Query Results

          1. Open Displays → Draw
          2. Add DataGrid control
          3. Set properties:
            • DataSource: @Dataset.Query.ActiveOrdersAutoRefresh: 30 seconds
            • AllowSorting: True
          4. Run solution (F5)Run solution 
          5. Verify data appears in grid
          Step



          5

          :

          . Execute from Script

          Create button to run query:

          1. Add button to display
          2. Double-click for codeand go to Dynamics:

          csharp

          public void RefreshData_Click(object sender, EventArgs e)
          {
              // Set parameters
              @Tag.Report_StartDate = DateTime.Today;
              @Tag.Report_EndDate = DateTime.Now;
              @Tag.Selected_Line = 1;
              
              // Execute query
              @Dataset.Query.ProductionReport.Select = true;
              
              // Get results
              DataTable results = @Dataset.Query.ProductionReport.SelectCommand();
              
              // Process data
              if (results.Rows.Count > 0)
              {
                  @Tag.TotalProduction = results.Compute("SUM(Total)", "");
              }
          }

          Step 6: Write Data to Database

          Create insert query:

          1. New query: LogEvent
          2. SQL statement:

          sql

          INSERT INTO EventLog 
          (Timestamp, EventType, Description, UserName, TagValue)
          VALUES 
          (GETDATE(), {{Tag.EventType}}, {{Tag.Description}}, 
           {{Tag.UserName}}, {{Tag.Value}})
          1. Execute from script:

          csharp

          // Set values
          @Tag.EventType = "Alarm";
          @Tag.Description = "High Temperature";
          @Tag.UserName = @Client.UserName;
          @Tag.Value = @Tag.TankFarm/Tank1/Temp;
          
          // Execute insert
          @Dataset.Query.LogEvent.ExecuteCommand();

          Step 7: Use Stored Procedures

          1. Create query: UpdateInventory
          2. SQL statement:

          sql

          EXEC sp_UpdateInventory 
            @ProductID = {{Tag.ProductID}},
            @Quantity = {{Tag.Quantity}},
            @Location = '{{Tag.Location}}'
          1. Execute:

          csharp

          @Tag.ProductID = 101;
          @Tag.Quantity = 50;
          @Tag.Location = "Warehouse-A";
          
          @Dataset.Query.UpdateInventory.ExecuteCommand();

          Step 8: Schedule Automatic Updates

          Create scheduled task:

          1. Go to Scripts → Tasks
          2. Create new task: UpdateMetrics
          3. Trigger: Server.Minute
          4. Code:

          csharp

          public void UpdateProductionMetrics()
          {
              // Run every minute
              if (@Server.Minute % 5 == 0) // Every 5 minutes
              {
                  @Dataset.Query.ProductionReport.Select = true;
                  
                  // Log execution
                  @Tag.LastUpdate = DateTime.Now;
              }
          }

          Step 9: Test Your Configuration

          1. Check Action and configure:
            1. Event: MouseLeftButtonDown
            2. Action: RuScript
          2. Click New
          3. The CodeBehind display will automatically open
          4. Add the following script:

           csharp

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

          {

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

          }


          Info
          titleDebug

          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

          [Scripts & Data Enrichment] - Process query results
          1. Start runtime
          2. Open Datasets → Datasets Monitor
          3. Verify:
            • LastStatus
            • RowCcount
          4. Start runtime (F5)
          5. Open Datasets → Monitor
          6. Verify:
            • Queries show "Success" status
            • Row counts update
            • No errors in LastStatusMessage
          7. Check DataGrid displays:
            • Data refreshes automatically
            • Sorting works
            • Values update with tags

          Next Steps

        1. [DataGrid Control] - Advanced grid features
        2. [Reports Module] - Generate database reports



      In this section...

      Page Tree
      root@parent
      spaces93DRAF