Varbinary text representation to byte[]

Question

In C#, how can I take the textual output that SQL Server Management Studio shows as the contents of a varbinary column, and turn that text into the byte[] that is stored in the column?

Answer 1

OK, so you want to copy and paste the value displayed in SSMS for a varbinary column (e.g. "0x6100730064006600"), and get the byte[] from it in C#?

That's quite easy - the part after 0x is just hex values (2 characters each). So, you take each pair, convert it to a number specifying a base of 16 (hex), and add it to a list, like so:

string stringFromSQL = "0x6100730064006600";
List<byte> byteList = new List<byte>();

string hexPart = stringFromSQL.Substring(2);
for (int i = 0; i < hexPart.Length / 2; i++)
{
    string hexNumber = hexPart.Substring(i * 2, 2);
    byteList.Add((byte)Convert.ToInt32(hexNumber, 16));
}

byte [] original = byteList.ToArray();

Disclaimer - fairly dodgy and unoptimal code, I just hacked it together for demonstration purposes (it should work though).

Answer 2

    SqlConnection conn = new SqlConnection(yourConnectionString);
    SqlCommand cmd = new SqlCommand("select col from yourtable", conn);
    SqlDataReader reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        byte[] bytes = (byte[])reader[0];
    }
    reader.Close();
    conn.Close();