SqlParam

SqlParam: typed parameter factories

SqlParam creates typed, unattached Microsoft.Data.SqlClient.SqlParameter objects for the 4.1.0 API. It is convenient with RepositoryBase shortcuts: the query, values, and SQL types can stay together in one call. Existing cmd.Add*Param helpers remain useful inside command-configuration callbacks.

The factory names follow the existing helpers: AddIntParam becomes SqlParam.Int, AddNVarcharParam becomes SqlParam.NVarchar, and AddIntOutputParam becomes SqlParam.IntOutput. They use the same SQL metadata, conversions, validation, nullable overloads, and optional flags. Factories configure parameters; they do not execute SQL or discover a procedure's metadata.

Start with a repository shortcut

This complete example assumes dbo.Products has Id int NOT NULL, Name nvarchar(100) NULL, and IsActive bit NOT NULL. Both queries select Id, Name in that order. Automatic mapping uses those names; the explicit mapper uses those ordinals. The example also supplies the two table functions used later on this page, for an existing dbo.ProductTableType(Id int NOT NULL, Name nvarchar(100) NULL) UDTT.

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Artisan.Orm;
using Microsoft.Data.SqlClient;

public sealed class Product
{
    public int Id { get; set; }
    public string? Name { get; set; }
}

[MapperFor(typeof(Product), RequiredMethod.AllMain)]
public static class ProductMapper
{
    public static Product CreateObject(SqlDataReader reader) => new Product
    {
        Id = reader.GetInt32(0),
        Name = reader.GetStringNullable(1)
    };

    public static DataTable CreateDataTable() => new DataTable("dbo.ProductTableType")
        .AddColumn<int>("Id", isNullable: false)
        .AddColumn<string>("Name", isNullable: true);

    public static object[] CreateDataRow(Product value) =>
        new object[] { value.Id, (object?)value.Name ?? DBNull.Value };
}

public static class ProductQueries
{
    private const string ListSql =
        "SELECT Id, Name FROM dbo.Products WHERE IsActive = @IsActive ORDER BY Id;";

    public static IList<Product> ReadAutomatic(RepositoryBase repository, bool isActive) =>
        repository.ReadAsList<Product>(ListSql, SqlParam.Bit("@IsActive", isActive));

    public static Task<IList<Product>> ReadMappedAsync(
        RepositoryBase repository, bool isActive, CancellationToken cancellationToken) =>
        repository.ReadToListAsync<Product>(
            ListSql, cancellationToken, SqlParam.Bit("@IsActive", isActive));
}

The params overload places the cancellation token before the parameters. Configuration-callback overloads retain their existing argument order. Parameter factories do not select the reading strategy: ReadAs* still uses automatic mapping and ReadTo* still follows its registered/inline/scalar mapping contract. An entity-read-only mapper can declare RequiredMethod.CreateObject; this example uses AllMain because it also provides table mapping.

Preserve the typed parameter contract

Use the same sizes, precision, scale, and flags that the SQL declaration requires:

using Artisan.Orm;
using Microsoft.Data.SqlClient;

public static class ValueParameters
{
    public static SqlParameter[] Create(string? comment, decimal amount) => new[]
    {
        SqlParam.NVarchar("@Comment", 200, comment, trimToNull: true, truncate: true),
        SqlParam.Decimal("@Amount", 12, 2, amount, truncateFraction: true)
    };
}

trimToNull trims before length validation or truncation and converts blank text to SQL NULL. truncate is an intentional length limit; leave it false when excess text should be rejected. MAX-string factories use Size = -1 and have no truncation flag. Decimal truncation discards excess fractional digits toward zero; it is not rounding. These flags default to false. Scalar null inputs become DBNull.Value on a real parameter, rather than omitting that parameter.

Every non-null factory result is a fresh parameter. A SqlParameter is mutable and belongs to one command collection at a time. Create new parameters for separate or concurrent commands; do not cache and share instances as parameter templates.

There is no SqlParam factory corresponding to the metadata-discovering cmd.AddParams(dictionary) or RepositoryBase.AddParams(cmd, object) batch operation. Use explicit factories when the SQL contract is known, or keep AddParams when procedure metadata discovery is intended. Neither factories nor the analyzer infer the live database contract.

Table parameters and mapper checks

Call shape	Behavior
SqlParam.Table(name, dataTable)	Uses DataTable.TableName as the SQL UDTT name
SqlParam.Table(name, products)	Uses registered CreateDataTable and CreateDataRow
SqlParam.Table(name, products, tableName)	Uses the same mapper with an explicit type-name override
SqlParam.Table(name, products, tableName, columnNames)	Automatic mapping of the named properties, in the requested order
SqlParam.TableRow(name, product)	Builds a one-row TVP with the registered table mapper
SqlParam.TableRow(name, product, tableName, columnNames)	Builds a one-row TVP using explicit-column automatic mapping
Non-generic Table / TableRow for byte, short, int, or long	Uses the matching built-in integer-ID table type

The three-argument collection overload is mapped, not automatic. An explicit generic call such as SqlParam.Table<int>(...) requests a mapper; it does not select the non-generic integer-ID convenience overload. Built-in ID helpers do not create their SQL types. For another schema/type name, build a DataTable with that name, for example ids.ToIntIdDataTable("dbo.CustomIdTableType", "Id").

Match the deployed UDTT name, column order, SQL/CLR types, and nullability. A manual mapper can flatten or transform the model; its table need not mirror all C# properties. Automatic mapping follows the explicitly selected properties and does not validate the deployed SQL type.

