使用 SSRS 和 ASP.NET 从 SQL 数据库保存和检索 PDF

引言

我开始构建一个应用程序，用于从数据库检索数据，并将其放入一种 PDF 格式中，并将其作为邮件触发。 对于此需求，我使用了 SSRS 报表 SQL Server 和 ASP.NET。

背景

在创建解决方案之前，我们需要考虑更改的频率，它可能会频繁更改或保持不变。 为此，我们需要考虑一种依赖性较小的解决方案。 因此，为了满足将数据作为附件 (PDF) 连同某些内容通过邮件发送的需求，我们尝试使用 SSRS 报表来生成附件。 为了维护所有邮件的备份，我们将 PDF/附件存储在 SQL 数据库中，然后触发邮件。

Using the Code

要开始编码，我们将首先创建一个 SSRS 报表。

让我们考虑一个名为 ABC 的 SSRS 报表。

创建 SSRS 报表后，让我们在数据库中创建一个表。

列名	数据类型
id	nchar(10)
名称	nchar(30)
email	varbinary(MAX)

在数据库中创建表后，创建一个网页并将两个控件放在页面上。

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>

之后，编写如下所示的 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();

            //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();
        //}
    }
}

关注点

需要考虑的几个要点如下

1. 对于 Gmail SMTP 配置 - 当您尝试从您的 ID 发送电子邮件时，请转到您的电子邮件设置并允许安全性较低的应用。 如果您不允许，代码将无法工作，并且每次尝试使用 gmail SMTP 服务器触发电子邮件时都会发生 5.5.1 身份验证错误。
2. 如果您有自己的 SMTP 服务器，则无需传递整个配置设置，并且允许默认情况下上述代码中提到的网络配置。

历史

• 2016 年 9 月 18 日：仍在修改代码，将会相应更新