generating sql script for byte array insertion

Question

I have been asked to generate scripts for a certain set of operations (basically insertion of product information for an ecommerce portal), and execute the generated script. The problem I'm facing is that we are storeing all images as binary data in a table. Now how am I supposed to script a query for this, Im getting a type mismatch when I tried to insert the byte array in the form of a string. This is what I tried.

//imgbyte is the byte array containing the piucture data
StringBuilder sb=new StringBuilder();
sb.AppendLine("declare @picquery as Varchar(4000)");
sb.AppendLine("set @picquery='Insert into Picture(PictureBinary) values (''"+imgbyte.ToString() +"'')'");
sb.AppendLine("exec(@picquery)");
// sb is then passed to another module where it is executed.

But the type for the binary data is wrong and the insert query fails. What am I doing wrong. the column PictureBinary is VarBinary(MAX)

Do you have the option of using prepared statements rather than generating a string of SQL? If you use prepared statements then .NET will handle the conversion for you. — mroach, Feb 21 '17 at 05:43
I have to script them and keep it ready. the script will be executed at a later time. — Sujit.Warrier, Feb 21 '17 at 06:18

score 8 · Accepted Answer · edited Aug 19 '21 at 18:31

For writing binary data, SQL Server expects the data to be in hexadecimal format with a leading 0x. Example:

INSERT INTO images (name, image) VALUES ('photo.jpg', 0xAF03083FCE...)

In general when interacting with the database you're best off using parameterised queries and letting .NET write the final SQL for you. It will automatically convert byte arrays to the correct format.

Parameterised query

// assuming 'cn' is a SqlConnection that's already open
var commandText= "INSERT INTO Picture (PictureBinary) VALUES (@bin)";
using (var cmd = new SqlCommand(commandText, cn))
{
    cmd.Parameters.Add("@bin", SqlDbType.Binary, imgByte.Length).Value = imgByte;
    cmd.ExecuteNonQuery();
}

Manual query building

If for some reason you do need to construct the query by hand, this is how you would do it:

// convert the byte array to a hex string
var hexString = BitConverter.ToString(imgByte).Replace("-", "");
var sql = string.Format("INSERT INTO Picture (PictureBinary) VALUES (0x{0})", hexString);

Note: Using BitConverter is one of many ways to convert bytes to hexadecimal in C#. Here's a great SO answer comparing performance

Syntactical Error Fix: String.Format – Yaman Jun 15 '21 at 01:41 — Yaman, Jun 15 '21 at 01:41

generating sql script for byte array insertion

1 Answers1