Storing C# Converted base64 as SQL Server varbinary(max)

Question

I've got an angular webapp where I accept images in base64 and store them in T-SQL varbinary. However, I have existing data stored in SQL-Server that appears to be in a different binary. I need both binaries to work with the same Read/Update methods in C#.

In the service I'm trying to convert a base64 string to SQL Server's varbinary and vice versa.

Manually in SSMS, I can take just the base64 string and insert into a row like this:

Cast(@base64ImgStr as varbinary(max))

But the result is different when I try to insert from C#:

Convert.FromBase64String(base64);

(same input). Since the binary is different the image doesn't show. I need a method in C# that provides the same binary as the one in T-SQL.

I must be around the solution, since I can cast and get the proper base64 in SQL Server (again, not in C#) like this:

cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

Instead of base64 I get back Unicode symbols, otherwise I'd paste in an example.

I need to get the base64 without calling the SQL Server base64Binary method, because my service reads images like this:

Encoding.ASCII.GetString(varbinary)

If I convert the manually inserted varbinary (Cast(@base64ImgStr as varbinary(max))) into varchar(max), I get a base64 string and the image displays appropriately in my webapp. But, this is not so when I use the C# method to create the binary. With the type of varbinary I get from Convert.FromBase64String(base64), I need an intermediate conversion in SQL Server (xs:base64binary) to reverse the binary to base64. With no intermediate conversion I get Unicode symbols and no image displays in the webapp. I don't want an intermediate conversion because I want consistent results whether users upload images or someone inserts images manually.

I don't see a method on the Convert class that gives me the same type of binary. But that seems to be exactly what I need. I suspect Unicode. Please help!

To store the image itself the result of Convert.FromBase64String(base64); should be stored. Cast(@base64ImgStr as varbinary(max)) would store a binary representation of the base64 encoded image - are you decoding from/using base64 when you read back? — Alex K.
– Alex K., Commented Dec 28, 2014 at 15:59
here's what i do to store the updated image: pm.SponsorImg = Convert.FromBase64String(vm.sponsorImg); (continuing answer - brb) — jacoblambert
– jacoblambert, Commented Dec 28, 2014 at 16:02
so i believe i am storing the result of Convert.FromBase64String(base64). but that byte array does not show the same varbinary as is stored when i take the same image and do Cast(@base64ImgStr as varbinary(max)). here's how i'm reading it back: var base64 = pm.SponsorImg != null ? Encoding.ASCII.GetString(pm.SponsorImg) : String.Empty; I need to continue to be able to read back this way because images will be both converted manually and uploaded via angular. — jacoblambert
– jacoblambert, Commented Dec 28, 2014 at 16:41
You seem to be going wrong with Encoding.ASCII.GetString(varbinary). If varbinary is your data and you want a base64 encoded string you should just use Convert.ToBase64String to convert the byte array to. What you have says that the binary input is the binary representation of a string (ie the bytes represent ascii characters, not base64 encoded bytes). — Chris
– Chris, Commented Dec 28, 2014 at 22:21
@stackoverflow.com/users/338068/chris , it seems that you provided the answer. i'm sorry i did not understand your recommendation at first. not sure how to give you more credit - thanks for your help. — jacoblambert
– jacoblambert, Commented Dec 29, 2014 at 19:38

Pang · Accepted Answer · 2018-01-23 06:12:29Z

9

This worked for me:

var image = Convert.FromBase64String(base64);
string hex = BitConverter.ToString(image);
hex = hex.Replace("-", "");
hex = "0x" + hex;

This hex can be saved in a varbinary(MAX) field without casting or converting and will show as an image in the browser.

edited Jan 23, 2018 at 6:12

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jan 23, 2018 at 4:47

Logan412

1091 silver badge3 bronze badges

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

2 Comments

Riples Over a year ago

Is there a PHP equivalent to this?

Logan412 Over a year ago

I am not aware.

Collectives™ on Stack Overflow

Storing C# Converted base64 as SQL Server varbinary(max)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related