Monday, May 16, 2011

Scripting image column data for INSERT statements

I earlier blogged about scripting datetime values for INSERT statements, or for example by use in the SubmitSQL method of the SQL Server Compact RemoteDataAccess API. In this post, I will show how to serialize byte arrays as a hexadecimal string. Notice that for the SubmitSQL method, there is a limit to the size of the SQL statement (not sure what is is, but 64 K is a good guess, I think). So if you have large images, that you want to send to your server, you are out of luck with this method.

Below is the code I currently have implemented in my ExportSqlCE INSERT statement generator.

if (dt.Columns[iColumn].DataType == typeof(Byte[]))
Byte[] buffer = (Byte[])dt.Rows[iRow][iColumn];
for (int i = 0; i < buffer.Length; i++)
_sbScript.Append(buffer[i].ToString("X2", System.Globalization.CultureInfo.InvariantCulture));

Notice the special “X2” string format, this is what performs the magic of creating the hex string. Prepending the string with 0x is required by SQL Server. (_sbScript is a StringBuilder)

There is a long discussion on Stackoverflow on how to do this faster, the code below seems to be the fastest:

private static string ByteArrayToHex(byte[] barray)


char[] c = new char[barray.Length * 2];

byte b;

for (int i = 0; i < barray.Length; ++i)


b = ((byte)(barray[i] >> 4));

c[i * 2] = (char)(b > 9 ? b + 0x37 : b + 0x30);

b = ((byte)(barray[i] & 0xF));

c[i * 2 + 1] = (char)(b > 9 ? b + 0x37 : b + 0x30);


return new string(c);


Maybe I should update my ExportSqlCe code? - Happy scripting!

No comments: