Save and Retrieve PDF from SQL DB using SSRS and ASP.NET

Introduction

I started to build an application to retrieve the data from DB and to put the same in one PDF format and to trigger it as a mail. For the requirement, I used SSRS report SQL Server and ASP.NET.

Background

Before creating one solution, we need to consider the frequency of change, it may frequently change or remain the same. For that, we need to think of the solution which should be less interdependent. Hence for the requirement to send the data in the mail as an attachment(PDF) along with some content, we tried using the SSRS report for generating the attachment. And to maintain a backup of all the mails, we stored the PDF/attachment in SQL DB and then triggered the mail.

Using the Code

To start coding, we will first create one SSRS report.

Let's consider an SSRS report named ABC.

After creating an SSRS report, let's create one table in the DB.

After creating the table in the database, create one web page and put two controls onto the page.

1. ScriptManager
2. ReportViewer

<asp:ScriptManager id="ScriptManager1" 
       runat="server" enablepagemethods="true"  />
     <%--   <asp:ScriptManager id="ScriptManager1" 
     runat="server" enablepagemethods="true" 
     xmlns:asp="#unknown" />--%>
    
        <rsweb:ReportViewer ID="ReportViewer1" runat="server">
</rsweb:ReportViewer>

After that, write the CS file code as shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Data;
using System.Net;
using System.Net.Mail;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Windows;
using iTextSharp.text.html.simpleparser;
using System.ComponentModel;

namespace Scratch
{
    public partial class emailssrs : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection
        (ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            Microsoft.Reporting.WebForms.ReportViewer ReportViewer1 = 
                                new Microsoft.Reporting.WebForms.ReportViewer();

            //set ProcessingMode to ReportViewer either Remote/Local

            ReportViewer1.ProcessingMode =
            Microsoft.Reporting.WebForms.ProcessingMode.Remote;

            //provide Report Server Url

            ReportViewer1.ServerReport.ReportServerUrl = 
               new Uri("http://bdc7-l-6j194y1/ReportServer_MSSQLSERVER1");

            /*Note: For loca report there is no ReportServerUrl property,
            it is in local solution folder, only report path is enough*/

            //provide Report Path
            ReportViewer1.EnableViewState = true;
            ReportViewer1.ServerReport.ReportPath = "/aus/order_report";

            //creating Report Parameters collection

            //Microsoft.Reporting.WebForms.ReportParameter[] 
            //rptParams = new Microsoft.Reporting.WebForms.ReportParameter[3];
            //rptParams[0] =   new Microsoft.Reporting.WebForms.ReportParameter("ID", "1729");

            //ReportViewer1.ServerReport.SetParameters(rptParams);

            //declare variable need to render report

            //in parameters

            string format = "PDF",
                devInfo = @"<DeviceInfo><Toolbar>True</Toolbar></DeviceInfo>";

            //out parameters

            string mimeType = "",encoding = "", 
            fileNameExtn = "";string[] stearms = null;
            Microsoft.Reporting.WebForms.Warning[] warnings = null;

            byte[] result = null;

            //try
            //{
            //render report, it will returns bite array

            result = ReportViewer1.ServerReport.Render(format, devInfo, out mimeType, 
                     out encoding, out fileNameExtn, out stearms, out warnings);
           
            con.Open();

            string query = "insert into email values (@id, @email)";
            SqlCommand cmd = new SqlCommand(query);

            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@id", 3);
            //cmd.Parameters.AddWithValue("@ContentType", contentType);
            cmd.Parameters.AddWithValue("@email", result);

            cmd.ExecuteNonQuery();
            con.Close();

//fetching the PDF file from DB to send it as an email
            con.Open();

            SqlCommand cmd1 = new SqlCommand("SELECT top 1 email from email where id = 3",con);

            SqlDataReader sdr = cmd1.ExecuteReader();

            //Get  Data
            sdr.Read();
            byte[] bytes = (byte[])sdr["email"];
            string fileName = "ABC.pdf";
            MemoryStream pdf = new MemoryStream(bytes);
            Attachment data = new Attachment(pdf, fileName);
            MailMessage mm = new MailMessage();
            mm.From = new MailAddress("abc@gmail.com");//put your mail address 
                                                            //from which you want to send the details
            string ToEmail = "";//put the email ids to whom you want to trigger the emails
            string[] Multi = ToEmail.Split(','); //logic to split the multiple to email ids
            foreach (string Multiemailid in Multi)
            {
                mm.To.Add(new MailAddress(Multiemailid));
            }
            mm.Subject = "Order To Distributor";
            StringBuilder bdy = new StringBuilder();
            bdy.Append("<table width='100%' 
            cellspacing='0' cellpadding='2'>");
            bdy.Append("<tr><td align='Left' 
            colspan = '2'><b>Dear,</b></td></tr>");
            bdy.Append("<tr><td colspan = '2'></td></tr>");
            bdy.Append("<tr><td><b>Your Order(< SO Number >) 
            has been successfully created, for your Order details please see attached file.</b>");
            bdy.Append("<tr><td><b>Customer: < Customer Code >, 
            < Customer Name1 ></b>");
            bdy.Append("</br>");
            bdy.Append("</br>");
            bdy.Append("</br>");
            
            bdy.Append("</table>");

            StringReader bd = new StringReader(bdy.ToString());
            mm.Body = bdy.ToString();
            string date_atchmnt = System.DateTime.Now.ToString();
            // int st = Convert.ToInt32(imgString);
            mm.Attachments.Add(data);
            mm.IsBodyHtml = true;
            SmtpClient smtp = new SmtpClient();


            // Include credentials if the server requires them.
            //smtp.Credentials = CredentialCache.DefaultNetworkCredentials;
            //smtpClient.Credentials = System.Net.CredentialCache.DefaultCredentials;
            //Console.WriteLine("Sending an e-mail message to {0} 
            //by using the SMTP host {1}.",  to.Address, client.Host);
            smtp.Host = "smtp.gmail.com";//putting the host details 
                                         //in my case I used the gmail SMTP
            smtp.EnableSsl = true;
            NetworkCredential NetworkCred = new NetworkCredential();
            NetworkCred.UserName = "";//pass your email id 
                                      //from which you want to trigger the email
            NetworkCred.Password = "";// pass your password for the profile
            smtp.UseDefaultCredentials = true;
            smtp.Credentials = NetworkCred;
            smtp.Port = 587;//port has to be configured in gmail smtp it will be same as written
            smtp.Send(mm);        
        }
        //catch (Exception ex)
        //{
        //    ex.Message.ToString();
        //}
    }
}

Points of Interest

Few points which need to be considered are as below:

1. For Gmail SMTP configuration - When you try to send the email from your id, go to your email settings and allow less secure APPS. If you won't allow it, code will not work and 5.5.1 authentication error will occur every time you try to trigger the email using the gmail SMTP server.
2. If you are having your SMTP server, you don't need to pass the whole configuration settings and allow the network configuration as default mentioned above in the code.

History

• 18-09-2016: Still making modification to code will update accordingly