How to save System.Net.Mail MailMessage object into SQL Server database?

Question

I have a requirement to save MailMessage objects (system.net.mail) to a table in a SQL Server 2005 database, with .Net 2.

The mail object also contains a single image attachment which is part of the mail message body as a logo on the top of the message.

This works and creates my MailMessage object and it's able to be sent no worries.

   private bool SendEMAIL(string wsStudent, string wsNOKemails, string wsMSG)
    {
        try
        {
                MailMessage message = new MailMessage();
                message.To.Clear();
                string[] wsEmails = wsNOKemails.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                foreach (string address in wsEmails)
                {
                    MailAddress to = new MailAddress(address);
                    message.To.Add(to);
                }

                //get staff email as the FROM address
                DataSourceSelectArguments dss = new DataSourceSelectArguments();
                sdsStaffDetails.SelectParameters["StaffID"].DefaultValue = Session["StaffID"].ToString();
                DataView dv = sdsStaffDetails.Select(dss) as DataView;
                DataTable dt = dv.ToTable() as DataTable;
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows) //this loop will only happen once
                    {
                        message.From = new MailAddress(dr.ItemArray[2].ToString());    //TET's staffer email address
                        message.Subject = "Tec-NQ - TET Report for " + wsStudent;
                        message.Priority = MailPriority.High;
                        message.IsBodyHtml = true;

                        //add the logo attachment
                        MemoryStream ms = new MemoryStream(File.ReadAllBytes(Server.MapPath("../images/Tec-NQ-Logo-BlueOrange-60x174.png")));
                        Attachment logo = new Attachment(ms, "logo");
                        message.Attachments.Add(logo);

                        //..but create a Base64 version of it, as we'll need to embedd this into the message so it knows
                        //where to place the logo into the email
                        string contentID = "Image";
                        logo.ContentId = contentID;
                        logo.ContentDisposition.Inline = true;
                        logo.ContentDisposition.DispositionType = DispositionTypeNames.Inline;

                        //replace the <p> contents of the logo with a new HTML string header to contain the cid reference for the logo
                        string wsHdr = "<html><body><p><a href=\"http://www.tecnq.com.au\" target=\"_blank\"><img alt=\"tecnqlogo\" src=\"cid:" + contentID + "\" style=\"border-style:none; height:34px; width:100px\" /></a></p>";

                        //then place back the rest of the email
                        string wsBody = wsHdr + " " + wsMSG.Substring(wsMSG.IndexOf("<hr />")) + "</html></body>";
                        message.Body = wsBody;

                        //send it!
                        SmtpClient SmtpClient = new SmtpClient();
                        SmtpClient.Send(message);
                    }
                    return true;
            }
            else
                return false;
        }
        catch (Exception msg)
        {
            StatusMessage(msg.ToString());
            return false;
        }
    }

I have read this and this, but all of these assume you're wanting to save the MailMessage to a file on disk. That is not the case for me.

I've also read this, which implies I should serialise the message. But I think the MailMessage is a core object that cannot be serialised, according to Microsoft. Is that correct?

So any ideas on how I can save the above as a BLOB or some data type, so all I have to do later on, is open the table, read the object and SEND it like a normal email?

UPDATE

This CodeProject article is a way forward, that I just found. However, it's to specify a filename when saving the MailMessage object to disk. That's a start because I can then specifiy some field delimiters in the filename to specify what files to use later on, in terms of reading the EML file and later emailing the object.

Instead of saving the entire `MailMessage` as single BLOB or other,is saving it's properties an option that can later be reconstructed? — Searching, Nov 16 '16 at 03:57
@Searching yes I can add it all later. Minimum is to save the attachment and the body of the message — Fandango68, Nov 16 '16 at 03:59
Sweet..In that case for Attachment, you need to convert to `byte[]` first and save as VARBINARY(MAX) `MyTable.Attachment`, and I guess other details are up to you. I could post an answer.. — Searching, Nov 16 '16 at 04:06
@Searching a working example of the email message and body in a way that I can save it into a SQL Server table would be ideal. Thank you — Fandango68, Nov 16 '16 at 04:09
Just got back on... Posted a sample ..If you are still interested. :) — Searching, Nov 16 '16 at 21:17

