SqlCommand "AddParam" extensions - lobodava/artisan-orm GitHub Wiki

Below is the table of the corresponding types in C# and SQL Server, and "AddParam" extension methods used in Artisan.Orm.

See examples of the "AddParam" extensions in SqlParameterTest.cs file and associated Stored Procedures.

This section describes the corrected source contract being prepared for 4.1.0. The fix is recorded in the 4.1.0 section of CHANGELOG.md; check whether an installed NuGet package includes it. Earlier implementations called the string helpers but discarded their return values.

The sized methods process the value in this order:

1. If trimToNull is enabled, trim leading/trailing whitespace. An empty or whitespace-only result becomes null.
2. For a non-null result, truncate to size when truncate is enabled. Otherwise, reject a value longer than size with ArgumentException before adding the parameter.
3. Store null as DBNull.Value; otherwise store the resulting string. Preserve the requested SQL type, size, and input direction.

With both flags disabled, an empty string remains empty and whitespace is preserved, subject to the length check. Null input becomes DBNull.Value with any flag combination. MAX methods perform only the optional trimming and null conversion.

For size = 3:

using var cmd = new Microsoft.Data.SqlClient.SqlCommand();
cmd.AddVarcharParam("@Code", 3, "  abcdef  ", trimToNull: true, truncate: true);
// Value == "abc", SqlDbType.VarChar, Size == 3.

cmd.AddNVarcharParam("@OptionalName", 3, "   ", trimToNull: true);
// Value == DBNull.Value, SqlDbType.NVarChar, Size == 3.

cmd.AddVarcharMaxParam("@Text", "  text  ", trimToNull: true);
// Value == "text", Size == -1.

cmd.AddNVarcharMaxParam("@Blank", "   ", trimToNull: true);
// Value == DBNull.Value, Size == -1.

Import Artisan.Orm for these extension methods. No preliminary TrimToNull() or TruncateTo() call is needed in the corrected version. Enable truncation only when shortening the field is part of the application's contract; leave it disabled when an overlong identifier or other value should be rejected.

Implementation: SqlCommandExtensions_Parameters.cs. Regression coverage: StringParameterTest.cs.

AddDecimalParam(name, precision, scale, value, truncateFraction: false) validates precision 1..38 and scale 0..precision, even when the nullable value is null. A value such as 0.25 fits decimal(2,2): the leading zero does not consume an integer digit.

The default rejects nonzero fractional digits beyond scale. Insignificant trailing zeroes are accepted. With truncateFraction: true, the parameter value is truncated toward zero before it reaches SQL Server: at scale 2, 1.239 becomes 1.23 and -1.239 becomes -1.23. Integer overflow is rejected. For rounding, round explicitly in application code before adding the parameter.

Earlier implementations did not actually truncate and could let SQL conversion round instead. Review callers that relied on that behavior when upgrading to 4.1.0.

Dictionary arguments accept either "Id" or "@Id" for SQL parameter @Id in 4.1.0. Supply a logical parameter only once. This name normalization does not infer a parameter type or bypass the procedure contract.

AddParams derives procedure metadata when needed; the explicit Add*Param helpers above avoid that discovery. In 4.1.0, discovered metadata is scoped to database/connection identity and the schema-qualified procedure, cloned for each command, and separated from the legacy public unscoped cache. A connection using integrated/token authentication reuses metadata within that connection instance. See parameter-cache details and 4.1.0 changes before relying on cache reuse across repositories.