Correctly store and retrieve PDF file using SQL database with .NET

Question

I have created a Web Application which sends PDF files to an SQL database table to later be loaded and sent via Email. Currently my code works and PDF file is being sent in Email, however once opening the PDF all is shown as expected except for the BACKGROUND IMAGE which is no longer present on the PDF. Now what I don't understand is that if I use the 'IFormFile' before it is sent to the database the PDF file when emailed shows correctly and includes its background image. Leading me to think I must be incorrectly converting the PDF file to/from binary format?

Please see my current code:

Javascript ajax to controller: ajax

if(email) {
   var file = doc.output('blob');
   var fd = new FormData();
   fd.append('pdfFile',file);
   fd.append('jobNo',$('#jobNoInput').val());
   $.ajax({
       url: "/Jobs/SendInvoiceEmailToQueue",
       data: fd,
       dataType: 'text',
       processData: false,
       contentType: false,
       type: 'POST',
       success: function(data) {
           var data2 = JSON.parse(data);
           if(data2 == "SUCCESS")
           {
               quickToast('Invoice added to Email Queue for Job 
No: ' + $('#jobNoInput').val(), 'success', 'Added to Email Queue');
           }
       }
   });
} else {
   doc.save('Invoice.pdf');
}

Upload PDF to database: upload pdf to database

public IActionResult SendInvoiceEmailToQueue(string jobNo)
{
var stream = Request.BodyReader.AsStream();
IFormFile pdfFile = Request.Form.Files[0];

string fileName = pdfFile.FileName;

var result = new StringBuilder();
using (var reader = new StreamReader(pdfFile.OpenReadStream()))
{
    result.Append(reader.ReadToEnd());
}

string base64 = result.ToString();
Byte[] bitmapData = new Byte[base64.Length];

var plainTextBytes = System.Text.Encoding.UTF8.GetBytes(base64);

connectionString();
con.Open();
com.Connection = con;

var query = "INSERT INTO dbo.email_queue([JOBNO],[PDF],[EMAILADDRESS],[DATE],[TIME]) VALUES(@jobNo, @pdf, 'address', GETDATE(), CURRENT_TIMESTAMP)";
SqlParameter picparameter = new SqlParameter();
picparameter.SqlDbType = SqlDbType.Image;
picparameter.ParameterName = "pdf";
picparameter.Value = plainTextBytes;
SqlParameter jobNoparameter = new SqlParameter();
jobNoparameter.SqlDbType = SqlDbType.Int;
jobNoparameter.ParameterName = "jobNo";
jobNoparameter.Value = Int32.Parse(jobNo);
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add(picparameter);
cmd.Parameters.Add(jobNoparameter);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();

return new JsonResult("SUCCESS");
}

Retrieve and send PDF via email: enter image description here

public IActionResult SendInvoiceEmail(string queueNo)
{
    connectionString();
    con.Open();
    com.Connection = con;
    com.CommandText = "SELECT * FROM dbo.recovery_email_queue WHERE EmailQueueRecNo = '" + queueNo + "'";
    dr = com.ExecuteReader();

    while (dr.Read())
    {
        var imageSource = System.Text.Encoding.UTF8.GetString(((byte[])dr["PDF"]));
        var pdffile = (byte[])dr["PDF"];
        
        var pdfBytes = Encoding.UTF8.GetBytes(imageSource);
        var stream = new MemoryStream(pdfBytes);

        IFormFile file = new FormFile(stream, 0, pdfBytes.Length, "invoice", "invoice.pdf");

        //attach pdf
        var attachment = new Attachment(file.OpenReadStream(), "invoice.pdf");

        using (MailMessage mail = new MailMessage())
        {
            mail.From = new MailAddress("fromAdd");
            mail.To.Add("add");
            mail.Subject = "Test Sending Invoice";
            mail.Body = "<h1>This is body</h1>";
            mail.IsBodyHtml = true;

            mail.Attachments.Add(attachment);
            using (SmtpClient smtp = new SmtpClient("smtp-mail.outlook.com", 587))
            {
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                smtp.UseDefaultCredentials = false;
                smtp.Credentials = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["emailUser"].ToString(), ConfigurationManager.AppSettings["emailPass"].ToString());
                smtp.EnableSsl = true;
                smtp.Send(mail);
            }
        }
    }

    return new JsonResult("SUCCESS");
}

The database column 'PDF' is of type varbinary(MAX) Pdf file generated using jsPDF

Answer 1

Okay I have managed to find a solution, the issue was in my understanding of conversion of the PDF file to binary format. We cannot use stream reader here as this is for TEXT!, once I changed code to read the bytes directly from the stream returned everything is working as intended and image is included from database loaded file.

Code changes to server side methods:

public async Task<IActionResult> SendInvoiceEmailToQueue(string jobNo)
    {
        IFormFile pdfFile = Request.Form.Files[0];

        var stream = pdfFile.OpenReadStream();
        var length = (int)stream.Length;
        byte[] data = new byte[length];
        await stream.ReadAsync(buffer: data, offset: 0, count: length);....

Reading from database:

while (dr.Read())
        {
            var pdffile = (byte[])dr["PDF"];
            
            var stream = new MemoryStream(pdffile);

            IFormFile file = new FormFile(stream, 0, pdffile.Length, "invoice", "invoice.pdf");

            //Attach pdf
            var attachment = new Attachment(file.OpenReadStream(), "invoice.pdf");....