CODEWITHSUNDEEP
c#mysqlblob
mrb398
mrb398

Reputation: 1317

How to load MySQL blob into C# byte array

I have a mysql table with a Medium Blob field that I filled with PDFs.

The PDFs are stored in the field without issue, and are inserted using the following query:

INSERT INTO PDFHistory ( OrderId, PDFByteArray) VALUES ( 123456, Convert.ToBase64String(pdf.ByteArray))

I am unable to pull the record and populate a byte[] object. I get the error "The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding characters, or an illegal character among the padding characters."

dbManager.Open();
using (DataTable dt = dbManager.ExecuteDataTable(CommandType.Text, "SELECT * FROM PDFHistory WHERE OrderId = {0} ORDER BY Timestamp DESC LIMIT 1" ))
{
   if (dt.Rows.Count > 0)
   {
       byte[] PDFByteArray = Convert.FromBase64String(dt.Rows[0]["PDFByteArray"].ToString());
   }
}

Any insight is appreciated.

Edit:

The mysql blob record looks like this (in general)

JVBERi0xLjQKJ.....too much to post.....AAAAAAAAAA==

Upvotes: 2

Views: 5140

Answers (3)

Alex
Alex

Reputation: 1

Workable code in VB (NET CORE), try to use the same logic in C#

        Dim Buf1 As Byte() = New Byte() {}
        ReDim Buf1(100)
        _DB.RawSqlQuery(Of Byte())($"select aes_encrypt('{Text}','{KeyString}') as aes_encrypt", Function(X)
                                                                                                     CType(X("aes_encrypt"), Byte()).CopyTo(Buf1, 0)
                                                                                                     ReDim Preserve Buf1(CType(X("aes_encrypt"), Byte()).Length - 1)
                                                                                                     Return Nothing
                                                                                                 End Function)

Upvotes: 0

Sau001
Sau001

Reputation: 1654

I will answer this question from my knowledge of MSSQL and ADO.NET. I think similar principles apply when it comes to handling BLOB column type. If you want the Byte[] that was originally stored in the database then I do not see a need for conversion using FromBase64String unless the downstream application requires this explicitly.

How to get the raw byte array from the database?

//Assuming you have already retrieve an instance to the Datatable
byte[] raw=(byte[])dt.Rows[0]["PDFByteArray"]

MySql Reference

https://dev.mysql.com/doc/connector-net/en/connector-net-programming-blob-reading.html

ADO.NET Reference

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-binary-data https://support.microsoft.com/en-gb/help/309158/how-to-read-and-write-blob-data-by-using-ado-net-with-visual-c-net https://learn.microsoft.com/en-us/dotnet/api/system.data.idatarecord.getbytes?view=netframework-4.7.2

Upvotes: 1

John Lord
John Lord

Reputation: 2175

You can use a memorystream for this.

MemoryStream ms = new MemoryStream(PdfBlob);
                byte[] newPdf = ms.ToArray();
                ms.Close();

I would not convert it to and from base64 strings. That would only matter if you were displaying blob contents directly in a web page (an image).

Upvotes: 0

Related Questions