How to save MailMessage with multiple attachment into Sql Server database

Question

I have a requirement where i need to save complete mailmessage object into database. Using below code i am able to save message body and single attachment into database but i can have n number of attachment in my email so how to handle such situation.

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

Can we have any solution where we can serilize complete mailmessage along with their attachment and then deserlize it again and retrive all information

Sure, with a [lot of work](https://stackoverflow.com/q/1122058/41379160). You're probably better off setting up a [file drop](https://stackoverflow.com/q/1264672/41379160) rather than (ab)using a database for this, but if you must you can co-opt a `FILESTREAM` to do your bidding. — Jeroen Mostert, Nov 14 '17 at 13:50
Sorry Kixoka but i was just looking for advice not for complete code. Because I was not having any clue to achieve it. — manish kumar, Nov 14 '17 at 13:52

score 0 · Answer 1 · answered Nov 14 '17 at 14:39

As a rule of thumb, storing filestreams/files in your db, is considered a bad design choice. Mostly, you would prefer to store the file on a file server, and then just save the URL to the file in your Db.

However, if you must, the attachments should be in a separate table, where the body is the parent table. (IE. there should be a foreign key to the body's primary key, on the attachment table)

This will allow you to link x amounts of attachments to a single row of email body on your DB.

Then you insert the body first, and then all the attachments after in a for loop, with the id of the body.

Once that works, optimize from there.

How to save MailMessage with multiple attachment into Sql Server database

1 Answers1