📜 ⬆️ ⬇️

MS SQL 2008: A table type with columns of the form sql_variant (for now?) Is incompatible with ADO.NET

sql_variant - A data type that stores the values ​​of various data types supported by SQL Server.

It may be useful in some generalizing aspects of the database being developed. For example, in the application settings table or when maintaining the dynamic properties of an object.

As for ADO.NET, here the possible convenience is that sql_variant can be transferred from managed code to a stored procedure, which is generalized for the transmitted value. If it is necessary to branch the logic according to the value passed, then the actual type can be found through the function SQL_VARIANT_PROPERTY .
In fact, there are no problems with a single parameter. Problems arise when you need to pass a list of parameters.

In theory, everything is simple. Judging by this article, it is best to match the list of parameters to a user-defined table type. Keeping in mind that the type of the object in .NET corresponds to the type of the parameter SqlDbType.Variant or DbType.Object , run the management and visual studio.

  1. Create a generic type:
    CREATE TYPE [dbo].[GenericList] AS TABLE([value] [sql_variant] NOT NULL)
  2. Create a procedure that will handle this parameter:
    CREATE PROCEDURE [dbo].[ParseGenericList]
    @list GenericList readonly
    SELECT value from @list

  3. Gladly rubbing the handles in anticipation of the result, we write to the .NET code to transfer the parameter to the database:
    DataTable table = new DataTable("GenericList");
    table.Columns.Add("value", typeof(object));


    using (SqlCommand command = connection.CreateCommand()) // created before
    command.CommandText = "dbo.ParseGenericList";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@list", table);
    using (SqlDataReader r = command.ExecuteReader())
    while (r.Read())

After the launch, instead of the expected three lines, the bird comes flying in the console as an exception:
"value" . "Object"

To be fair, it’s worth noting that if you change the data type from object \ sql_variant to string \ nvarchar or float \ float, respectively, then everything works with a bang.

With a query to the database, there are also no problems:
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

A few minutes of googling find an open report in Microsoft technical support:

Inside the correspondence, the MC gives links to patches that I install. One (for whists) was not established, another (for .net-a) was installed. However, without much result. In the discussion itself, it says that the bug appears.

This is such an unpleasant story. You can try to work around the problem in several ways (crutches):

Here is a bummer. Actually, the greatest resentment is caused by a bolt on the problem.

Source: https://habr.com/ru/post/101326/

All Articles