External Databases

While API integrations are preferred, legacy environments often require direct SQL access to external databases. Struktural provides a specialized service for this purpose, bypassing Entity Framework and operating via raw ADO.NET connections.

Using IExternalDatabaseService

This service is exposed within the C# Scripting sandbox through the ServiceProvider. It supports dynamic query execution and schema introspection.

var extDb = ServiceProvider.GetService<IExternalDatabaseService>();

// The connection string should idealy be fetched from the Secret Manager
var connString = await Secrets.GetSecretAsync("LegacySqlServerDb");

var sql = "SELECT EmployeeId, Salary, Department FROM HumanResources.Payroll WHERE Status = 'Active'";

// Execute the query
var results = await extDb.ExecuteSqlAsync(
    provider: "SQLServer", 
    connectionString: connString, 
    schema: "HumanResources", 
    sql: sql, 
    obfuscationRules: new Dictionary<string, string>(), // See Data Masking below
    authMode: "Native"
);

// Process the results (IEnumerable<IDictionary<string, object?>>)
foreach (var row in results)
{
    Log($"Loaded Employee: {row["EmployeeId"]}");
}

On-The-Fly Data Masking (Obfuscation)

When fetching data from highly secure legacy systems to display within Struktural (e.g., inside a Virtual Entity Grid), you may need to mask sensitive columns. The ExecuteSqlAsync method accepts a dictionary of obfuscation rules.

The engine parses the projected SQL alias and replaces the physical data with the masking string before the data ever enters the Struktural application memory space.

var maskingRules = new Dictionary<string, string>
{
    { "Salary", "****" },
    { "SocialSecurityNumber", "XXX-XX-XXXX" }
};

var results = await extDb.ExecuteSqlAsync(..., sql, maskingRules, ...);

In this scenario, the user will see "****" in the Salary column instead of the actual numeric value.