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.
Column name | Datatype |
id | nchar(10) |
name | nchar(30) |
email | varbinary(MAX) |
After creating the table in the database, create one web page and put two controls onto the page.
ScriptManager
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();
ReportViewer1.ProcessingMode =
Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportServerUrl =
new Uri("http://bdc7-l-6j194y1/ReportServer_MSSQLSERVER1");
ReportViewer1.EnableViewState = true;
ReportViewer1.ServerReport.ReportPath = "/aus/order_report";
string format = "PDF",
devInfo = @"<DeviceInfo><Toolbar>True</Toolbar></DeviceInfo>";
string mimeType = "",encoding = "",
fileNameExtn = "";string[] stearms = null;
Microsoft.Reporting.WebForms.Warning[] warnings = null;
byte[] result = null;
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("@email", result);
cmd.ExecuteNonQuery();
con.Close();
con.Open();
SqlCommand cmd1 = new SqlCommand("SELECT top 1 email from email where id = 3",con);
SqlDataReader sdr = cmd1.ExecuteReader();
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"); string ToEmail = ""; string[] Multi = ToEmail.Split(','); 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();
mm.Attachments.Add(data);
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.EnableSsl = true;
NetworkCredential NetworkCred = new NetworkCredential();
NetworkCred.UserName = "";
NetworkCred.Password = "";
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 587;
smtp.Send(mm);
}
}
}
Points of Interest
Few points which need to be considered are as below:
- 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.
- 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