CODEWITHSUNDEEP
asp.netsql-server
Sujit.Warrier
Sujit.Warrier

Reputation: 2869

generating sql script for byte array insertion

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)

Upvotes: 1

Views: 4363

Answers (1)

mroach
mroach

Reputation: 2468

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

Upvotes: 8

Related Questions