Inserting a UDT value into a SQL 2005 Table. RRS feed

  • Question

  • Hi!

    I'm writing a generic component, which takes as input the following: databasename, schemaname, tablename, columnname, and columnvalue (in string format). My generic component should then create a new row in that table, and insert the specified value in the column.

    I'm facing difficulty trying to enter values when the column is a UDT type. I create a SqlCommand object, use a parameterized INSERT statement, and for the SqlParameter object, I set the SqlDbType to "Udt". I also first read some System tables (specifically - sys.types and sys.columns) and set the UdtTypeName property of the SqlParameter correctly. The SqlParameter.Value - I set it to the System.String value specified.

    However, I get the exception that the type "System.String" is not registered on the server. Specifying the value as SqlString threw a similar error.

    What I've figured out, is that for UDT types, the SqlParameter.Value must be an object of the UDT type as defined in the .NET assembly. However, since my component is generic, I dont want to link against the assembly. One way would be to dynamically load the assembly, and use reflection to call the Parse() method on the UDT to convert the String/SqlString to the UDT object.

    Question - how do I determine this information dynamically? ie., the fully qualified .NET type of the UDT? So that if this DLL is present in the GAC, I can use reflection or something to get the Type?

    Or is there an easier way?

    The main requirement is that I dont want to compile/link my generic component against the UDT assembly.

    Sunday, December 23, 2007 9:35 AM