Convert Image data type into Base64 and Base64 into Image data type

Question

I'm using Data-Type "Image" in MS SQL 2012 to store Image.

problem: I have an image in BASE64 string in C#

/9j/4AAQSkZJRgABAQEASABIAAD/4SKhRXhpZgAATU0AKgAAAAgABwESAAMAAAABAAEAAAEaAAUAAAABAAAAYgEbAAUAAAABAAAAagEoAAMAAAABAAIAAAExAAIAAAAeAAAAcgEyAAIAAAAUAAAAkIdpAAQAAAABAAAApAAAANAALcbAAAAnEAAtxsAAACcQQWRvYmUgUGhvdG9zaG9wIENTNiAoV2luZG93cykAMjAxNjowMjowNSAxNDo1MTo0MwAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAFUKADAAQAAAABAAACWAAAAAAAAAAGAQMAAwAAAAEABgAAARoABQAAAAEAAAEeARsABQAAAAEAAAEmASgAAwAAAAEAAgAAAgEABAAAAAEAAAEuAgIABAAAAAEAACFrAAAAAAAAAEgAAAABAAAASAAAAAH/2

I am converting it into byte[] in C# as I need to save it into a column of data type IMAGE. Like this:

byte[] imageInByteArray =Convert.FromBase64String("MyImage");

It is saved successfully like this:

The byte[] array data in database -

Now I am trying to retrieve an image and converting it back into BASE64 using this:

var imageA = results.Read<byte[]>().ToArray();
string imageB =Convert.ToBase64String(imageA);

Now I am getting the result like this:

MHhGRkQ4RkZFMTAwNTg0NTc4Njk2NjAwMDA0RDREMDAyQTAwMDAwMDA4MDAwNDg3NjkwMDA0MDAwMDAwMDEwMDAwMDAzRTAxMTIw

The result is not what I was expecting it should be like this

/9j/4AAQSkZJRgABAQEASABIAAD/4SKhRXhpZgAATU0AKgAAAAgABwESAAMAAAABAAEAAAEaAAUAAAABAAAAYgEbAAUAAAABAAAAagEoAAMAAAABAAIAAAExAAIAAAAeAAAAcgEyAAIAAAAUAAAAkIdpAAQAAAABAAAApAAAANAALcbAAAAnEAAtxsAAACcQQWRvYmUgUGhvdG9zaG9wIENTNiAoV2luZG93cykAMjAxNjowMjowNSAxNDo1MTo0MwAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAFUKADAAQAAAABAAACWAAAAAAAAAAGAQMAAwAAAAEABgAAARoABQAAAAEAAAEeARsABQAAAAEAAAEmASgAAwAAAAEAAgAAAgEABAAAAAEAAAEuAgIABAAAAAEAACFrAAAAAAAAAEgAAAABAAAASAAAAAH/2

Answer 1

I found the solution for this, Please look into this because it might be useful when you do not want to change your datatype Image in your database:

I sent base64 string as it is to database and there I converted it into varbinary like this:

SELECT CAST(N'' AS xml).value('xs:base64Binary(sql:variable("@Image2"))', 'varbinary(max)')

and inserted it into the image column.

then I retrieved an image in base64 like this:

 SELECT cast('' as xml).value('xs:base64Binary(sql:column("img"))', 'varchar(max)') FROM imageTemp WHERE...

It gave me the exact Base64 string which I have sent earlier.

Thank You.