Inserting String data into Varbinary User-Defined Data Type Column SQL Server .NET

Question

I have the requirement to insert records into a SQL table that contains varbinary and image columns.

How do I get 0x00120A011EFD89F94DDA363BA64F57441DE9 (This is the same for all records)

into this

BLOB_TYPE (MY_UDDT(varbinary(18))?

This is what I have so far, everything being very straightforward except for the SqlDbType.Udt parameter definition. With this code I'm getting the error, "Specified type is not registered on the target server.System.Byte[]", I can say 100% the 'User-Defined Data Type' exists on the server. I queried select * from sys.types and it returned the type.

Definition: CREATE TYPE [dbo].[MY_UDDT] FROM varbinary NOT NULL

        sql = "INSERT INTO dbo.BLOBS (BLOB_TYPE) VALUES (@BLOB_TYPE);"
        cmd = New SqlCommand(sql, conn)
        Dim parameter As New SqlParameter("@BLOB_TYPE", SqlDbType.Udt)
        With parameter
            .SqlDbType = SqlDbType.Udt
            .UdtTypeName = "dbo.MY_UDDT"
            .Value = blobType
        End With
        cmd.Parameters.Add(parameter)
        cmd.ExecuteNonQuery()

Public ReadOnly Property blobType As Byte()
    Get
        Dim Str As String = "0x00620A011EFD89F94DDA863BA64F57441DE9"
        Dim bytes As Byte() = New Byte(Str.Length * 2 - 1) {}
        System.Buffer.BlockCopy(Str.ToCharArray(), 0, bytes, 0, bytes.Length)
        Return bytes
    End Get
End Property

Answer 1

There are several things being misunderstood here:

• There is a big difference between User-Defined Data Types (UDDTs) and User-Defined Types (UDTs), though it is understandable that people get confused given the similarity of the names.
  • What you created via CREATE TYPE is a UDDT, not a UDT. This is a T-SQL only construct.
  • Note for the future: when specifying a variable-length datatype, be sure to specify the length: VARBINARY(18) instead of VARBINARY
  • A UDT is a SQLCLR construct. Here is an example on MSDN of using a UDT in this manner:
    http://msdn.microsoft.com/library/ms131080.aspx

• You don't need anything fancy here. You just need to set:
  
  • SqlDbType = SqlDbType.VarBinary
  • Size = 18

• Your blobType function is not sending the hex representation of the string that you are expecting. It is sending the hex values for each character of the string "0x00620A011EFD89F94DDA863BA64F57441DE9". Meaning, the hex value for "0" (i.e. 48 or 0x30), then the hex value for "x" (i.e. 120 or 0x78), and so on for all 38 characters.

  You need to take each set of 2 characters after the "0x" (i.e. "00" then "62" then "0A" and so on), convert those to actual bytes, and send the array.

  OR

  If you have the values in VB as strings and are using SQL Server 2008 or newer, you can pass the string in and have SQL Server convert it for you. You would just modify your INSERT statement to be: INSERT INTO dbo.BLOBS (BLOB_TYPE) VALUES (CONVERT(VARBINARY(18), @BLOB_TYPE, 1));

  The CONVERT function, using a style of "1" (meaning: a string representation of a binary, starting with "0x") will do the trick. For example:

  SELECT CONVERT(VARBINARY(18), '0x00620A011EFD89F94DDA863BA64F57441DE9', 1)

  Of course, then you need to change the SqlParameter properties to be:

  • SqlDbType = SqlDbType.VarChar
  • Size = 38