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];
    _sbScript.Append("0x");
    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!