Executesqlcommand - Update byte - Error

Question

Follows code:

var bytes = ConvertTo.Bytes(file);

int num = ctx.Database.ExecuteSqlCommand(
    $"UPDATE dbo.Table" +
    $"SET Video = '{bytes}' " +
    $"WHERE id = {Id} ");

Follow code to convert:

public static byte[] Bytes(HttpPostedFileBase file)
{
    var length = file.InputStream.Length;
    byte[] fileData = null;
    using (var binaryReader = new BinaryReader(file.InputStream))
    {
        fileData = binaryReader.ReadBytes(file.ContentLength);
    }
    return fileData;
}

I get error:

Implicit conversion of data type varchar to varbinary(max) is not permitted. Use the CONVERT function to execute this query.

Some solution?

Answer 1

You should use darlings parameterized to do this job.

Anyway, you can do it this way:

var valor = "0x" + BitConverter.ToString(arraytoinsert).Replace("-", "");

int num = ctx.Database.ExecuteSqlCommand(
          $"UPDATE dbo.Table" +
          $"SET Video = {valor} " +
          $"WHERE id = {Id} ");

---

Using a parameterized query, it would look like this:

using(SqlCommand cmd = new SqlCommand("UPDATE dbo.Table SET Video = (@pVideo)", conn))
{
    cmd.Parameters.Add("@pVideo", SqlDbType.VarBinary, 8000).Value = bytes;
    cmd.ExecuteNonQuery();
}