Artisan Way of Data Reply - lobodava/artisan-orm GitHub Wiki

The DataReply pattern lets a stored procedure communicate business-logic outcomes — validation failures, "not found", "conflict", "unauthorized" — back to the application layer in a structured way, distinct from infrastructure errors (timeouts, deadlocks, network issues). The procedure prepends one extra result set with a status code, optionally followed by validation messages, and only then returns the actual data.

Originally introduced in Artisan.Orm 1.1. For the long-form design rationale and the evolution of the pattern, see the re-published article: The Artisan Way of Data Reply.

Every stored procedure that opts into the pattern emits, in order:

1. Result set 0 — a single row, single varchar column containing the status code (OK, FAIL, MISSING, CONFLICT, UNAUTHORIZED, ...).
2. Result set 1 (optional) — when status is not OK, zero or more rows of (Code, Message, ...) describing what went wrong.
3. Result sets 2+ — the actual procedure output (a saved entity, a queried list, etc.).

create procedure dbo.SaveUser
    @User    dbo.UserTableType       readonly,
    @RoleIds dbo.TinyIntIdTableType  readonly
as
begin
    set nocount on;

    -- Validate
    if exists (select 1 from @User u where u.Login is null or len(u.Login) > 20)
    begin
        select 'FAIL';                                      -- result set 0: status

        select 'INVALID_LOGIN', 'Login must be 1..20 characters'    -- result set 1: messages
        union all
        select 'INVALID_ROLE',  'Unknown role assigned to user';

        return;
    end;

    -- Happy path
    select 'OK';                                           -- result set 0: status

    merge into dbo.Users ...;
    select * from dbo.Users where Id in (...);            -- result set 2: actual data
end;

RepositoryBase.CheckForDataReplyException reads result set 0, parses the status, and:

• on OK — advances past it so the next Read* call sees the actual data,
• on anything else — reads the messages (if any) and throws DataReplyException.

public User? SaveUser(User user)
{
    return GetByCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveUser");
        cmd.AddTableRowParam("@User",    user);
        cmd.AddTableParam   ("@RoleIds", user.RoleIds);

        return cmd.GetByReader(reader =>
        {
            CheckForDataReplyException(reader);   // throws DataReplyException if status != OK
            return reader.ReadTo<User>();          // reads the saved User on the OK path
        });
    });
}

A typical controller turns the exception into a structured 4xx response:

try
{
    var saved = _repo.SaveUser(user);
    return Ok(saved);
}
catch (DataReplyException ex)
{
    return BadRequest(new
    {
        Status   = ex.Status.ToString(),
        Messages = ex.Messages
    });
}

Built-in statuses (in Artisan.Orm.DataReplyStatus):

• Ok
• Fail
• Missing — entity not found (404 territory)
• Conflict — concurrency / uniqueness violation (409 territory)
• Unauthorized — access denied (403 territory)

Any unrecognised status string is parsed as Fail. Add new statuses as needed by introducing matching string constants on the SQL side and treating unknown ones as generic failures.

Both of those propagate as SqlException. Distinguishing "user typed an invalid email" from "the database deadlocked" then requires inspecting error numbers, severity levels, and parsing message text — fragile and verbose. The DataReply pattern keeps expected business outcomes out of the exception channel entirely, leaving real exceptions to mean real exceptions.

A side benefit: the SQL profiler / extended events output stays clean. You don't have to filter "user validation failures" out of your error monitoring.

• Use DataReply when validation logic lives in T-SQL (referential checks across tables, "row already exists" detection via MERGE output, rate limits enforced by the database).

• Use C# validation with [ValidationAttribute] / FluentValidation when validation can be done from data the controller already has.

• Use RAISERROR / THROW for genuine exceptions (deadlock victim, FK violation that should never happen, division by zero) that you want to surface as SqlException.

The three are not mutually exclusive — a procedure can run cheap C# validations first, then RAISERROR for "should never happen" guard checks, then return DataReply codes for "happened during normal operation".

See also:

• DataReply.cs
• DataReplyException.cs
• Negative identities and object graph saving — typical SaveUser-style procedures that benefit from DataReply
• Background reading (long-form): The Artisan Way of Data Reply