score 4 · Accepted Answer · edited May 23 '17 at 12:33

I think the update you posted is pretty good. Also check out this answer for How to save MailMessage object to disk as *.eml or *.msg file. You probably can create multiple clients, one to properly send and other to save the message to local file, which then needs to be saved to DB. I haven't tried this though.

If you are still looking into this, posted sample code...

Restricted to single attachment only. The sample is only to get you started and should not be taken as is. There is a lot of room for improvements. We load the attachment into a byte[] and then add it as a Attachment(Stream s). We save this into the db as our VARBINARY content.

Your First send method, that also saves the message to db

try
        {
            MailMessage message = new MailMessage();
            var logoPath = @"C:\MyLogo.jpg";
            message.From = new MailAddress("from@email.co.nz");
            message.To.Add("to@email.co.nz");
            message.IsBodyHtml = true;

            //Read the attachment into byte Array. This assumes single attachment only in the Mail Message.
            byte[] arr = File.ReadAllBytes(logoPath);

            using (var stream = new MemoryStream(arr))
            {
                stream.Position = 0;
                Attachment logo = new Attachment(stream, "Logo");
                string contentID = "Image";
                logo.ContentId = contentID;
                logo.ContentDisposition.Inline = true;
                logo.ContentDisposition.DispositionType = DispositionTypeNames.Inline;

                string wsHdr = "<html><body><p><a href=\"http://www.tecnq.com.au\" target=\"_blank\"><img alt=\"tecnqlogo\" src=\"cid:" + contentID + "\" style=\"border-style:none; height:34px; width:100px\" /></a></p>" + "</html></body>"; ;

                message.Body = wsHdr;
                message.Attachments.Add(logo);


                SmtpClient smtp = new SmtpClient();
                smtp.Host = "MyServer";
                smtp.Port = 25;
                smtp.Send(message);
                SaveMessage(message, arr);
            }             
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

To Save the mail message

  //ZERO ERRO CHECKING.
    private static void SaveMessage(MailMessage message, byte[] arr)
    {
        using (var conn = new SqlConnection("CONNECTION_STRING"))
        {
            using (var cmd = new SqlCommand("INSERT INTO MailSent (Body,Attachment) VALUES(@BODY,@ATTACHMENT)", conn))
            {
                conn.Open();
                var param = new SqlParameter("@BODY", SqlDbType.VarChar)
                {
                    Value = message.Body
                };
                var param2 = new SqlParameter("@ATTACHMENT", SqlDbType.Binary)
                {
                    Value = arr
                };
                cmd.Parameters.Add(param);
                cmd.Parameters.Add(param2);
                cmd.ExecuteNonQuery();
            }
        }            
    }

Resend message

 try
        {
            MailMessage message = new MailMessage();
            byte[] arr2 = null;
            GetMailMessage(2, ref message, ref arr2);


            message.From = new MailAddress("from@email.co.nz");
            message.To.Add("to@email.co.nz");
            message.IsBodyHtml = true;

            using (var stream = new MemoryStream(arr2))
            {
                stream.Position = 0;
                Attachment logo = new Attachment(stream, "Logo");
                string contentID = "Image";
                logo.ContentId = contentID;
                logo.ContentDisposition.Inline = true;
                logo.ContentDisposition.DispositionType = DispositionTypeNames.Inline;
                message.Attachments.Add(logo);

                SmtpClient smtp = new SmtpClient();
                smtp.Host = "MyServer";
                smtp.Port = 25;
                smtp.Send(message);

            }

            // SaveMessage(message, arr);
        }
        catch (Exception ex)
        {

            Console.WriteLine(ex.ToString());
        }

To Retrieve the message

    //ZERO ERROR CHECKING.
    private static void GetMailMessage(int itemId, ref MailMessage msg2, ref byte[] arr2)
    {
        using (var conn = new SqlConnection("CONNECTION_STRING"))
        {
            using (var cmd = new SqlCommand("SELECT [Body],[Attachment] FROM MailSent WHERE ID = @itemId", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@itemId", itemId);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        msg2.Body = dr[0].ToString();
                        arr2 = dr[1] as byte[];
                    }
                }
            }
        }
    }

How to save System.Net.Mail MailMessage object into SQL Server database?

1 Answers1