Why Sql Server Image In Binary Causes Issue In String Query But Works In Parameterized Query

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

Solution 1:

You can actually just do:

INSERTINTO [tblImages]  ([Image])
VALUES0x5379......

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

Solution 2:

In the hex in the second data snapshot (0x53797374...) all the bytes spell out the type name ("System.Byte[]" maybe? I stopped translating...).

0x53 - 'S'
0x79 - 'y'
0x73 - 's'
0x74 - 't'
0x65 - 'e'
0x6D - 'm'

When you build the string using CONVERT(VARBINARY(MAX), '" + array + "')" the arrays ToString method is being called, which is not well defined on System.Array and returns the name of the type. Then your Sql looks like INSERT INTO ... (CONVERT(VARBINARY(MAX), 'System.Byte[]') and that will be inserted into the database in error.

Choosing the parameterized option would definately be the safer way to go (with respect to both Security, and potential bugs), but if you want to execute it as a SQL string, you will want to build a string of the actual bytes, as demonstrated in ErikEJ's answer.

Solution 3:

You have a different INSERT statement in the string query code.

The parametrized query code is:

query = @"INSERT INTO [tblImages]
                 ([Image])
          VALUES (@image)";

Whilst the string query code is:

query = @"INSERT INTO [tblImages]
                 ([Image])
          VALUES (Convert(varbinary(MAX),'" + byteImg + "'))";

Removing the CONVERT should woudl make the two statements insert in the same way.