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

Question

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 1

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];
}

Answer 2

I have founded more simple solution for the case when you have only one or serveral values in SQL resultset and don't want to convert it to *.csv and so on.

The case when copy-pasting the SQL-varbinary-values is acceptable.

using System;
using System.Text;

namespace TestProj
{
    class Program
    {
        static void Main(string[] args)
        {
            //the value from database (Ctrl+C & Ctrl+V)
            string varbinaryStr = "0x7B2255";  

            //remove '0x' prefix AND trailing spaces
            var no_0x_varbinary_str = varbinaryStr.Replace("0x", "").Trim();

            //the bytes
            byte[] bytes = StrToByteArray(no_0x_varbinary_str);
            
            //now you can work with the bytes as you need
            //in my case there was an UTF8-string that have been saved in SQL as varbinary
            //so I am just decode it like this
            var string_from_database = Encoding.UTF8.GetString(bytes);
            
            Console.ReadKey();
        }
        
        static byte[] StrToByteArray(string str)
        {
            Dictionary<string, byte> hexindex = new Dictionary<string, byte>();
            for (int i = 0; i <= 255; i++)
                hexindex.Add(i.ToString("X2"), (byte)i);

            List<byte> hexres = new List<byte>();
            for (int i = 0; i < str.Length; i += 2)
                hexres.Add(hexindex[str.Substring(i, 2)]);

            return hexres.ToArray();
        }
    }
}

The example of string to byte[] convertion that have been used in StrToByteArray I found in this question where you can find other refference to the discussion of the fastest convertion way (with examples and tests).