Dataset SQL Query (Tutorial)

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

Option B: SQL Server

Name: ProductionDB
Provider: System.Data.SqlClient
Database: SQLServer
ConnectionString: 
  Data Source=SERVER\INSTANCE;
  Initial Catalog=Production;
  Integrated Security=True

1. Click Test → Should show "Connected"

Step 2: Create Simple Query

1. Go to Datasets → Queries
2. Click Plus icon
3. Configure:
   • Name: ActiveOrders
   • DB: ProductionDB
4. Click OK
5. 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. Create new query: ProductionReport
2. 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.

Step 4: Display Query Results

1. Open Displays → Draw
2. Add DataGrid control
3. Set properties:
   • DataSource: @Dataset.Query.ActiveOrders
   • AutoRefresh: 30 seconds
   • AllowSorting: True
4. Run solution (F5)
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 code:

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. Start runtime (F5)
2. Open Datasets → Monitor
3. Verify:
   • Queries show "Success" status
   • Row counts update
   • No errors in LastStatusMessage
4. Check DataGrid displays:
   • Data refreshes automatically
   • Sorting works
   • Values update with tags

Next Steps

• [DataGrid Control] - Advanced grid features
• [Reports Module] - Generate database reports
• [Scripts & Data Enrichment] - Process query results

In this section...