Pull to refresh

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

Reading time 3 min
Views 5.6K
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-структур;

Вот такой облом. Собственно, наибольшее негодование вызывает забитый болт на проблему.
Tags:
Hubs:
0
Comments 0
Comments Leave a comment

Articles