Data Access Patterns¶

The Data property is your rule's gateway to everything on the current P21 window. It provides access to field values, all datawindow tables and rows, cascade control, focus management, the database connection, and the logging subsystem.

This page covers the patterns you will use in nearly every rule you write.

The Data Object¶

Data is of type IRuleData (exposed by P21.Extensions.BusinessRule). It is available as a property on your Rule base class — you do not need to instantiate it.

Key members:

Member	Type	Description
`Data.Fields`	`IFieldCollection`	Named access to the current row's fields
`Data.Set`	`DataSet`	The full window DataSet containing all datawindow tables
`Data.ConnectionString`	`string`	SQL Server connection string for direct DB queries
`Data.ResponseAttributes`	`ResponseAttributes`	Assign to show a custom dialog to the user

Reading Field Values¶

Single Field — String¶

// Returns null if the field doesn't exist or has no value
string customerId = Data.Fields["customer_id"]?.ToString();

Single Field — Typed Value¶

// Use Value<T>() for strongly-typed access
decimal unitQty = Data.Fields["unit_quantity"].Value<decimal>();
int     lineNo  = Data.Fields["line_no"].Value<int>();
bool    taxable = Data.Fields["taxable_yn"].Value<bool>();

Null-Safe Pattern¶

var field = Data.Fields["some_field"];

if (field != null && !string.IsNullOrWhiteSpace(field.ToString()))
{
    // Safe to use field.Value<T>()
    decimal value = field.Value<decimal>();
}

Iterating All Fields¶

In Validator rules, Data.Fields may contain multiple fields if the rule is assigned to multiple fields or is running in a context where several fields changed. Iterate when your logic applies generically across fields:

foreach (var field in Data.Fields)
{
    var name  = field.Name;
    var value = field.Value?.ToString();

    // Apply logic per field
}

Reading DataTable Rows (Multi-Row Mode)¶

When a Validator rule is configured in multi-row mode, or in On Demand rules, Data.Set.Tables gives you access to all rows in the window's datawindows.

Get a Table¶

var orderLines = Data.Set.Tables["oe_line"];

if (orderLines == null)
{
    // Table not loaded in this context — return gracefully
    return RuleResult.Success;
}

Iterate Rows¶

foreach (DataRow row in orderLines.Rows)
{
    string itemId   = row.Field<string>("item_id");
    decimal qty     = row.Field<decimal>("unit_quantity");
    decimal price   = row.Field<decimal>("unit_price");
    decimal extPrice = qty * price;

    // Process row...
}

Filter Out Deleted Rows¶

In P21 DataSets, rows marked for deletion (DataRowState.Deleted) are still present in the table until the DataSet is accepted. Always filter them out in aggregate calculations:

using System.Data;
using System.Linq;

decimal orderTotal = orderLines.AsEnumerable()
    .Where(r => r.RowState != DataRowState.Deleted)
    .Sum(r => r.Field<decimal>("extended_price"));

Common P21 DataWindow Names¶

Window	DataWindow Name	Primary Table
Order Entry — Header	`d_oe_hdr`	`oe_hdr`
Order Entry — Lines	`d_oe_line`	`oe_line`
Purchase Order — Header	`d_po_hdr`	`po_hdr`
Purchase Order — Lines	`d_po_line`	`po_line`
Customer	`d_customer`	`customer`
Inventory	`d_inv_mast`	`inv_mast`
Invoice Header	`d_oe_hdr_hist`	`oe_hdr_hist`
Invoice Lines	`d_oe_line_hist`	`oe_line_hist`

Table Names Are Not DataWindow Names

The DataWindow name (e.g., d_oe_line) is the PowerBuilder control name, not the SQL Server table name (oe_line). They are usually very similar but not always identical. When in doubt, check the P21 PowerBuilder source or use the rule's Log.AddAndPersist to dump Data.Set.Tables names at runtime.

Writing Field Values¶

Set via Data.Fields (Triggers P21 Field Events)¶

// Setting via Data.Fields fires the field's change event in P21.
// Use this when you want P21 to react to the change (lookups, defaulting, validation).
Data.Fields["ship_via"].Value = "UPS GND";
Data.Fields["notes"].Value = "Automatically processed";

Set via DataRow (Direct, No P21 Event)¶

// Setting via DataRow directly modifies the DataSet without firing P21 field events.
// Use this when you want to set a value without triggering downstream events.
DataRow row = Data.Set.Tables["oe_line"].Rows[0];
row.SetField("allocated_qty", 0m);
row.SetField("disposition", "H");

Choose Carefully

Data.Fields["field"].Value = x fires the full P21 field-change pipeline (lookups, other rules, UI refresh). row.SetField("field", x) bypasses all of that. Use SetField when you are making multiple controlled changes and need to avoid recursive rule triggering.

Controlling Cascade (Preventing Recursive Triggers)¶

When your rule sets field values that would themselves trigger other rules or field-change events, you can suppress that cascade:

// Disable cascade: subsequent SetField calls will not trigger further events
Data.SetCascade(false);

try
{
    foreach (DataRow row in Data.Set.Tables["oe_line"].Rows)
    {
        var qty = row.Field<decimal>("unit_quantity");
        row.SetField("allocated_qty", qty > 0 ? qty : 0m);
    }
}
finally
{
    // Always re-enable cascade — failure to do so affects all subsequent processing
    Data.SetCascade(true);
}

Always Re-Enable Cascade

Wrap SetCascade(false) calls in a try/finally block. If your rule throws an exception while cascade is disabled, P21's field-change pipeline remains disabled for that window session, causing erratic behavior.

Setting Field Update Order¶

When your rule needs P21 to process multiple field changes in a specific sequence (because later fields depend on earlier ones), use SetFieldUpdateOrder:

