MS SQL 2008: Табличный тип с колонками вида sql_variant (пока?) несовместим с ADO.NET

sql_variant — Тип данных, хранящий значения различных типов данных, поддерживаемых SQL Server.

Может быть полезен в каких-то обобщающих аспектах разрабатываемой БД. Например, в таблице настроек приложения или при ведении динамических свойств объекта.

Что касается ADO.NET, то здесь возможное удобство в том, что sql_variant можно передать из управляемого кода в хранимую процедуру, которая является обобщенной для передаваемого значения. Если требуется разветвлять логику согласно передаваемому значению, то фактический тип можно узнать через функцию SQL_VARIANT_PROPERTY.

На самом деле, проблем с единичным параметром нет. Проблемы возникают тогда, когда нужно передавать список параметров.



По идее все просто. Списку параметров судя по этой статье лучше всего сопоставить пользовательский тип-таблицу (user defined table type). Держа в уме то, что типу object в .NET соответствует тип параметра SqlDbType.Variant или DbType.Object, запускаем management и visual студии.

1. Создаем обобщенный тип:
   CREATE TYPE [dbo].[GenericList] AS TABLE([value] [sql_variant] NOT NULL)
2. Создаем процедуру, которая будет обрабатывать этот параметр:
   CREATE PROCEDURE [dbo].[ParseGenericList]
@list GenericList readonly
AS
BEGIN
SET NOCOUNT ON;
SELECT value from @list
END
   
3. Радостно потирая ручки в предвкушении результата, пишем в .NET-е код для передачи параметра в БД:
   DataTable table = new DataTable("GenericList");
table.Columns.Add("value", typeof(object));

table.Rows.Add("string");
table.Rows.Add(DateTime.Now);
table.Rows.Add(145);

using (SqlCommand command = connection.CreateCommand()) // created before
{
command.CommandText = "dbo.ParseGenericList";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@list", table);
conn.Open();
using (SqlDataReader r = command.ExecuteReader())
while (r.Read())
Console.WriteLine(r.GetValue(0));
}

После запуска вместо ожидаемых трех строк в консоли прилетает птица обломинго в виде исключения:
Тип столбца "value" не поддерживается. Тип равен "Object"

Справедливости ради стоит заметить, что если изменить тип данных с object\sql_variant на string\nvarchar или float\float соответственно, то все работает на ура.

С запросом в БД тоже проблем нет:
declare @list dbo.GenericList;
insert into @list(value) select cast(0 as bit);
insert into @list(value) select cast('bla' as nvarchar(3));
insert into @list(value) select cast(0.15 as float);

exec dbo.ParseGenericList @list

Несколько минут гугления находят открытый репорт в техподдержке Microsoft:
клац

Внутри переписки МС дает ссылки на патчики, которые я устанавливаю. Один (для висты) не установился, другой (для .net-а) установился. Впрочем, без особого результата. В самом обсуждении говорится, что баг appears to be a very complicated issue and we need further investigation.

Вот такая неприятная история. Можно попробовать обойти проблему несколькими путями (костылями):

• Вместо одного обобщенного списка sql_variant передавать несколько списков с конкретным типом (datetime, int, float, etc);
• Передавать список пар (строка_значение, тип_значения) и кастить значения в в sql_variant непосредственно в хранимой процедуре;
• Сделать допущение о неприменимости передаваемых параметров в CRUD, за исключением R. Тогда можно передавать список строк и сравнивать результаты кастов существующих полей sql_variant с передаваемыми строками;
• Отказаться от использования sql_variant в пользу xml-структур;

Вот такой облом. Собственно, наибольшее негодование вызывает забитый болт на проблему.