The analyzer checks mapped SqlParam.Table<T> and TableRow<T> calls and method-group references. A missing registration produces ART001; a registered mapper missing a needed table function produces ART006. The compatible signatures are DataTable CreateDataTable() and object[] CreateDataRow(T value). Adding both functions is sufficient even when the attribute declares only RequiredMethod.CreateObject. Explicit-column automatic overloads, non-generic ID overloads, and other factories such as Json do not require a registered mapper. See compile-time mapper validation.

Null, empty, and direct command use

A null collection or null DataTable passed to SqlParam.Table returns null: there is no parameter to attach. Repository parameter-array overloads, including CreateCommand, skip these null entries. A non-null empty collection produces a real structured parameter with an empty table. All four integer collection overloads follow that rule, including short; the previous AddTableParam short overload's null inconsistency is normalized too.

SqlParam.TableRow<Product>(name, null) has different semantics: it wraps one null entity, and the registered table conversion skips that entity. With a valid mapper, it therefore supplies an empty mapped table, not an omitted parameter. Do not use that expression to request omission. Collections are converted to DataTables when the factory runs; these factories are not streaming inputs.

Raw SqlCommand.Parameters.Add / AddRange do not perform the repository's null filtering. Guard an optional factory result explicitly. This helper reuses the Product declarations above and only configures its caller's command:

using System.Collections.Generic;
using Artisan.Orm;
using Microsoft.Data.SqlClient;

public static class ProductParameters
{
    public static void AddOptionalProducts(SqlCommand command, IEnumerable<Product>? products)
    {
        var parameter = SqlParam.Table("@Products", products);
        if (parameter != null)
            command.Parameters.Add(parameter);
    }

    public static SqlParameter? CreateAutomatic(IEnumerable<Product>? products) =>
        SqlParam.Table("@Products", products, "dbo.ProductTableType", "Id,Name");
}

Output parameters and the procedure return value

Output factories return a parameter with Direction = Output; they do not set InputOutput. Retain the returned object to read its Value after execution has completed. With a reader-based operation, wait until the reader has closed or been disposed. Handle DBNull.Value for nullable outputs. An input/output parameter still requires an explicit SqlParameter with Direction = InputOutput and its initial value.

This independent example assumes dbo.SaveLabel(@Name nvarchar(100), @SavedId int OUTPUT, @Message nvarchar(200) OUTPUT), with nullable outputs and an integer T-SQL RETURN code:

using System;
using System.Threading;
using System.Threading.Tasks;
using Artisan.Orm;

public static class LabelCommands
{
    public static async Task<(int ReturnCode, int? SavedId, string? Message)> SaveAsync(
        RepositoryBase repository, string name, CancellationToken cancellationToken)
    {
        var savedId = SqlParam.IntOutput("@SavedId");
        var message = SqlParam.NVarcharOutput("@Message", 200);
        var returnValue = SqlParam.ReturnValue();

        var code = await repository.ExecuteAsync(
            "dbo.SaveLabel", cancellationToken,
            SqlParam.NVarchar("@Name", 100, name), savedId, message, returnValue);

        return (code,
            savedId.Value is null or DBNull ? null : (int)savedId.Value,
            message.Value is null or DBNull ? null : (string)message.Value);
    }
}

Execute / ExecuteAsync and ExecuteCommand / ExecuteCommandAsync return the procedure's return code, not the affected-row count. They reuse the supplied integer return-value parameter; in the example, returnValue.Value contains the same code. Without a supplied return parameter, they add one before execution.

The return-parameter helpers have distinct ownership and lookup behavior:

Helper	Behavior
SqlParam.ReturnValue()	Creates a fresh, unattached @ReturnValue parameter with SqlDbType.Int and Direction.ReturnValue
cmd.GetReturnValueParam()	Validates and returns the existing return-value parameter, regardless of its name; returns null if absent
cmd.ReturnValueParam()	Validates and reuses the existing parameter, or adds a new one if absent
cmd.AddReturnValueParam()	Ensures the same parameter exists; repeated calls do not add duplicates

Validation rejects multiple return-value parameters, a return parameter whose SQL type is not Int, and a non-return parameter named ReturnValue or @ReturnValue (case-insensitive). Repository Execute* methods perform this validation before opening/executing the command. The factory itself has no command to inspect; attaching two independently created return parameters is still an error.

ExecuteCommand / ExecuteCommandAsync now configure the command before ensuring its return parameter, so callbacks can supply SqlParam.ReturnValue() themselves. A callback that needs the reference during configuration should call cmd.ReturnValueParam(); do not index cmd.Parameters["@ReturnValue"] assuming the repository added it before the callback.

Target-framework availability

Core typed factories, output factories, TVPs, and ReturnValue are available in all three library assets: netstandard2.1, net8.0, and net10.0.

The net8.0 and net10.0 assets additionally provide SqlParam.Json<T>, SqlParam.Date overloads for DateOnly / DateOnly?, and SqlParam.Time overloads for TimeOnly / TimeOnly?. Those overloads are absent from netstandard2.1; use supported serialization with NVarcharMax, or the DateTime / TimeSpan overloads there.

Json uses default System.Text.Json serialization into nvarchar(max) (Size = -1), with null input sent as SQL NULL. It does not inherit application serializer options. For custom options, serialize explicitly and pass the text to NVarcharMax; passing JSON text into Json serializes it again as a JSON string. Date/time helpers preserve the existing conversion contracts. Adding typed factories does not make DateOnly or TimeOnly automatic-mapping types.

See also: SqlCommand parameter helpers, table parameters, output parameters, and repository command initialization.