SqlBulkCopy loses SqlMoney type identity when writing sql_variant columns

[mdaigle]

Description

When SqlMoney values are bulk-copied into a sql_variant column, they arrive as numeric instead of money. This is because SqlBulkCopy's write path strips SqlMoney to its underlying CLR decimal and then fails to recover the original TDS type.

Root Cause

SqlBulkCopy invokes ValidateBulkCopyVariant(), which calls MetaType.GetComValueFromSqlVariant() to convert all INullable SqlTypes to their CLR equivalents. For most types the CLR value maps back to the same TDS type (e.g. SqlInt32 → int → SQLINT4), but SqlMoney → decimal → SQLNUMERICN instead of SQLMONEY.

The normal parameter write path (TdsParser.WriteSqlVariantValue) has a heuristic that works around this: when it encounters a decimal value with length == 8, it assumes the value was originally SqlMoney and writes SQLMONEY on the wire. However, the bulk copy write path (TdsParser.WriteSqlVariantDataRowValue) does not accept a length parameter and has no such recovery logic. Its case TdsEnums.SQLMONEY branch is effectively unreachable because the value has already been converted to a plain decimal, which maps to SQLNUMERICN.

Relevant Code Locations

• SqlBulkCopy.cs ~line 1515 — ValidateBulkCopyVariant() calls GetComValueFromSqlVariant(), which converts SqlMoney to decimal
• TdsParser.cs ~line 7694-7714 — WriteSqlVariantValue contains the length == 8 heuristic that recovers SqlMoney from decimal
• TdsParser.cs ~line 7869-8060 — WriteSqlVariantDataRowValue lacks this heuristic; case TdsEnums.SQLMONEY (~line 7985) is unreachable for SqlMoney values

Expected Behavior

A SqlMoney value bulk-copied into a sql_variant column should produce a value with SQL_VARIANT_PROPERTY(col, 'BaseType') = 'money'.

Actual Behavior

The value arrives as numeric (i.e. SQL_VARIANT_PROPERTY(col, 'BaseType') = 'numeric').

Reproduction

using var connection = new SqlConnection(connectionString);
connection.Open();

// Setup
using var cmd = new SqlCommand(
    "CREATE TABLE #Test (Id INT, Val SQL_VARIANT)", connection);
cmd.ExecuteNonQuery();

// Bulk copy a SqlMoney value
var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Val", typeof(object));
dt.Rows.Add(1, new SqlMoney(1.23m));

using var bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "#Test";
bulkCopy.WriteToServer(dt);

// Verify
cmd.CommandText = 
    "SELECT SQL_VARIANT_PROPERTY(Val, 'BaseType') FROM #Test WHERE Id = 1";
var baseType = (string)cmd.ExecuteScalar();
// baseType is "numeric" — expected "money"

Impact

This is a data fidelity issue: the value is numerically correct, but the SQL Server base type metadata is lost. Consumers that branch on SQL_VARIANT_PROPERTY(..., 'BaseType') or expect money semantics will see incorrect behavior.

Possible Fix

WriteSqlVariantDataRowValue could apply the same length == 8 heuristic used by WriteSqlVariantValue, or ValidateBulkCopyVariant could preserve the original MetaType for SqlMoney values rather than stripping them to decimal.