Write Image to SQL Server using Blob Literal String in C#

Question

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.

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
– Wim Ombelets, Commented Apr 4, 2012 at 8: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
– Paul, Commented Apr 4, 2012 at 20:07

Adrian Toman · Accepted Answer · 2012-04-04 04:36:55Z

1

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

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

edited Apr 4, 2012 at 4:36

answered Apr 4, 2012 at 4:12

Adrian Toman

11.5k6 gold badges50 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Over a year ago

Ha! 0x with no quotes. ie 0xFFD8FFE0... Thanks Adiran.

Collectives™ on Stack Overflow

Write Image to SQL Server using Blob Literal String in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related