Dapper custom helper (for Table-Valued Parameters) comma instead of dot in floating point value

Question

Code from Does Dapper support SQL 2008 Table-Valued Parameters? works great.

But with MSSQL SQL_VARIANT column comes (usually) the trouble...

private static string ConnectionString = @"data source=192.168.1.1;initial catalog=;persist security info=False;user id=sa;password=";

private static void TestTVP2()
    {
        var connection = new SqlConnection(ConnectionString);
        connection.Open();

        var nums =
            connection
            .Query<BigIntKeySqlVariantValueType.TypeFields>("[dbo].[pmesAttributeValueUpdate]", new BigIntKeySQLVariantValueTVP(new[]
                    {
                        new BigIntKeySqlVariantValueType.TypeFields{ParameterName = 9976669, ParameterValue= 8.5}
                    }, "@tvp")
                ).ToList();

        foreach (var num in nums)
        {
            Console.WriteLine(num.BigIntKeySqlVariantValueTypeId + " " + num.ParameterName + " " +
                              num.ParameterValue);
        }
    }


internal class BigIntKeySQLVariantValueTVP : SqlMapper.IDynamicParameters
{
    private readonly IEnumerable<BigIntKeySqlVariantValueType.TypeFields> _values;
    private readonly string _parametername;

    public BigIntKeySQLVariantValueTVP(IEnumerable<BigIntKeySqlVariantValueType.TypeFields> values, string parametername)
    {
        this._values = values;
        this._parametername = parametername;
    }

    public void AddParameters(IDbCommand command)
    {
        var sqlCommand = (SqlCommand)command;
        sqlCommand.CommandType = CommandType.StoredProcedure;

        var numberList = new List<SqlDataRecord>();

        // Create an SqlMetaData object that describes our table type.
        SqlMetaData[] tvpDefinition =
            {
                new SqlMetaData(
                    "BigIntKeySQLVariantValueTypeId", SqlDbType.BigInt, true, false, SortOrder.Unspecified, -1),
                new SqlMetaData("ParameterName", SqlDbType.BigInt),
                new SqlMetaData("ParameterValue", SqlDbType.Variant),
            };

        foreach (var n in _values)
        {
            // Create a new record, using the metadata array above.
            var rec = new SqlDataRecord(tvpDefinition);

            rec.SetInt64(1, n.ParameterName); // Set the value.
            rec.SetValue(2, n.ParameterValue);

            numberList.Add(rec); // Add it to the list.
        }

        // Add the table parameter.
        var p = sqlCommand.Parameters.Add(_parametername, SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "BigIntKeySQLVariantValueType";
        p.Value = numberList;
    }

    public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
    {
        AddParameters(command);
    }
}

public class BigIntKeySqlVariantValueType 
{
    /// <summary>
    /// Type POCO
    /// </summary>
    public struct TypeFields
    {
        public long BigIntKeySqlVariantValueTypeId { get; private set; }

        public long ParameterName { get; set; }

        public object ParameterValue { get; set; }
    }
}

Here is code generated by Dapper as seen in SQL Profiler:

declare @p1 dbo.BigIntKeySQLVariantValueType
insert into @p1 values(9976669,8,5)
exec [dbo].[pmesAttributeValueUpdate] @tvp=@p1

My Table-Valued type:

CREATE TYPE [dbo].[BigIntKeySQLVariantValueType] AS TABLE(
    [BigIntKeySQLVariantValueTypeId] [bigint] IDENTITY(1,1) NOT NULL,
    [ParameterName] [bigint] NULL,
    [ParameterValue] [sql_variant] NULL,
    PRIMARY KEY CLUSTERED 
(
    [BigIntKeySQLVariantValueTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

Code generated by Dapper should be:

insert into @p1 values(9976669,8.5)

How to "replace" comma with dot in SqlParameter

        // Add the table parameter.
        var p = sqlCommand.Parameters.Add(_parametername, SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "BigIntKeySQLVariantValueType";
        p.Value = numberList;

How are you getting that output? Are you using a SQL trace? Or is that something like mini-profiler? The point is: dapper never adds `declare` etc - the parameters are sent *as parameters*. It could be that the error here is simply a UI error during rendering (not using an invariant culture). — Marc Gravell, Apr 18 '13 at 06:42
"Dapper output" is Control+C from Profiler and invoke: `connection.Query("[dbo].[pmesAttributeValueUpdate]", tvp).ToList();` must generate INSERT INTO, right? — Tomasito, Apr 18 '13 at 09:06
it is hard to say; that *could* just be a profiler rendering issue; does it *work* currently? also: you don't show the command / parameters you are supplying to dapper, so it is hard for me to comment on whether it is right / wrong... can you show the call to `Query` / `Execute` ? (including the command / command-type / etc) Dapper does NOT add `INSERT` etc. It just adds parameters. — Marc Gravell, Apr 18 '13 at 10:04
@MarcGravell it works... All signs in the heavens (Profiler) and the earth (Console) suggests that it should not so I did not check whether the table has the data. Bonus question: why SQL Profiler shows `INSERTS` and how actual SQL statement looks like. — Tomasito, Apr 18 '13 at 11:01
I believe that is a white lie that is intended to make it human readable and make it possible to copy/paste into SSMS to debug — Marc Gravell, Apr 18 '13 at 11:22

Tomasito · Accepted Answer · 2013-04-18T20:20:08.273

0

It turns out that the value of a comma instead of a period is only shown in SQL Profiler and in the Console.Write.

To the database is passed the correct floating-point value.

edited Apr 18 '13 at 20:20

answered Apr 18 '13 at 11:54

Tomasito

1,864
1
20
43

Dapper custom helper (for Table-Valued Parameters) comma instead of dot in floating point value

1 Answers1