SQL Server VARBINARY(max) to c# byte[]

I am querying the table (one of the columns is a VARBINARY(MAX)) which returns some records.

Then I save that as .dat.csv then I parse through that .dat file and get that varbinary value into a string by splitting the file based on commas. Now I need to convert this varbinary to byte array. How can I do that?

Answer

Good question. Technically, you can do this by first converting to a character array, then converting to bytes. However, strings in .NET are Unicode-encoded by default (instead of ASCII), so it gets tricky.

If at all possible, you should try to pull the varbinary out of the file as a byte array, using the FileStream you’re reading from instead of the StreamReader which performs encoding conversions to and from the file encoding type.

The problem with this byte-to-string-to-byte babelfishing is that certain bytecodes have special meaning in each Unicode encoding, giving information to the decoder about the number of bytes it should pull to decode the next character. When converting between various Unicode encodings and the .NET-native UTF-8 encoding for strings, bytes will be gained, lost, and changed. When it’s a string, no biggie; the encoding information stays with the string. When it’s binary data, the encoding and decoding can garble it unless it’s done in a very specific way.

The only way this will work flawlessly is if you write the file out using ASCII encoding, then read it back in as such, which will cause each individual byte to be treated as a single character. You can then simply convert each char back to a byte, and the more significant byte of the UInt16 behind the scenes of the Syetem.Char, which is just zero-padding for the byte fed in to that char, will be discarded.

var reader = new StreamReader(new FileStream("test.csv"), Encoding.ASCII);
var varBinaryString = reader.Read(<wherever the varbinary is in the file/line>);

var byteArray = varBinaryString.ToCharArray().Select(c=>(byte)c).ToArray();

Technically, you could pull it in using any Unicode encoding as well, but you need to know a lot of specifics about how you wrote out those bytes and how the reader is reading them back in, so that you can perform the correct encoding and expansion (or deflation) as necessary to get the original bytestream.

EDIT: The .NET 2.0 version – no Linq:

StreamReader reader = new StreamReader(new FileStream("test.csv"), Encoding.ASCII);
string varBinaryString = reader.Read(<wherever the varbinary is in the file/line>);

char[] charArray = varBinaryString.ToCharArray();
byte[] byteArray = new byte[charArray.Length];

for(int i=0; i< charArray.Length; i++)
{
    byteArray[i] = (byte)charArray[i];
}