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!

Answer 1

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.