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

Question

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"

Answer 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.