Saving Attachments from mail item to Database

Question

I need to read the mail contents like To, CC, Subject, Body, Attachments of a mail and store it in Database tables. I am using the below code.

  EmailMessage msg = (EmailMessage)item;
  foreach (var col in msg.CcRecipients)
   {
   ccList += col.Address.ToString() + ";";
   }

   foreach (var col in msg.ToRecipients)
   {
   toList += col.Address.ToString() + ";";
   }

Similarly I can read Body and Subject. Problem is in reading Attachments How to read the Attachments and store it in Database.

I can use msg.Attachments. But What would be the type of the object. Please suggest some better solution to store the attachments to Database.

Answer 1

You would probably need a table with at least two columns. A varbinary to store the actual file (byte[]), and another column of varchar (string) to store the mime type.

Answer 2

Relate a EmailMessage to zero or many Attachments. Use varbinary(MAX) as the SQL column type. Or FILESTREAM. References below:

1. varbinary(max) tames blob (with code)
2. tsql binary & varbinary (msdn)
3. Compare Options for Storing Blobs (SQL Server) (msdn)
4. How To Update A BLOB In SQL SERVER Using TSQL (StackOverflow question)
5. Blob data in huge SQL Server database (StackOverflow question)
6. An Introduction to SQL Server FileStream (good tutorial)
7. Download sqlserver varbinary (StackOverflow question)