Передача параметра типа "объект" в параметре table-valued для столбца sql_variant

У меня есть табличный параметр в SQL Server 2012, определенный как:

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

Я называю это в С# кодом, который выглядит примерно так:

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:

Тип столбца "Значение" не поддерживается. Тип "Объект"

Я нашел человека, который столкнулся с той же проблемой 3 года назад, когда он был идентифицирован как известная ошибка Microsoft. Однако ссылка на ошибку сломана. Кто-нибудь знает, есть ли обновленная информация о статусе ошибки или возможном обходном пути? Как бы то ни было, эта ошибка действительно убивает значения полей sql_variant.

Ответ 1

Этому посту уже много лет, но я столкнулся с той же проблемой и нашел решение. Если вы не используете DataTable, а вместо этого заполняете коллекцию 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;

Ответ 2

Надеюсь, у вас действительно хорошее применение для этой модели данных "один размер подходит всем". Как правило, он взрывается в вашем лице, когда вы пытаетесь масштабироваться из-за непонимания реляционной алгебры с множеством оснований и способности к переносимости. Возможно, вы подумали об этом. Я бы не хотел разрабатывать ETL или отчеты по этой модели.

Имейте в виду, что вы пытаетесь вставить объект в механизм базы данных, чтобы он не знал о базовой структуре. Объект настолько общий, что получатель объекта не знает, как его интерпретировать. Объект имеет свойства, но вы сможете получить доступ к ним только в том случае, если у вас есть явные инструкции или вы используете отражение, чтобы идентифицировать базовый тип для их распаковки. Единственный способ (который я знаю) надежно преобразовать объект в базовый тип, не зная об этом, - это использование рефлексии. Возможно, некоторые из.Net-гуру наполнит меня другими способами.

Тип универсального объекта не может быть сохранен как sql_variant, как это. Даже sql_variant требует несколько сильно типизированных значений. SQL Server не намерен использовать рефлексию, чтобы попытаться выяснить, что такое тип данных (а затем значение базовых данных).

Вы можете использовать метод System.Reflection GetType, а затем бросить оператор case, чтобы выполнить его соответствующим образом перед вставкой.

Ответ 3

К сожалению, у меня нет времени прямо сейчас, чтобы полностью ответить на этот вопрос, но я могу получить вас на правильном пути.

То, что я делал в прошлом, вместо использования TVP, использовать параметр XML, сериализовать набор данных (или любой POCO, если на то пошло) в качестве XML, передать этот XML в proc, а затем использовать свойство ".nodes" (и другие члены) переменной xml, чтобы извлечь все, что было необходимо в временные таблицы, локальные таблицы, "рабочие таблицы" и т.д....

Это расплывчато, но если вы сериализуете этот набор данных и проверяете созданный xml и читаете о типах данных XML в Books On Line, вы, вероятно, сможете выяснить, как выполнить свою задачу.

Если это не поможет, я постараюсь собрать реальное решение завтра, поскольку я просто столкнулся с этим вопросом, когда я уезжал на этот день.

Ура !!