Converting SQL Server varBinary data into string C#

Question

I need help figuring out how to convert data that comes in from a SQL Server table column that is set as varBinary(max) into a string in order to display it in a label.

This is in C# and I'm using a DataReader.

I can pull the data in using:

var BinaryString = reader[1];

i know that this column holds text that was previously convert to binary.

Solution 1

It really depends on which encoding was used when you originally converted from string to binary:

 byte[] binaryString = (byte[])reader[1];

 // if the original encoding was ASCII
 string x = Encoding.ASCII.GetString(binaryString);

 // if the original encoding was UTF-8
 string y = Encoding.UTF8.GetString(binaryString);

 // if the original encoding was UTF-16
 string z = Encoding.Unicode.GetString(binaryString);

 // etc

Solution 2

The binary data must be encoded text - and you need to know which encoding was used in order to accurately convert it back to text. So for example, you might use:

byte[] binaryData = reader[1];
string text = Encoding.UTF8.GetString(binaryData);

or

byte[] binaryData = reader[1];
string text = Encoding.Unicode.GetString(binaryData);

or various other options... but you need to know the right encoding. Otherwise it's like trying to load a JPEG file into an image viewer which only reads PNG... but worse, because if you get the wrong encoding it may appear to work for some strings.

The next thing to work out is why it's being stored as binary in the first place... if it's meant to be text, why isn't it being stored that way.

Solution 3

You need to know what encoding was used to create the binary. Then you can use

System.Text.Encoding.UTF8.GetString(reader[1]);

And change UTF8 for whatever encoding was used.