How convert a Byte[] to a data to insert a value in a varbinary(max) column in SQL Server?

Question

I have a object with a byte[] property, and I would like to convert this value to the correct value to can insert it into the database using T-SQL.

But I don't know how I could convert the byte[] to the correct value for T-SQL for the insert.

Thanks.

Answer 1

Create a Console Application project and try this code

// Sample Class
public class MyClass
{
    public byte[] data;
}

// Main 
static void Main(string[] args)
{
    MyClass cls = new MyClass();
    using (SqlConnection cn = new SqlConnection("CONNECTION STRING"))
    {
        cn.Open();
        using (SqlCommand cmd = new SqlCommand("insert into MyTable values (@data)", cn))
        {
            cmd.Parameters.AddWithValue("@data", cls.data);
            cmd.ExecuteNonQuery();
        }
    }
}

Answer 2

You want to convert to VarBinary.

See the following:

SQL Server Data Type Mappings

simple example (setting command parameter)

byte[] data;
command.Parameters.Add("@data", SqlDbType.VarBinary).Value = data;

T-SQL example of how to pass in varbinary

CREATE PROCEDURE YourStoredProc
    @data varbinary(max)
AS
BEGIN
  -- your code
END

Answer 3

Generating raw varbinary to insert to database (copy-paste case)

string ToVarbinary(byte[] data)
    {
        var sb = new StringBuilder((data.Length * 2) + 2);
        sb.Append("0x");

        for (int i = 0; i < data.Length; i++)
        {
            sb.Append(data[i].ToString("X2"));
        }

        return sb.ToString();
    }