How to convert binary String back to Byte in CSharp. Hex number created by SQL Server 2012 Varbinary(8)

Question

I am trying to convert a String Hex number back into a Byte() in CSharp & I just can't do it!

These Binary numbers are stored in a varBinary(8) column inside Sql Server 2012. A format example is : 0x000000003B654362

I have no problem geting them from the database & turning them into a String. But when I want to query the database using this Hex number as a key I want to turn the String back into a cSharp Byte type. I get an error "Input String is not in correct format" everytime I try to use Convert.ToByte.

The Hex numbers themselves were created inside a tsql query inside Sql Server as follows.

enter image description here

What flavor of Convert.ToByte do I have to use in order to turn the Hex String back into a Byte so I can use it as a query parameter of type varBinary(8) in order to use it as a key when pulling from the database? I am not sure what type of encoding SQL Server 2012 is using to create these Hex numbers as they are created within the sql & not in cSharp. Thank you for looking.

@Marc G

I have tried to query the database using byte[]. requestNbr is my Hex number as a String I have tried System.Text.UnicodeEncoding encoding = new System.Text.UnicodeEncoding(); Byte[] bytes = encoding.GetBytes(requestNbr); Then I also tried UTF8, UTF32 & ASCIIEncoding.

All with cmd.Parameters.Add("@requestNbr", SqlDbType.VarBinary).Value = bytes;

None of these get me any results from the database. Yet when I go to the Stored Procedure & just set the @requestNbr varBinary(8) param to the HexNumber, then it returns results.

The varBinary sql param

The Where Filter

The results I should get when I pass in my byte [] but always returns no results.

Marc Gravell · Accepted Answer · 2013-11-23 20:18:51Z

1

varbinary is handled as raw data. You should never need to treat it as hex or worry about formats. You just use a parameter with a byte[] value (to talk to the database), or read values as byte[] (to read from the database into c#).

I wonder if the biggest problem here is using concatenation rather than parameters.

answered Nov 23, 2013 at 20:18

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

drzounds Over a year ago

So how would I pass the hex number of format 0x000000003B654362 that is currently a String to the stored procedure that expects it to be varBinary? SqlCommand cmd = new SqlCommand(storedproc, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@requestNbr", SqlDbType.VarChar).Value = requestNbr;

Marc Gravell Over a year ago

@drzounds first, stop using strings in this scenario. In c# and c# alone, convert that hex to a byte[]. Then add a parameter with that byte[] value.

drzounds · Accepted Answer · 2013-11-23 23:23:51Z

0

Solution:

I finally just had to give up trying to convert the Hex String into a byte[].

I am now passing in the Hex String itself as NVarChar(max) and changed my WHERE filter to

WHERE CONVERT(VARCHAR(1000), RequestNbr, 1) = @requestNbr

Changing the problem varBinary(8) column into a String that worked for the filter.

answered Nov 23, 2013 at 23:23

drzounds

3793 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to convert binary String back to Byte in CSharp. Hex number created by SQL Server 2012 Varbinary(8)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related