Stored procedures

Stored procedures are supported on every backend. Instead of positional parameters, you call them through a typed C# contract: input, output and return values map to a plain class, and results are typed, including multiple result sets.

Declare the procedure#

Annotate a class with [StoredProcInfo] and the procedure's name, and describe each parameter with [ParamInfo]. Output and return values use ParamDirection. A result set is described by a row class whose properties carry [ColumnInfo]:

using System.Data;
using Kiwify.Kiwi.ORM.Core.DbObjects;

[DbContextObject("Sales")]      // registered as a stored procedure because of [StoredProcInfo]
[StoredProcInfo("get_customer_orders")]
public class GetCustomerOrders
{
    [ParamInfo("customer_id")]
    public int CustomerId { get; set; }                                   // input

    [ParamInfo("total_orders", ParamDirection = ParameterDirection.Output)]
    public int? TotalOrders { get; set; }                                 // output

    // Result set 1: read it with ExecuteRead<OrderRow>()
    public class OrderRow
    {
        [ColumnInfo("orderid")]  public int OrderId { get; set; }
        [ColumnInfo("total")]    public decimal Total { get; set; }
    }
}

You rarely write these by hand: kiwi orm generate produces them from the database, including parameter directions and result-set row classes. Non-default schemas become part of the call name (schema.procedure). See Generate entities and migrations.

Call it#

var cmd = db.StoredProc<GetCustomerOrders>(p => p.CustomerId = 42);

List<GetCustomerOrders.OrderRow> orders = cmd.ExecuteRead<GetCustomerOrders.OrderRow>();
int? total = cmd.ProcObject!.TotalOrders;     // output value mapped back onto the object

Choose the call that matches what the procedure returns:

var (orders, items) = db.StoredProc<GetCustomerOrders>(p => p.CustomerId = 42)
                        .ExecuteReadMultiResults<OrderRow, ItemRow>();   // one list per result set, in order

Async variants are available for the same operations.

Passing an existing object#

StoredProc<T>(T storedProc) accepts an already populated instance, which is convenient when the same parameter object is reused.

Notes#

• Stored procedure calls take part in the current transaction and, in the Enterprise edition, in the audit trail. See Transactions.
• Procedures are described in your context with [DbContextObject], like entities, so they are registered with AddDbObjects(...).