在 sql_variant 列的表值参数中传递“对象"类型的参数 [英] Passing parameter of type 'object' in table-valued parameter for sql_variant column

问题描述

我在 SQL Server 2012 中有一个表值参数定义为:

I have a table-valued parameter in SQL Server 2012 defined as:

CREATE TYPE [dbo].[TVP] AS TABLE (
    [Id] [int] NOT NULL,
    [FieldName] [nvarchar](100) NOT NULL,
    [Value] [sql_variant] NOT NULL
)

我在 C# 中调用它，代码大致如下所示:

I call it in C# with code that looks roughly like the following:

var mdItems = new DataTable();
mdItems.Columns.Add("Id", typeof(int));
mdItems.Columns.Add("FieldName", typeof(string));
mdItems.Columns.Add("Value", typeof(object));
mdItems.Rows.Add(new object[] {2, "blah", "value"}); //'value' is usually a string
SqlCommand sqlCommand = conn.CreateCommand();
sqlCommand.CommandText = "[WriteFieldValues]";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@FieldValues", mdItems);
sqlCommand.ExecuteNonQuery();

然后我在 ExecuteNonQuery 调用中从 SQL Server 收到以下错误:

I then get the following error from SQL Server on the ExecuteNonQuery call:

不支持值"列的类型.类型是对象"

我找到了某人 谁在 3 年前遇到了同样的问题，当时它被确定为已知的 Microsoft 错误.但是，该错误的链接已损坏.有谁知道是否有关于错误状态或潜在解决方法的更新信息?就目前而言，这个错误确实杀死了 sql_variant 字段的值.

I found someone who encountered the same problem 3 years ago when it was identified as a known Microsoft bug. The link to the bug is broken, though. Does anyone know if there is updated information on the status of the bug or a potential workaround? As it stands, this bug really kills the value of sql_variant fields.

推荐答案

这篇文章已经有很多年了，但我遇到了同样的问题并有解决方案.如果您不使用 DataTable 而是填充 SqlDataRecord 的集合，那么您可以将 SqlDataRecord 的数据类型设置为 SqlDbType.Variant.

This post is many years old now but I hit the same problem and have a solution. If you do not use a DataTable but instead populate a collection of SqlDataRecord then you can set the datatype of the SqlDataRecord to SqlDbType.Variant.

 List<SqlDataRecord> dataTable = new List<SqlDataRecord>();
var dr = new SqlDataRecord(
                            new SqlMetaData("Id", SqlDbType.Int),
                            new SqlMetaData("Value", SqlDbType.Variant));

dr.SetInt32(0, id);
dr.SetValue(1, myObject);

dataTable.Add(dr);

[...]

SqlCommand sqlCommand = new SqlCommand("dbo.MyProc");
var structuredParam = sqlCommand.Parameters.Add("myTableParam", SqlDbType.Structured);
structuredParam.Value = dataTable;

这篇关于在 sql_variant 列的表值参数中传递“对象"类型的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！