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.

score 7 · Answer 1 · edited Apr 12 '17 at 18:52

7

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

edited Apr 12 '17 at 18:52

Scott Chamberlain

121,188
31
271
414

answered Apr 12 '17 at 17:02

William Xifaras

4,974
2
17
19

it would be good to show how you use the parameter in the query too, if the OP has never used parameters before he may not know how to use it. – Scott Chamberlain Apr 12 '17 at 17:08
Thanks for the use of AddWithValue. It helps me. – Álvaro García Apr 12 '17 at 18:24
@ÁlvaroGarcía that is the wrong way to use AddWithValue, I have corrected it to use the correct function. – Scott Chamberlain Apr 12 '17 at 18:52

FLICKER · Accepted Answer · 2017-04-12T18:56:24.433

7

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();
        }
    }
}

edited Apr 12 '17 at 18:56

answered Apr 12 '17 at 17:10

FLICKER

5,950
3
40
69

1

The overload of `Add` you are calling is marked obsolete. You should use `.AddWithValue("@data", cls.data);` or `.Add("@data", SqlDbType.VarBinary).Value = cls.data;` – Scott Chamberlain Apr 12 '17 at 18:52
@ScottChamberlain, Thanks for notifying me. I updated my answer – FLICKER Apr 12 '17 at 18:56

score 1 · Answer 3 · answered Dec 27 '21 at 14:01

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();
    }

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

3 Answers3

Linked