c# - Why Sql Server Image In Binary Causes Issue in string Query but works in Parameterized Query

Question

I am saving Image in a table in VarBinary and its working.but the problem is if i save image using string query when i retrieve it it says parameter not valid although Image binary is save in database and i can see it. but if i use parametrized query and retrieve image it is displayed correctly:

Here is my parametrized query code:

     try
                    {
                        byte[] byteImg = ImageToByteArray(pictureBox1.Image);
                        connection = con.getConnection();
                        if (connection != null)
                        {
                            query = @"INSERT INTO [tblImages]
                               ([Image])
                         VALUES
                               (@image)";
                            connection.Open();
                            cmd = new SqlCommand(query, connection);
                            cmd.Parameters.AddWithValue("@image", byteImg);
                            cmd.ExecuteNonQuery();
                            MessageBox.Show("Saved");
                            connection.Close();
                            pictureBox1.Image = null;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

Database Row View Using Parameterized:

Here is my string query code:

 try
                {
                     byte[] byteImg = ImageToByteArray(pictureBox1.Image);
                    connection = con.getConnection();
                    if (connection != null)
                    {
                        query = @"INSERT INTO [tblImages]
                           ([Image])
                     VALUES
                           (Convert(varbinary(MAX),'" + byteImg + "'))";
                        connection.Open();
                        cmd = new SqlCommand(query, connection);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Saved");
                        connection.Close();
                        pictureBox1.Image = null;
                    }
                }
                catch
                {

                }

Byte Conversion Method:

public byte[] ImageToByteArray(Image img)       
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            return ms.ToArray();
        }

Database Row View:

Why is this happening why using Parametrized query binary is different in row and using string query it different, its very weird.

Any help will be appreciated.

Answer 1

You can actually just do:

INSERT INTO [tblImages]  ([Image])
VALUES 0x5379......

To build the binary string use code like this (buffer is your byte array)

_sbScript.Append("0x");

for (int i = 0; i < buffer.Length; i++)
{
    _sbScript.Append(buffer[i].ToString("X2",System.Globalization.CultureInfo.InvariantCulture));
}