Storing Email Content in Database

[Arulmanoj]

Hi,

Am using asp.net with C# (VS 2008) to send mails with attachments using SMTP server and MailMessage class.

Now i want to store this email message with attachment into SQL 2008 DB and later a windows service will send mails by gettnig the mail content from database.

Could anyone provide information on storing emails with attachment into DB.

Thanks.

[invoked]

Hello Arulmanoj,

When we decided to use our DB for storing emails and attachments, we simply created a few tables for each purpose. One table exists for metadata such as email status, date received, customerID, etc. We then used the ID from this table to associate to the other necessary tables such as EmailMessage, EmailAddresses, EmailTemplate, and EmailFile. You may be thinking that this level of normalization is overkill but believe me when I say that I have yet to find a scenario where you can over-normalize although I'm sure someone will lend a hand in explaining one.

Back to the question. The EmailFile table is a simple table with an EmailID and a FileID and this allows user to attach multiple attachments to an email as a one-to-many relationship. On the other end of that FileID we have created a seperate DB for files(not required) and are using a table called File which has the following fields: FileID, MimeTypeID, FileName, FileDescription , UploadDate, UploadBy, and DeletedDate. Just as a reference, the MimeTypeID is linked to a MimeType DB table.

So that's almost it, the only other table is our FileBinary table that simply has a FileBinaryID as a PK, FileID as a FK to the file record, and lastly the FileBinary which is of type varbinary(max). We simply use the FileUpload control to capture the file binary. I also suggest having an allowed file types table and checking each file against them to ensure security when uploading files.

If there is something that I didn't cover or something you would like more clarification on, please ask away. I tried to do my best explaining this at a high level but I had way more to say.

Good luck!