传递型“对象”的参数表值参数的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 电话：

的列的类型值不被支持。该类型是'对象'的

我发现的 的人谁3年前遇到了同样的问题，当它被确定为一个已知的Microsoft错误。到bug的链接被打破，虽然。有谁知道，如果有更新的错误或潜在的解决方法的状态信息？既然这样，这个错误真的杀死了 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.

推荐答案

希望你有一个很好的使用了这种一刀切的数据模型。通常，它在你的脸上，当你试图扩展由于基于集合的关系代数和sargeability的误解炸毁。也许你已经给这一些思考。我不想开发这个模型的ETL或报告。

Hopefully, you have a really good use for this "one size fits all" data model. Typically, it blows up in your face when you try to scale due to a misunderstanding of set based relational algebra and sargeability. Maybe you've given this some thought. I would hate to develop ETLs or reports on this model.

请记住，你想一个对象插入到它没有底层的知识数据库引擎结构体。目的是使通用的对象的接收器具有不知道如何解释它的知识。对象具有的属性，但你只能访问那些如果你有明确的指示，或者你使用反射来识别潜在的类型来解包它们。
的唯一方法（即我所知道的）一个对象可靠地转换为基础类型，不知道有这样的事，是通过使用反射。也许一些的.Net大师将填补我在其他方面。

Keep in mind, you are trying to insert an object into the database engine that it has no knowledge of the underlying structure. The object is so generic that the receiver of the object has no knowledge of how to interpret it. The object has properties, but you'll only be able to access those if you have explicit instructions or you use reflection to identify the underlying type to unpackage them. The only way (that I know of) to reliably convert an object to the underlying type, without knowing anything about it, is through the use of reflection. Maybe some of the .Net gurus will fill me in on other ways.

通用对象类型不能被存储为这样的SQL_VARIANT。即使SQL_VARIANT需要有点强类型的值。 SQL Server不打算隐含使用反射来试图找出数据类型（然后是基础数据的值）是什么。

The generic object type cannot be stored as a sql_variant like this. Even sql_variant requires somewhat strongly typed values. SQL Server is not going to implicitly use reflection to try and figure out what the data type (and then the value of the underlying data) is.

您可以使用的System.Reflection的GetType方法，然后抛出一个case语句之前插入相应的投。

You could use System.Reflection's GetType method and then throw a case statement in to cast accordingly before insert.

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