在开发自动化报告发送系统时,经常需要考虑报告的生成频率以及如何高效地处理数据。为了实现将数据以PDF格式作为附件通过邮件发送,并且保留邮件备份,可以使用SQL Server Reporting Services (SSRS) 来生成PDF文件,并将PDF文件存储在SQL数据库中。本文将详细介绍如何实现这一功能。
在创建解决方案之前,需要考虑数据变更的频率,这可能经常变化或者保持不变。因此,需要设计一个解决方案,使其尽可能地减少相互依赖。对于将数据以PDF格式通过邮件发送的需求,尝试使用SSRS报告来生成附件。为了维护所有邮件的备份,将PDF/附件存储在SQL数据库中,然后触发邮件发送。
要开始编码,首先需要创建一个SSRS报告。
假设创建了一个名为ABC的SSRS报告。
创建报告后,需要在数据库中创建一个表格。
以下是表格的列名和数据类型:
id nchar(10)
name nchar(30)
email varbinary(MAX)
在数据库中创建表格后,需要创建一个网页,并在页面上放置两个控件。
<asp:ScriptManager id="ScriptManager1" runat="server" enablepagemethods="true"/>
<rsweb:ReportViewer ID="ReportViewer1" runat="server">
</rsweb:ReportViewer>
接下来,需要编写CS文件的代码。
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";
Microsoft.Reporting.WebForms.ReportParameter[] rptParams = new Microsoft.Reporting.WebForms.ReportParameter[3];
rptParams[0] = new Microsoft.Reporting.WebForms.ReportParameter("ID", "1729");
ReportViewer1.ServerReport.SetParameters(rptParams);
string format = "PDF", devInfo = "True ";
string mimeType = "", encoding = "", fileNameExtn = "";
string[] stream = null;
Microsoft.Reporting.WebForms.Warning[] warnings = null;
byte[] result = null;
try
{
result = ReportViewer1.ServerReport.Render(format, devInfo, out mimeType, out encoding, out fileNameExtn, out stream, 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();
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("");
bdy.Append("Dear, ");
bdy.Append("");
bdy.Append("Your Order(< SO Number >) has been successfully created, for your Order details please see attached file. ");
bdy.Append("Customer: < Customer Code >, < Customer Name1 > ");
bdy.Append("
");
StringReader bd = new StringReader(bdy.ToString());
mm.Body = bd.ToString();
string date_atchmnt = System.DateTime.Now.ToString();
mm.Attachments.Add(data);
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Credentials = CredentialCache.DefaultNetworkCredentials;
smtp.Host = "smtp.gmail.com";
NetworkCredential NetworkCred = new NetworkCredential();
NetworkCred.UserName = "";
NetworkCred.Password = "";
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 587;
smtp.Send(mm);
}
catch (Exception ex)
{
ex.Message.ToString();
}
}
}
}
以下是一些需要注意的要点:
对于Gmail SMTP配置 - 当尝试从ID发送邮件时,请转到电子邮件设置并允许不太安全的应用程序。如果不允许,代码将无法工作,每次尝试使用Gmail SMTP服务器触发邮件时都会发生5.5.1身份验证错误。