1

I am trying to save a PDF file that is generated from a user form in my webpage. Generating the PDF and sending it via mail works great, however trying to save it to SQL database is proving to be a bit problimatic

So this is my code I am using to generate the PDF:

 Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
        using (MemoryStream memoryStream = new MemoryStream())
        {
            PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
            pdfDoc.Open();

            string imageURL = Server.MapPath(".") + "../../dist/img/PDF_Header.png";
            iTextSharp.text.Image jpg = iTextSharp.text.Image.GetInstance(imageURL);
            jpg.ScaleToFit(1000f, 113f);
            jpg.SpacingBefore = 10f;
            jpg.SpacingAfter = 1f;
            jpg.Alignment = Element.ALIGN_CENTER;
            Font FONT = new Font(Font.TIMES_ROMAN, 12, Font.BOLD);

            Paragraph par1 = new Paragraph(lblClientHeaderNew.Text.ToString() + " New User Request - " + txtNewFirstName.Text.ToString() + " " + txtNewLastName.Text.ToString(), FONT);
            par1.SpacingAfter = 4f;
            par1.SpacingBefore = 5f;
            par1.Alignment = Element.ALIGN_CENTER;

 ....
            string imageFooter = Server.MapPath(".") + "../../dist/img/PDF_Footer.png";
            iTextSharp.text.Image jpgfooter = iTextSharp.text.Image.GetInstance(imageFooter);
            jpgfooter.ScaleToFit(1000f, 90f);
            jpgfooter.SpacingBefore = 250f;
            jpgfooter.SpacingAfter = 250f;
            jpgfooter.Alignment = Element.ALIGN_CENTER;
 
            pdfDoc.Add(jpg);
            pdfDoc.Add(jpgfooter);
            pdfDoc.Close();

The when sending the email with the PDF attachment I use the following code:

 string companyName = lblClientHeaderNew.Text.ToString();
            byte[] bytes = memoryStream.ToArray();
            bytes.ToArray();
            string to = "xxxx@xxx.com";
            string from = "xxxx@xxx.com";
            MailMessage message = new MailMessage(from, to);
            message.To.Add("xxxx@xxx.com");

            string mailbody = "Hi Billings," + "<br>" + "<br>" +
                "Please find attached new user request for " + lblClientHeaderNew.Text.ToString() + "<br>" + "<br>" +
                "User:    " + txtNewFirstName.Text.ToString() + " " + txtNewLastName.Text.ToString() + "<br>" +
                "Thanks";
            Attachment pdfAttachment = new Attachment(new MemoryStream(bytes), "New User Request - " + companyName + " - " + txtNewFirstName.Text.ToString() + " " + txtNewLastName.Text.ToString() + ".pdf");
            message.Subject = "New User Request For " + lblClientHeaderNew.Text.ToString();
            message.Body = mailbody;
            message.Attachments.Add(pdfAttachment);
            message.BodyEncoding = Encoding.UTF8;
            message.IsBodyHtml = true;
            SmtpClient client = new SmtpClient("smtpserver", 587);
            System.Net.NetworkCredential basicCredential1 = new
            System.Net.NetworkCredential("xxxxx@xxxx,com", "xxxxxx");
            client.EnableSsl = true;
            client.UseDefaultCredentials = false;
            client.Credentials = basicCredential1;
            try
            {
                client.Send(message);
                Toastr.ShowToast("New User Form", "Your Form Has Been Sent Successully!", Toastr.Type.Success);
            }

            catch (Exception ex)
            {
                throw ex;
            }

And so here is where I am completely stuck to save the attachment only generated to my SQL database

What I am trying to do is when user clicks on the button its send the mail with attachment and automatically saves the attachment in the database.

What I have tried is the following:

string clientName = lblClientHeaderNew.Text;
            string userName = txtNewFirstName.Text + " " + txtNewLastName.Text;

            string query = "INSERT INTO tbl_Submitted_Client_Forms (ClientName, UserName,    Attachment) VALUES (@ClientName, @UserName, @Attachment)";
            string constr =    ConfigurationManager.ConnectionStrings["ClientConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Parameters.AddWithValue("@ClientName", clientName);
                    cmd.Parameters.AddWithValue("@UserName", userName);
                    cmd.Parameters.AddWithValue("@Attachment", bytes.ToArray());
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }

This code is working but not as expected. It save it to the database but saves the attachment as and not the actual PDF attachment.

I have tried various solutions but most of them points to either a file upload or pdf download to local drive.

Thanks

Enthusiast
  • 21
  • 4
  • Does this answer your question? [insert a BLOB via a sql script?](https://stackoverflow.com/questions/2607326/insert-a-blob-via-a-sql-script) – Mingye Wang Mar 16 '23 at 19:57

1 Answers1

1

Okay so I have finally figured it out.

Created the file to a folder

string filename = "New User Request - " + companyName + " - " + txtNewFirstName.Text.ToString() + " " + txtNewLastName.Text.ToString() + ".pdf";
string filepath = Server.MapPath(@"~\App_Data\");
File.WriteAllBytes(filepath + filename , bytes);
memoryStream.Close();

Then save that file to the database

string filePath = Server.MapPath(@"~\App_Data\" + filename);
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] bytespdf = br.ReadBytes((Int32)fs.Length);
br.Close();
fs.Close();
cmd.Parameters.AddWithValue("@Attachment", bytespdf);
Enthusiast
  • 21
  • 4