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

If you can, I would suggest using `varbinary(MAX)`. `image` was deprecated in SQL Server 2005. — Thom A, Feb 21 '19 at 10:13
Hey @Larnu, Thanks for the suggestion. but i can't modify the data-type — Divya Agrawal, Feb 21 '19 at 10:16

score 4 · Answer 1 · answered Mar 07 '19 at 05:55

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.

score 3 · Answer 2 · answered Mar 02 '20 at 22:56

3

You can Convert binary to Base64 using XML and the hint "for xml path"

select file_name,ColumnWithBinary,ColumnToSwFinalResult
from TableWithBinary
cross apply (select ColumnWithBinary '*' for xml path('')) T (ColumnToSwFinalResult)
GO

answered Mar 02 '20 at 22:56

cain

709
2
10
18

1

FYI: If SQL Server Management is not giving the whole Base64 on the results it's because there is a default limit of 65535 chars. Change it on Tools -> Options -> Query Results -> SQL Server -> Results to Grid -> Maximun Characters Retrieved – javdromero Feb 10 '23 at 21:08

score 2 · Answer 3 · answered Feb 21 '19 at 10:27

I believe you need help on how to store image data in sql server.

You can create a table like below. Please note that you may not want to create database , just a table would suffice. If you already have a table where you want to save this data, you will have to make sure that the type of column is varbinary(max).

CREATE DATABASE MyDatabase;  
GO  
USE MyDatabase;  
GO  
CREATE TABLE MyImageDatabaseTable (Id int, BLOBData varbinary(max));  
GO

Then you can read the data as:

SqlConnection objConn = new SqlConnection(connectionString);  
objConn.Open();  
SqlCommand objCmd = new SqlCommand("Select * From MyImageDatabaseTable", objConn);  
SqlDataReader dr = objCmd.ExecuteReader();  
while(dr.Read())
{
     byte[] myImageByteArrayData = (byte[]) dr["BLOBData"];  

     string myImageBase64StringData = Convert.ToBase64String(myImageByteArrayData );
}

Hope this helps.

Shnugo · Answer 4 · 2019-02-21T10:37:43.927

1

Answering your question

How to retrieve base64 from database using this byte[] array?

SQL-Server will transform your binary to base64 implicitly when embedding it into XML.

Try this

--I fill a binary variable with some dummy content
DECLARE @SomeBinary VARBINARY(MAX) = CAST('This is a dummy content' AS VARBINARY(MAX));
--this is the resulting binary
SELECT @SomeBinary;
--Now you see what happens using FOR XML PATH
SELECT (SELECT @SomeBinary FOR XML PATH(''))

We can re-convert this easily

DECLARE @base64 VARCHAR(MAX)='VGhpcyBpcyBhIGR1bW15IGNvbnRlbnQ=';
SELECT CAST(@base64  AS XML).value('.','varbinary(max)');

Probably you'll have to use a CAST(YourImageTypedColumn AS VARBINARY(MAX)) first.

But it might be easier to retrieve this as binary and to the conversion on your client side application.

edited Feb 21 '19 at 10:37

answered Feb 21 '19 at 10:28

Shnugo

66,100
9
53
114

Hello @shnugo, but I am not getting the same base 64 string as I had earlier. I first converted Base64 string into byte[] in C#, then I saved it into the database in a column of IMAGE type now I want to retrieve the exact Base64 string that I had. – Divya Agrawal Feb 21 '19 at 12:33
@DivyaAgrawal Do you mean, that your binary is a base64 string and not the byte array representing a picture? Just try `CAST(yourBinaryColumn AS VARCHAR(MAX) ` – Shnugo Feb 21 '19 at 12:37
It is giving me error. Explicit conversion from data type image to varchar(max) is not allowed. – Divya Agrawal Feb 21 '19 at 12:41
@DivyaAgrawal As told above you must Cast to varbinary(max) first – Shnugo Feb 21 '19 at 12:43
Please look at the question again I have edited it, may be previously it was not clear. – Divya Agrawal Feb 21 '19 at 12:58

score 1 · Answer 5 · edited Apr 06 '22 at 11:42

1

I use this method below, to convert bytearray from database to an image.

public Bitmap ConvertByteArrayToBitmap(byte[] Array)
{
    if (Array == null) return null;

    using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
    {
        ms.Write(Array, 0, Array.Length);
        ms.Position = 0L;

        return new Bitmap(ms);
    }
}

Regards.

edited Apr 06 '22 at 11:42

Mark Schultheiss

32,614
12
69
100

answered Feb 21 '19 at 11:16

Dionei Beilke Dos Santos

11
3

Sorry but i did not get it. It is not the solution for which I am looking for. – Divya Agrawal Feb 21 '19 at 12:37

score 1 · Answer 6 · answered Nov 06 '20 at 06:56

I got it!!!

C# code

string base64Encoded = Convert.ToBase64String(((byte[])result.photoFromSQLImageColumn));
string base64Decoded;
byte[] data = System.Convert.FromBase64String(base64Encoded);
base64Decoded = System.Text.ASCIIEncoding.ASCII.GetString(data);

and in this variable -----> base64Decoded you will have your rigth base64 string!!

Greetings from México!!!

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

6 Answers6

Linked