Write Image to SQL Server using Blob Literal String in C# - 【StackMirror】|c#|sql|tsql|bytearray

I have a c# function that needs to write to a SQL image column. I have the image in a byte array. The standard seems to be the use of SqlCommand while passing the byte array as a parameter of type System.Data.SqlDbType.Image.

Unfotunately, my function can only use text queries (don't ask why) so I have to find a way to use T-SQL commands only. What I have so far can write to the column but I don't know what format of string to make the image blob string.

sql = "DECLARE @ptrval binary(16)" +
"SELECT @ptrval = textptr(Photo) FROM EMPhoto WHERE Employee='" + employeeID + "'" +
"WRITETEXT EMPhoto.Photo @ptrval " + imageByteArrayAsString;

I've tried converting imageByteArray to a Hex string and Binary string but it doesn't seem to end up correct in SQL or in the application that reads it.

2012-04-04 04:00
by Paul

Hi there. There's nothing unfortunate about that. Databases aren't meant to store images. Just because you can doesn't mean you should. Can you tell me what business requirement makes you want to sidestep local storage - Wim Ombelets 2012-04-04 08:52

Wimbo, I agree. Local storage would be my preference as well. The function I'm making is only middleware for a third party application. I don't have control over the application that uses sql image datatypes - Paul 2012-04-04 20:07

A T-SQL Binary constant is an unquoted hexidecimal string prefixed with 0x. ie 0xFFD8FFE0...

string imageByteArrayAsString = "0x" + BitConverter.ToString(image).Replace("-", string.Empty);

2012-04-04 04:12
by Adrian Toman

Ha! 0x with no quotes. ie 0xFFD8FFE0... Thanks Adiran - Paul 2012-04-04 04:35

No probs and good points - Adrian Toman 2012-04-04 04:39