在sql_variant列的表值参数中传递 'object' 类型的参数

Question

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

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

在c#中调用它，代码大致如下:

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();

我然后得到以下错误从SQL Server上的ExecuteNonQuery调用:

不支持列"Value"的类型。类型为'Object'

我发现有人在3年前遇到了同样的问题，当时它被确定为一个已知的微软漏洞。不过，该漏洞的链接已经断开。有人知道是否有关于bug状态或潜在解决方案的更新信息吗?按照目前的情况，这个bug确实杀死了sql_variant字段的值。

Answer 1

这篇文章已经发表很多年了，但我遇到了同样的问题，并有一个解决方案。如果您不使用数据表，而是填充SqlDataRecord的集合，那么您可以将SqlDataRecord的数据类型设置为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;

Answer 2

不幸的是，我现在没有时间完全回答这个问题，但我可以引导你走上正确的道路。

我过去所做的是，而不是使用TVP，使用XML参数，将数据集(或任何POCO)序列化为XML，将该XML传递到进程中，然后使用"。属性(和其他成员)的XML变量，以提取所需的任何到临时表，本地表变量，"工作表"等…

这是模糊的，但如果你序列化数据集，检查创建的xml，并阅读在线图书中的xml数据类型，你可能能够弄清楚如何完成你的任务。

如果没有帮助，我将试着把一个实际的解决方案放在明天，因为我刚刚遇到这个问题，当我离开这一天。

干杯! !

Answer 3

我猜您正在使用sql_variant，因为您想传递不同的类型。

您应该使用nvarchar(255)，然后将该类型传递给表。

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

也使用typeof(string)而不是typeof(object)作为数据表中的Value列。

这样就可以将[Value]转换为存储过程中的[Type]。

SELECT CAST([Value] AS [Type]) AS ValueWithType

Answer 4

希望您能够很好地使用这种"一刀切"的数据模型。通常，由于对基于集合的关系代数和可寻性的误解，当您尝试扩展时，它会在您的脸上爆炸。也许你已经考虑过了。我不愿意在这个模型上开发etl或报告。

请记住，您正在尝试将对象插入到数据库引擎中，而该对象不了解底层结构。对象是如此通用，以至于对象的接收者不知道如何解释它。对象具有属性，但只有在具有显式指令或使用反射来识别底层类型以解包它们时才能访问这些属性。(据我所知)在不了解对象的情况下可靠地将对象转换为底层类型的唯一方法是使用反射。也许一些。net大师会以其他方式告诉我。

泛型对象类型不能像这样存储为sql_variant。甚至sql_variant也需要某种强类型的值。SQL Server不会隐式地使用反射来尝试找出数据类型(然后是底层数据的值)。

你可以用System。