How to convert string bytes into bytes[] and save into database

Question

I have some JSON data which contains an image field with bytes. I want to save these bytes into a database, but I am not able to convert the string into a byte array.

{
    "trackid": "TRXh82URBNA2878934",
    "serviceid": "66",
    "category": "URBN",
    "name": "sachin",
    "description": "Sanitations Work",
    "location": "Greater Noida",
    "requesterlocation": "new delhi",
    "email": "sachin@gmail.com",
    "contact_number": "882379823",
    "latitude": "78.23",
    "longitude": "30.23",
    "locationtype": "urban",
    "sectorid": "12",
    "userid": "34",
    "filename": "IMG001",
    "extension": ".jpg",
    "contenttype": "image/jpeg",
    "image": "0xFFD8FFE13F5745786966000049492A000800000012000E01020020000000E60000000F01020020000000060100001001020020000000260100001201030001000000010000001A01050001000000460100001B010500010000004E01000028010300010000000200000031010200200000005601000032010200140000007601000013020300010000000200000020020400010000000000000021020400010000000000000022020400010000000000000023020400010000000000000024020400010000000100000025020200200000008A0100006987040001000000AA01000025880400010000002C0300004504000000000000000000000000000000000000000000000000000000000000000000004F50504F000000000000000000000000000000000000000000000000000000005839303039000000000000000000000000000000000000000000000000000000480000000100000048000000010000004D6564696154656B2043616D657261204170706C69636174696F6E0A00000000323031363A31303A32312030393A34383A323200000000000000000000000000000000000000000000000000000000000000000019009A82050001000000DC0200009D82050001000000E40200002288030001000000000000002788030001000000320000000090070004000000303232300390020014000000EC02000004900200140000000003000001910700040000000102030004920A0001000000140300000792030001000000020000000892030001000000FF000000099203000"
}

I am facing an error while inserting the byte string into the database. Here is my code:

SqlCommand cmd = new SqlCommand("PROC_INSERT_CITIZENT_REQUEST_DOCUMENTS_FOR_WEB_API");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Connection;
Connection.Open();
cmd.Parameters.AddWithValue("@P_Cititzen_request_recno", oRequest.requestid);
cmd.Parameters.AddWithValue("@P_Category", oRequest.category);
cmd.Parameters.AddWithValue("@P_Filename", oRequest.filename);
cmd.Parameters.AddWithValue("@P_extension", oRequest.extension);
cmd.Parameters.AddWithValue("@P_contentType", oRequest.contenttype);
//byte[] newBytes = Convert.FromBase64String(oRequest.image);
cmd.Parameters.AddWithValue("@P_Doc_Data", Encoding.UTF8.GetBytes(oRequest.image));
cmd.Parameters.AddWithValue("@P_MSG", "");

j = cmd.ExecuteNonQuery();
if (j < 1)
{
    j = 0;
}

What am I doing wrong?

"I am facing error" doesn't tell us *anything* about the error you've seen, or where it is. It looks like you should be parsing the `image` field as hex - there are a lot of questions on Stack Overflow about parsing hex to bytes. — Jon Skeet, Nov 28 '16 at 06:34
@sachinbhatt And what if you just use `cmd.Parameters.AddWithValue("@P_Doc_Data", oRequest.image);`? What is a datatype of `@P_Doc_Data`? — gofr1, Nov 28 '16 at 07:44
@JonSkeet error is Invalid length for a Base-64 char array or string. — sachin bhatt, Nov 28 '16 at 07:49
@gofr1 Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. — sachin bhatt, Nov 28 '16 at 07:51
Well that doesn't look like base64 to me, so why are you using `Convert.FromBase64String`? (It's hex - see my first comment.) And the error message should be in the *question*. — Jon Skeet, Nov 28 '16 at 08:02

score 1 · Answer 1 · edited May 23 '17 at 12:13

First of all, your string is not Base64 encoded, nor is it UTF8 encoded. It is a string of hexadecimal digits. You need a way to convert from hex to bytes. This subject has already been covered to death on Stack Overflow; see How do you convert Byte Array to Hexadecimal String, and vice versa?

However, there is another problem: in order to convert from hexadecimal string using one of the answers in the linked question, the "0x" prefix must first be removed, and the length of the hex string must be even (because the hex digits are processed in pairs to make bytes). In your case, the length of the hex string after removing the prefix is 1155, so that means you are missing a digit. Presumably, the missing digit is a zero at the beginning of the string, because the "0x" notation customarily used for indicating hexadecimal numbers allows leading zeroes to be omitted. If that is true here, then you should be able to use the following code to do the conversion:

// strip off "0x" prefix
string hex = oRequest.image.Substring(2);    

// add leading 0 if length is odd
if (hex.Length % 2 == 1) hex = "0" + hex;    

// convert hex string to bytes
byte[] bytes = System.Runtime.Remoting.Metadata.W3cXsd2001.SoapHexBinary.Parse(hex).Value;

Then add it to your SqlCommand like this:

cmd.Parameters.AddWithValue("@P_Doc_Data", bytes);

How to convert string bytes into bytes[] and save into database

1 Answers1