'Impossible to add a User-Defined-Data Type parameter to my query

I'm trying to use a User-Defined-Data Type with C# and SQL Server 2008 but I have an exception raised when command.ExecuteReader() is executed. 

string qyery = "SELECT * FROM Product WHERE IsAvailableProduct < @Param";
SqlCommand command = new SqlCommand(qyery, conn);
SqlParameter param = new SqlParameter("@Param", SqlDbType.Structured);

param.UdtTypeName = "MyBolean";
param.SqlDbType = SqlDbType.Structured;
param.Value = 1;

command.Parameters.Add(param);
SqlDataReader reader = command.ExecuteReader();

The raised exception is :

Failed to convert parameter value from a Int32 to a IEnumerable``1.

What's wrong in my code ?

EDIT :

If I change the SqlDbType to Udt, I have this exception : Specified type is not registered on the target server.System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

If I set the DbType property to Int (which is the base type of "MyBolean") the result is the exception Failed to convert parameter value from a Int32 to a IEnumerable1.`` is raised too.

EDIT :

I modified the title because in SQL Server 2008, it's "User Define DATA Type" and no "User Defined Types"

c++sharpsqlsql-server-2008user-defined-types


Solution 1:[1]

The SqlDbType enumeration value Structured is used to indicate that you're passing around a Table Value Parameter, which is why you're getting the error message you're getting as the value you're passing in is an integer, not a "list of something" for use as a TVP.

You need to set param.SqlDbType based on the underlying datatype for MyBolean which I suspect is probably Bit or Int.

Solution 2:[2]

At first glance I would guess you at least need to make SqlDbType = Udt (not Stuctured).

Solution 3:[3]

  1. if you use UdtTypeName="MyBoolean" this type must exist in your database
  2. also if you set SqlDbType=SqlDbType.Structured your type "MyBoolean" must be "table-valued parameter" look at this and more info about using table-valued parameters

so when you set those properies parameter value must be at least implement IEnumerable (i.e. a list of objects), but you provide integer value and this caused error

Solution 4:[4]

I have User-Defined Table Types in my database (assume this to be MyTableTypeList). These are used as parameters in my stored procedure. To pass values through this data type I do the following:

Declare a DataTable similar to my User-Defined Table Type, populate values in it.

Assign SqlParameter property SqlDbType as SqlDbType.Structured "param.SqlDbType = SqlDbType.Structured"

Assign TypeName property with the name of the User-Defnied Table Type. "param.TypeName = "MyTableTypeList"

Solution 5:[5]

Specified type is not registered on the target server.System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089: I'm afraid, you cannot use primitive types, like int32 or string in the Value property of a Udt type parameter. You should either create your custom UDT-Classes in .NET (decorating them with the SqlUserDefinedTypeAttribute) and register them on the SQL-Server (see the details here and here), or you can "fallback" to use the base SQL-types your UDTs based on, like Decimal, DateTime, NVarChar etc. in your .NET code instead of Udt. I faced the very same problem, and voted for the second option.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rob
Solution 2 DancesWithBamboo
Solution 3 Alexander
Solution 4 Vin
Solution 5 pholpar

Related Questions