// Ensure item_id is processed first (triggers item lookup),
// then unit_quantity (validated against available stock),
// then unit_price (may be recalculated based on quantity breaks)
Data.SetFieldUpdateOrder(new[] { "item_id", "unit_quantity", "unit_price" });

Setting Focus¶

After your rule completes, you can direct the user's cursor to a specific field:

// Move focus to the customer_id field when the rule finishes
Data.SetFocus("customer_id");

This is particularly useful in Validator rules that reject a value — return RuleResult.Error("...") and then set focus back to the offending field so the user knows where to correct the problem.

Adding New Rows¶

// Insert a new row into a datawindow
Data.AddNewRow("oe_line");

// The new row is appended to the end of the table
var lines  = Data.Set.Tables["oe_line"];
var newRow = lines.Rows[lines.Rows.Count - 1]; // C# index-from-end: lines.Rows[^1]

// Populate the new row — use SetField to avoid triggering events on each field
Data.SetCascade(false);
try
{
    newRow.SetField("item_id",        "FREIGHT");
    newRow.SetField("item_desc",      "Freight Charge");
    newRow.SetField("unit_quantity",  1m);
    newRow.SetField("unit_price",     freightCharge);
    newRow.SetField("extended_price", freightCharge);
}
finally
{
    Data.SetCascade(true);
}

Sequential Multi-Row Updates¶

When your rule needs to modify every row in a datawindow and wants each modification to pass through P21's normal update pipeline (so field events fire per row), use UpdateByOrderCoded:

// UpdateByOrderCoded processes each row through P21's update mechanism.
// Use this when the changes must fire field events (e.g., recalculating extended price
// after changing unit_price requires P21 to recompute the total).
Data.UpdateByOrderCoded("oe_line", rowIndex =>
{
    var row = Data.Set.Tables["oe_line"].Rows[rowIndex];
    var qty = row.Field<decimal>("unit_quantity");

    // Example: zero out allocation for lines with no quantity
    row.SetField("allocated_qty", qty > 0 ? qty : 0m);
});

UpdateByOrderCoded vs. Direct SetField Loop¶

Approach	P21 Events Fire Per Row	Performance	Use When
`UpdateByOrderCoded`	Yes	Slower	Modifications must trigger P21 recalculations per row
Direct `SetField` loop	No	Faster	Simple field stamping with no downstream dependencies

Direct Database Access¶

When your rule needs data from outside the current window — a related record, a lookup table, or an aggregate query — use Data.ConnectionString to open a direct SQL Server connection:

using System.Data.SqlClient;

// Always use parameterized queries — never string concatenation
using var conn = new SqlConnection(Data.ConnectionString);
conn.Open();

var cmd = new SqlCommand(
    @"SELECT credit_limit, credit_hold
      FROM   customer
      WHERE  customer_id = @customerId",
    conn);
cmd.Parameters.AddWithValue("@customerId", customerId);

using var reader = cmd.ExecuteReader();
if (reader.Read())
{
    decimal creditLimit = reader.GetDecimal(0);
    string  creditHold  = reader.GetString(1);
}

Scalar Query Pattern¶

var cmd = new SqlCommand(
    "SELECT SUM(extended_price) FROM oe_line WHERE order_no = @orderNo", conn);
cmd.Parameters.AddWithValue("@orderNo", orderNo);

var result = cmd.ExecuteScalar();
decimal openOrderTotal = result != DBNull.Value ? (decimal)result : 0m;

Keep DB Calls Fast

Synchronous Validator rules block the P21 UI thread. Database calls inside validators must be fast — keep them targeted and indexed. Avoid full-table scans or heavy joins in rules that fire on every field change. Consider adding covering indexes for any columns you query frequently from rules.

Use Parameters — Always

Data.ConnectionString connects to the P21 production database. Never concatenate user-supplied field values into SQL strings. Use SqlParameter for every value.

Logging¶

// Log.AddAndPersist writes a record to the P21 application log immediately
Log.AddAndPersist($"[MyRule] customer={customerId}, action={action}, result=ok");

The entry is visible in P21 under Setup → Application Log. Log entries include a timestamp, the rule name, and your message text.

Logging Best Practices¶

Log at entry and exit for rules that handle significant business events
Log the key identifier (order number, customer ID) so log entries are searchable
Do not log on every field-change event (e.g., every keystroke in a quantity field) — this generates enormous log volume
Use structured messages: [RuleName] key=value, key=value for easy parsing

Common Data Access Patterns Cheat Sheet¶

// Read a header field (single-row mode)
string customerId = Data.Fields["customer_id"]?.ToString();

// Read a typed field value
decimal qty = Data.Fields["unit_quantity"].Value<decimal>();

// Read all lines (multi-row mode)
var lines = Data.Set.Tables["oe_line"];

// Aggregate across lines
decimal total = lines.AsEnumerable()
    .Where(r => r.RowState != DataRowState.Deleted)
    .Sum(r => r.Field<decimal>("extended_price"));

// Set a field (fires P21 event)
Data.Fields["ship_via"].Value = "FEDEX";

// Set a DataRow field (no P21 event)
row.SetField("disposition", "H");

// Add a new row
Data.AddNewRow("oe_line");
var newRow = Data.Set.Tables["oe_line"].Rows[^1];

// Cascade control
Data.SetCascade(false);
try { /* bulk SetField calls */ } finally { Data.SetCascade(true); }

// Specify field processing order
Data.SetFieldUpdateOrder(new[] { "item_id", "unit_quantity", "unit_price" });

// Move focus
Data.SetFocus("customer_id");

// Direct DB query
using var conn = new SqlConnection(Data.ConnectionString);
conn.Open();
// ...

// Log
Log.AddAndPersist($"[MyRule] order={orderNo}");