CODEWITHSUNDEEP
sql-servervb.netsqlclrsqlcommanduser-defined-types
knockando
knockando

Reputation: 1012

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

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

Upvotes: 1

Views: 2634

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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

Upvotes: 3

Jeroen Mostert
Jeroen Mostert

Reputation: 28779

The problem is that the parameter value is a byte array, not an instance of the UDT you are claiming is going to be passed in, and System.Byte[] is, unsurprisingly, not a registered UDT.

It's confusing, but the "UDT" on the .NET side is used only for UDTs that are created in assemblies (CREATE TYPE ... EXTERNAL NAME) and not for UDTs based on SQL Server types (called "alias data types" in Books Online). To pass those, do not set the SqlDbType to Udt but simply pass in a value of the underlying base type. Basically, these types are only useful on the database end to ensure consistency.

Furthermore, you construct the blob incorrectly -- you need to convert the hexstring to bytes, not the characters that make up the hexstring. If it's really a constant, you can declare it as such. So:

cmd.Parameters.AddWithValue("@BLOB_TYPE", New Byte() { &H00, &H62, &H0A, &H01, &H1E, &HFD, &H89, &HF9, &H4D, &HDA, &H86, &H3B, &HA6, &H4F, &H57, &H44, &H1D, &HE9 })

Should do the trick.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

SqlDbType.Udt is for SQLCLR types. In this case, you have a varbinary column so specify SqlDbType.VarBinary along with the max size (18). For the value, pass the byte array containing the raw binary value.

You mention an 18-byte varbinary data type but the data looks to be much longer. If your intent is to convert a string in hex notation to an 18-byte binary value, that's a different question than the one asked here. I suggest you post another question if you need help with that.

Upvotes: 0

Related Questions