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"

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.

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

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

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"

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.