This tutorial provides a comprehensive guide to using the [ProjectNamespace].Data.SqlText class within your "Code" (C#/VB.NET) business rules. This utility is the key to creating powerful, database-agnostic logic that can run on any database engine supported by your application, including SQLite.

1. The Traditional SQL Business Rule

Before diving into C# or VB.NET code, consider how business logic is typically implemented using the standard Type: SQL rule.

The framework allows you to write scripts in your database's native language (e.g., T-SQL for SQL Server). The framework automatically handles parameter binding for you—you simply reference field names with the @ symbol.

Scenario: Prevent duplicate CustomerIDs when a new Customer record is inserted.

Rule Configuration (Customers.xml):

• Type: SQL
• Command Name: Insert
• Phase: Before

Script (T-SQL):

123456789101112131415-- 1. Declare a variable to hold the result
declare @count int

-- 2. Execute the query using the auto-bound parameter @CustomerID
select @count = count(*) 
from Customers 
where CustomerID = @CustomerID

-- 3. Check the logic
if @count > 0
begin
    -- 4. Interact with the UI using framework parameters
    set @BusinessRules_PreventDefault = 1
    set @Result_Focus = 'CustomerID, This Customer ID is already in use.'
end

How it works internally: When this rule runs, the application framework actually uses the SqlText class internally to execute your script! It automatically locates parameters like @CustomerID and binds them to the current field values to prevent SQL injection.

When should you use Type: SQL rules? Use this method if the following apply to your project:

• You are a database expert: You are familiar with the syntax of the programming language supported by your database engine (e.g., you know how to write T-SQL or PL/SQL).
• You are building for "Now": You do not expect to transform this business rule into another implementation. In other words, you are not developing an SRS that will be fed to a coding agent, nor do you expect to use the built-in GEN (Scaffolding) to produce custom server-side code.
• You are data-focused: You do not expect the rule to interact with the external world. It will never need to read/write files, perform calculations with complex in-memory data structures, or call external network APIs.
• You are NOT using SQLite: Your "SQL Data Source" (the Default Database) provides a rich procedural language (like SQL Server or Oracle).

2. The Purpose of the SqlText Class

The SqlText class is a utility provided by the application framework specifically for use inside Type: Code business rules. Its primary purpose is to provide a simple, secure, and database-agnostic way to execute SQL commands explicitly in your C# or VB.NET code.

• Database Agnostic: The SqlText class automatically uses the correct data provider (System.Data.SqlClient, Oracle.ManagedDataAccess.Client, System.Data.SQLite, etc.) based on your project's connection string. You write standard SQL, and SqlText handles the provider-specific details.
• Essential for SQLite: The Default Database / Provider or the new project is configured as SQLite by default, which does not have a rich procedural language like T-SQL. SqlText is the only way to execute server-side database validation and logic (e.g., SELECT COUNT(*)...) in a SQLite-based application.
• Secure: It is designed for use with parameters, which provides inherent protection against SQL injection attacks.
• Superior GEN Blueprint: As explained in the final chapter, a Type: Code rule using SqlText is a vastly superior "blueprint" for the GEN (Scaffolding) deliverable.
• Visual Studio Debugging: Unlike SQL scripts, Type: Code rules can be debugged interactively. By choosing the "Edit Code" action, you can open the rule file in Visual Studio, set a breakpoint, and run your app. This gives you the best of both worlds: the live app configuration tools of the App Studio and the powerful server-side debugging features of Visual Studio.

3. The Basics: How to Use SqlText

The SqlText class implements IDisposable, so it should always be wrapped in a using statement to ensure database connections are properly opened and closed.

C#:

12345678using (SqlText myQuery = new SqlText(
    "SELECT CompanyName FROM Customers WHERE CustomerID = @CustomerID"))
{
    // Add parameters (prevents SQL injection)
    myQuery.AddParameter("@CustomerID", "ALFKI");
    
    // ... execute the query ...
}

VB.NET:

12345678Using myQuery As SqlText = New SqlText(
    "SELECT CompanyName FROM Customers WHERE CustomerID = @CustomerID")

    ' Add parameters (prevents SQL injection)
    myQuery.AddParameter("@CustomerID", "ALFKI")
    
    ' ... execute the query ...
End Using

The class provides three main methods to execute your command:

1. ExecuteNonQuery(): For commands that do not return data, such as INSERT, UPDATE, and DELETE. It returns an int representing the number of rows affected.
2. ExecuteScalar(): For queries that return a single value, such as SELECT COUNT(*) or SELECT MAX(UnitPrice). It returns an object that you must cast to the correct type.
3. ExecuteReader(): For queries that return one or more rows. This returns a DbDataReader that you can iterate over using a while loop.

4. Example 1: Writing Data (ExecuteNonQuery)

This example shows how to write a rule that updates a record in the database.

Scenario: Create a Custom action named "RaisePrice" on the Products controller that increases a product's UnitPrice by 10%.

Rule Configuration (Products.xml):

• Type: Code
• Command Name: Custom
• Command Argument: RaisePrice

Code Business Rule (C#):

1234567891011121314151617181920212223/*
  This rule handles the "RaisePrice" action for a single product.
  The 'instance' variable is a 'ProductsModel' object.
*/
[Rule("r100")]
public void r100_RaiseProductPrice(ProductsModel instance)
{
    // 1. Calculate the new price
    decimal newPrice = (decimal)instance.UnitPrice * 1.10m;

    // 2. Define the SQL UPDATE statement
    string sql = "UPDATE \"Products\" SET UnitPrice = @NewPrice WHERE ProductID = @ProductID";
    
    // 3. Use SqlText to execute the command
    using (SqlText updateCommand = new SqlText(sql))
    {
        updateCommand.AddParameter("@NewPrice", newPrice);
        updateCommand.AddParameter("@ProductID", instance.ProductID);
        
        // 4. Execute the non-query (fire and forget)
        updateCommand.ExecuteNonQuery();
    }
}

5. Example 2: Reading a Single Value (ExecuteScalar)

This replicates the logic from the SQL example in Chapter 1 but implements it in portable C# code. This rule runs before a new customer is inserted to check if the CustomerID already exists.

Scenario: Prevent duplicate CustomerIDs when a new Customer record is inserted.

Rule Configuration (Customers.xml):

• Type: Code
• Command Name: Insert
• Phase: Before

Code Business Rule (C#):

1234567891011121314151617181920212223242526272829303132333435/*
  This rule validates a new customer before insertion.
  The 'instance' variable is a 'CustomersModel' object.
*/
[Rule("r101")]
public void r101_ValidateDuplicateCustomer(CustomersModel instance)
{
    // 1. Define the SQL query to check for existence
    string sql = "SELECT COUNT(*) FROM \"Customers\" WHERE CustomerID = @CustomerID";
    
    int existingCount = 0;

    // 2. Use SqlText to execute the scalar query
    using (SqlText checkCustomer = new SqlText(sql))
    {
        checkCustomer.AddParameter("@CustomerID", instance.CustomerID);
        
        // 3. ExecuteScalar() returns an object, so we must cast it
        object result = checkCustomer.ExecuteScalar();
        if (result != null && result != DBNull.Value)
        {
            existingCount = Convert.ToInt32(result);
        }
    }

    // 4. If the customer exists, prevent the insert and show an error
    if (existingCount > 0)
    {
        // Prevents the "Insert" command from running
        PreventDefault(); 
        
        // Sets focus to the 'CustomerID' field and displays a message
        Result.Focus("CustomerID", "This Customer ID is already in use.");
    }
}

6. Example 3: Reading Multiple Rows (ExecuteReader)

This example shows how to perform a complex calculation. This rule calculates the total sum of all line items in an Order and updates a hypothetical OrderTotal field on the Orders record.

Scenario: A custom action "RecalculateTotal" on the Orders controller.

Rule Configuration (Orders.xml):

• Type: Code
• Command Name: Custom
• Command Argument: RecalculateTotal

Code Business Rule (C#):

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647/*
  This rule calculates the sum of all "Order Details" for a given Order.
  The 'instance' variable is an 'OrdersModel' object.
*/
[Rule("r102")]
public void r102_RecalculateOrderTotal(OrdersModel instance)
{
    int orderID = instance.OrderID;
    decimal orderTotal = 0;

    // 1. Define the SQL to read all line items for this order.
    //    Field names come from OrderDetails.xml 
    string readSql = 
      "SELECT UnitPrice, Quantity, Discount FROM \"Order Details\" WHERE OrderID = @OrderID";

    // 2. Use ExecuteReader() to get multiple rows
    using (SqlText readDetails = new SqlText(readSql))
    {
        readDetails.AddParameter("@OrderID", orderID);
        
        // 3. Open the reader
        using (var reader = readDetails.ExecuteReader())
        {
            // 4. Loop over the results using reader.Read()
            while (reader.Read())
            {
                decimal unitPrice = (decimal)reader["UnitPrice"];
                short quantity = (short)reader["Quantity"];
                float discount = (float)reader["Discount"]; // 'real' maps to float

                orderTotal += unitPrice * quantity * (1 - (decimal)discount);
            }
        }
    }

    // 5. Define the SQL to update the parent 'Orders' record.
    //    (Assuming 'OrderTotal' is a custom field on the Orders table)
    string writeSql = "UPDATE \"Orders\" SET OrderTotal = @OrderTotal WHERE OrderID = @OrderID";

    // 6. Use ExecuteNonQuery() to write the new total
    using (SqlText writeTotal = new SqlText(writeSql))
    {
        writeTotal.AddParameter("@OrderTotal", orderTotal);
        writeTotal.AddParameter("@OrderID", orderID);
        writeTotal.ExecuteNonQuery();
    }
}

7. SqlText and GEN (Scaffolding): The "Code Blueprint"

For the Digital Consultant in a Box, a key strategic advantage of using Type: Code rules with SqlText is for the GEN (Scaffolding) or SRS (Software Requirements Specification) deliverables.

The SqlText ("Code") Rule: A Superior Blueprint

The SqlText C# rule is a "white box" for the Scaffolding AI. It provides a perfect, unambiguous logical blueprint.

When the AI translates the C# validation rule (Example 2) to Next.js, it can:

1. Read the C# file.
2. Identify the SELECT COUNT(*)... SQL string.
3. Identify the C# logic: if (existingCount > 0)....
4. Natively Re-implement this logic in a JavaScript API route:

12345678// AI-generated Next.js route
const countResult = await db.query(
  "SELECT COUNT(*) as count FROM Customers WHERE CustomerID = @customerID", 
  { customerID: req.body.customerID }
);
if (countResult.recordset[0].count > 0) {
  return res.status(400).json({ field: "CustomerID", message: "..." });
}

This is a clean, idiomatic translation that results in maintainable Next.js code.

The Type: SQL Rule: A "Black Box"

If you wrote the same validation as a Type: SQL rule (e.g., in T-SQL, as shown in Chapter 1), the Scaffolding AI sees an opaque "black box." It cannot understand the logic inside the SQL block because T-SQL logic is not easily portable to Node.js or Python.

Its only option is to "wrap" the entire block, which results in heavier, database-dependent code:

12345678910111213// AI-generated Next.js route (for a Type: SQL rule)
const result = await db.execute(
  "DECLARE @count... IF @count > 0... SET @Result_Focus = ...",
  { 
    customerID: req.body.customerID, 
    BusinessRules_PreventDefault: { dir: 'out' }, // Must add output params
    Result_Focus: { dir: 'out' }
  }
);

if (result.output.BusinessRules_PreventDefault) {
  return res.status(400).json({ ... });
}

Conclusion: By using Type: Code and SqlText (especially for SQLite apps), you are not only creating robust, database-agnostic rules, but you are also authoring the perfect "code blueprints" for the AI to generate high-quality, maintainable code for any GEN (Scaffolding) target.