如何用WebForms和C#将iTextSharp生成的PDF附件保存到SQL数据库

我正尝试将网页中用户表单生成的PDF文件保存至SQL数据库。目前生成PDF并通过邮件发送的功能运行正常，但保存到数据库时遇到了问题：生成的PDF能正常作为邮件附件发送，但存入数据库的附件并非实际有效的PDF文件。

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

发送邮件的代码 #

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;
}

尝试保存到数据库的代码 #

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()); // 冗余调用ToArray()
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

问题根源 #

1. MemoryStream作用域错误：生成PDF的memoryStream被包裹在using块中，离开该块时流会被释放，后续获取的bytes数据可能不完整。
2. 冗余的ToArray()调用：bytes本身已是byte[]类型，重复调用无意义。
3. 数据库字段类型不匹配：需确保Attachment字段为VARBINARY(MAX)（SQL Server存储二进制文件的标准类型）。
4. PDF内容遗漏：原代码未将核心段落par1添加到PDF，导致生成的PDF本身内容缺失。

修正后的完整代码 #

// 1. 生成PDF并获取完整二进制数据
byte[] pdfBytes = null;
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
using (MemoryStream memoryStream = new MemoryStream())
{
    PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
    pdfDoc.Open();

    // 添加PDF头部图片
    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;
    pdfDoc.Add(jpg);

    // 添加核心段落
    Font FONT = new Font(Font.TIMES_ROMAN, 12, Font.BOLD);
    Paragraph par1 = new Paragraph(lblClientHeaderNew.Text + " New User Request - " + txtNewFirstName.Text + " " + txtNewLastName.Text, FONT);
    par1.SpacingAfter = 4f;
    par1.SpacingBefore = 5f;
    par1.Alignment = Element.ALIGN_CENTER;
    pdfDoc.Add(par1);

    // 省略中间内容...

    // 添加PDF底部图片
    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(jpgfooter);

    pdfDoc.Close();
    // 在using块内获取完整二进制数据，此时流未被释放
    pdfBytes = memoryStream.ToArray();
}

if (pdfBytes != null)
{
    // 2. 发送带PDF附件的邮件
    string companyName = lblClientHeaderNew.Text;
    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}<br><br>" +
        $"User:    {txtNewFirstName.Text} {txtNewLastName.Text}<br>" +
        $"Thanks";
    Attachment pdfAttachment = new Attachment(new MemoryStream(pdfBytes), $"New User Request - {companyName} - {txtNewFirstName.Text} {txtNewLastName.Text}.pdf");
    message.Subject = $"New User Request For {companyName}";
    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 Successfully!", Toastr.Type.Success);
    }
    catch (Exception ex)
    {
        throw ex;
    }

    // 3. 将PDF保存到数据库
    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, con))
        {
            // 明确指定参数类型，避免隐式转换问题
            cmd.Parameters.Add("@ClientName", SqlDbType.NVarChar).Value = clientName;
            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = userName;
            cmd.Parameters.Add("@Attachment", SqlDbType.VarBinary, -1).Value = pdfBytes;
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

注意事项 #

• 确认数据库表的Attachment字段类型为VARBINARY(MAX)，避免使用已过时的IMAGE类型。
• 必须在MemoryStream的using块内调用ToArray()获取二进制数据，否则流被释放后数据会失效。
• 替换AddWithValue为明确指定SqlDbType的方式，避免参数类型不匹配导致的异常。

内容的提问来源于stack exchange，提问作者Enthusiast