Converting an SQL image to Base64 in C#

Question

I have a database storing Binary JPEG Images with two different file signatures (FFD8FFE0 and FFD8DDE1) and I would like to convert them into Base64 so I can use them in another application (Power BI). The data is stored as an IMAGE field type, however I only receive the data in a CSV file and import into my tool as a string and work with it from there.

For the file signature FFD8FFE0, I have no problem converting using the below code (from another Stack post - thank you):

    public static string ToBase64(String sBinary)
    {
        int noChars = sBinary.Length;

        byte[] bytes = new byte[noChars / 2];

        for (int i = 0; i < noChars; i += 2)
        {
            bytes[i / 2] = Convert.ToByte(sBinary.Substring(i, 2), 16);
        }
        return Convert.ToBase64String(bytes);
    }

However, the file signature FFD8FFE1 is not converting and displaying properly to an image. It gives me an output, but does not display properly.

Any advice? Is this because of the different file signature OR because of the size of the string (they are noticeably larger).

EDIT: Thank you everyone who assisted. As mentioned in the comments, the real issue was the data I was trying to convert - it was being truncated in the CSV. So for anything who ever comes across this post, pull directly from SQL and not a text file as there is a good chance the data will be truncated.

Answer 1

It sounds like you exported the binary data from an admin tool's query results. Such tools will almost always truncate the displayed binary results to conserve memory.

It's better and easier to read the data directly from the database using ADO.NET or a micro-ORM like Dapper to reduce the boilerplate code.

Using Dapper you could write something as simple as:

var sql="select image from MyTable where Category=@category";

using var connection=new SqlConnection(connectionString);

var images=connection.Query<byte[]>(sql,new {category="Cats"});

And convert it with :

var cats64=images.Select(bytes=>Convert.ToBase64String(bytes));

Dapper will handle opening and closing the connection, so we don't even have to do that.

If you want to retrieve more fields, you can define a class to accept the results. Dapper will map the result columns to properties by name. Once you have a class, you can easily add a method to return the Base64 string:

class MyData
{
    public string Name{get;set;}
    public byte[] Image {get;set;}

    public string ToBase64()=>Convert.ToBase64String(Image);
}


....

var images=connection.Query<MyData>("select Name,Image From ....",...);

Using plain old ADO.NET needs a few more lines:

var sql="select image from MyTable where Category=@category";
using var connection=new SqlConnection(connectionString);
using var cmd=new SqlCommand(sql,connection);
cmd.Parameters.Add("@category",SqlDbType.NVarChar,20).Value="Cats";
using var reader=cmd.ExecuteReader();

while(reader.Read())
{
    var image=(byte[])reader["image"];
    ...
}

With ADO.NET though, it's also possible to load the data as a stream instead of loading everything into memory. This is very helpful when the image is large because we avoid caching the entire blob in memory. We could write the stream to a file directly without first loading it in memory :

while(reader.Read())
{
    using (var stream=reader.GetStream(0))
    using (var file=File.Create(somePath))
    {
        stream.CopyTo(file);
    }
}

There's no overload of Convert.ToBase64String that works with streams. It's possible to use the CryptoStream class with a Base64 transform to convert a stream to encode an input stream into Base64 as this SO answer shows. Adopting it to this case:

while(reader.Read())
{
    using (var stream.      = reader.GetStream(0))
    using (var base64Stream = new CryptoStream( stream, new ToBase64Transform(), CryptoStreamMode.Read ) )
    using (var outputFile   = File.Create(somePath) )
{
    await base64Stream.CopyToAsync( outputFile ).ConfigureAwait(false);
}

Answer 2

If you are looking to convert to a base64 jpg url:

public static string ToBase64PNGUrl (byte[] bytes) =>
    $"data:image/jpg;base64,{Convert.ToBase64String(bytes)}";

Answer 3

You are mapping the results from the database incorrectly, this field should be byte[], not string.

I assume you are receiving a hexadecimal representation of the bytes, which you could convert to bytes then convert to base64 as you attempted (Convert.ToBase64String(bytes)).

Try using EF-code first to read from table and define the image property as byte[].