Json Parameters NET6 - lobodava/artisan-orm GitHub Wiki

AddJsonParam<T> serialises a CLR object to JSON via System.Text.Json and binds it to an nvarchar(max) parameter. The procedure parses it on the SQL side via OPENJSON. It's a pragmatic alternative to user-defined table types when the payload doesn't fit a single rectangular shape.

Available on net8.0 and net10.0 targets only — guarded by #if NET6_0_OR_GREATER.

Added in v4 — see What's New in v4.

Signature

public static void AddJsonParam<T>(this SqlCommand cmd, string parameterName, T? value);

null values are sent as SQL NULL (not the string "null"). Any non-null T — including anonymous types, records, classes, and primitives — is serialised with the default JsonSerializerOptions.

Round-trip example

C# call site:

public void SavePreferences(int userId, UserPrefs prefs)
{
    ExecuteCommand(cmd =>
    {
        cmd.UseProcedure("dbo.UpsertUserPreferences");
        cmd.AddIntParam ("@UserId",      userId);
        cmd.AddJsonParam("@Preferences", prefs);
    });
}

T-SQL counterpart:

create procedure dbo.UpsertUserPreferences
    @UserId      int,
    @Preferences nvarchar(max)
as
begin
    set nocount on;

    declare @Theme    nvarchar(50),
            @FontSize int,
            @Locale   nvarchar(10);

    select @Theme    = Theme,
           @FontSize = FontSize,
           @Locale   = Locale
    from   openjson(@Preferences)
    with   (
        Theme    nvarchar(50)  '$.theme',
        FontSize int           '$.fontSize',
        Locale   nvarchar(10)  '$.locale'
    );

    merge into dbo.UserPreferences as target
    using (select @UserId as UserId) as source
    on    target.UserId = source.UserId
    when matched     then update set Theme = @Theme, FontSize = @FontSize, Locale = @Locale
    when not matched then insert (UserId, Theme, FontSize, Locale)
                          values (@UserId, @Theme, @FontSize, @Locale);
end;

Note the '$.theme' JSON path: System.Text.Json defaults to camelCase property names, so a C# property Theme lands at JSON path $.theme. Match the casing in your OPENJSON ... WITH clause.

Anonymous types

Convenient for tightly-scoped payloads that don't deserve a dedicated DTO:

cmd.AddJsonParam("@Filter", new
{
    minAmount = 100m,
    statuses  = new[] { "Active", "Pending" },
    fromDate  = DateTime.UtcNow.AddDays(-30)
});

Arrays of records

OPENJSON can also project an array of objects into a tabular result, which is occasionally useful as a lighter-weight alternative to a TVP:

public void BulkUpdateRecords(IList<RecordPatch> patches)
{
    ExecuteCommand(cmd =>
    {
        cmd.UseProcedure("dbo.PatchRecords");
        cmd.AddJsonParam("@Patches", patches);
    });
}

create procedure dbo.PatchRecords
    @Patches nvarchar(max)
as
begin
    set nocount on;

    update r
    set    r.Name   = p.Name,
           r.Status = p.Status
    from   dbo.Records r
    inner join openjson(@Patches)
        with (
            Id     int           '$.id',
            Name   nvarchar(100) '$.name',
            Status nvarchar(20)  '$.status'
        ) p on p.Id = r.Id;
end;

When to choose JSON vs TVP

Aspect	`AddJsonParam<T>`	`AddTableParam` (TVP)
SQL side parsing cost	One `OPENJSON` per call — measurable for large payloads.	Free — the TVP arrives as a typed table.
Schema validation	Soft — bad JSON throws at parse time; types coerced.	Strict — column types validated at the boundary.
Database setup	None — only the procedure parameter is `nvarchar(max)`.	Requires creating a user-defined table type beforehand.
Heterogeneous payloads	Natural — nested objects, arrays, optional fields.	Awkward — every TVP is a flat rectangle.
Maximum size	`nvarchar(max)` — ~2 GB.	TVP rows — millions of rows possible, faster on bulk inserts.
Suitable for batch insert	OK for small batches (hundreds).	Better for large batches (thousands+).

Rule of thumb:

Single complex object (settings, filter, configuration) → JSON.
Many flat rows of the same shape → TVP.
Many nested objects → either work; pick whichever your team already has tooling for.

Other options

System.Text.Json is locked in for now — there is no overload that accepts a JsonSerializerOptions. If you need custom serialisation (snake_case, custom converters, etc.), serialise manually first and pass the resulting string via cmd.AddNVarcharMaxParam("@Json", json).

Receiving JSON from SQL Server

To go the other direction — read a JSON column produced by FOR JSON — read it as a regular string:

var json = cmd.ReadTo<string>("select OrderJson from dbo.Orders where Id = @Id");
var order = JsonSerializer.Deserialize<Order>(json);

There is no built-in ReadJson<T> helper because deserialisation rarely benefits from being inside the data-access layer.