Mise Custom SQL and Procedures

Custom SQL and procedures

Use Sql when a complete statement needs syntax outside the fluent builder. Interpolation still binds values and inserts only constant fragments through :raw. Use Procedure for an ADO.NET stored-procedure command.

Execute a complete SELECT

var query = new PostgreSqlQueryBuilder()
    .Sql(
        $"""
        SELECT {CourseTbl.CourseIdCol:raw}, {CourseTbl.TitleCol:raw}, {CourseTbl.CapacityCol:raw}
        FROM {CourseTbl.Table:raw}
        WHERE {CourseTbl.CapacityCol:raw} >= {minimumCapacity}
        """
    );

var courses = await reader.ListAsync<CourseRow>(query, cancellationToken);

The literal SQL is trusted syntax, and minimumCapacity remains a parameter. Give the result columns the names required by the mapped row.

Custom SQL is a complete statement mode. Do not combine it with fluent SELECT, FROM, predicates, CTEs, joins, ordering, or paging. Compilation rejects those mixes. Write the complete statement in Sql when choosing this mode.

Execute a complete write

var command = new PostgreSqlCommandBuilder()
    .Sql(
        $"""
        UPDATE {CourseTbl.Table:raw}
        SET capacity = capacity + {extraPlaces}
        WHERE {CourseTbl.CourseIdCol:raw} = {courseId}
        """
    )
    .Timeout(15);

var updated = await writer.ExecuteAsync(command, cancellationToken);

This command can use the same writer transaction as fluent writes. It cannot also select INSERT, UPDATE, DELETE, or procedure mode through their builder methods.

Call a stored procedure

var command = new SqlServerCommandBuilder()
    .Procedure("catalog.CloseCourse")
    .ProcedureParameter("@CourseId", courseId, DbType.Int32)
    .Timeout(30);

var affected = await writer.ExecuteAsync(command, cancellationToken);

Procedure selects CommandType.StoredProcedure; the name is a procedure identifier, not an interpolated SQL body. The database and provider must support this ADO.NET mode. PostgreSQL function or procedure syntax can instead require a suitable SQL statement for your provider.

ProcedureParameter adds a named input value and optional DbType. Names must be unique, and the method is valid only in procedure mode. The builder does not expose output-parameter directions or a separate procedure return-value parameter.

A procedure returning rows can use ReturningListAsync<T> or ReturningFirstOrNotFoundAsync<T> with a compatible mapped row. Those terminals read the result set; they do not require a fluent RETURNING clause. Provider-reported affected counts can be negative when a meaningful row count is unavailable.

Timeouts and reader behavior

Command builders support Timeout(seconds) with a nonnegative value. The configured value is passed to ADO.NET. The provider defines the meaning of zero and its default when no timeout is set.

Query builders support Behavior(CommandBehavior). Allowed flags are SingleResult, SequentialAccess, and SingleRow, including combinations. CloseConnection, schema-only, and other unsupported behavior flags are rejected. Behavior can be configured once per query.

SequentialAccess is a provider hint. Generated row materialization reads properties in mapping order, so ensure that order is compatible with the selected column ordinals when the provider requires sequential access.

Compile for inspection

Compile() returns CompiledSql with Text, Parameters, CommandType, Timeout, and Behavior. Execution terminals accept builders and compile them when invoked. A compiled object is an inspection snapshot, rather than an IQueryBuilder or ICommandBuilder accepted directly by those terminals.

See SQL interpolation, Readers, and Transactions.