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. Click Plus icon to add database

Option A: Use Default SQLite

Name: ProductionDB
Provider: System.Data.SQLiteSQLite Data Provider
Database: .Net Framework Data Provider for SQLite
ConnectionString: [Auto-configured]

Option B: SQL Server

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

1. Click Test → Should show "Connected"

1. OK"

2. Create Simple Query

1. Go to Datasets → Queries
2. Click Plus icon
3. Configure:
   • Name: ActiveOrders

2. Click OK
3. Change the DB column to TagHistorian
4. 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. 1. SELECT "TankFarm/Tank1/Temperature" FROM ProcessData

   
   

   ---

   3. Add Dynamic Parameters

   1. Go to Datasets → Queries
   2. Double-click SqlStatement to edit
   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. 1. SELECT "TankFarm/Tank1/Temperature" FROM ProcessData WHERE UTCTimestamp_Ticks >= (strftime('%s', {Tag.TankFarm/Tank1/Temperature.Timestamp}, '-10 days') * 10000000) + 621355968000000000
      2. 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. Double-click and go to Settings
      4. Set properties:
         • DataSource: @Dataset.Query.ActiveOrdersAutoRefresh: 30 seconds
         • AllowSorting: True
      5. Run solution (F5)Run solution 
      6. 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: RunScript
      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
      title 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
      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

   5. [DataGrid Control] - Advanced grid features
   6. [Reports Module] - Generate database reports

   Excerpt Include
   Datasets Module Datasets Module nopanel true

   [Scripts & Data Enrichment] - Process query results

In this section...