ASPNet EMail attachment from SQL BLOB

Question

We are storing scheduled email information in SQL Server 2005. Included in the email information are 0 - 3 attachments of varying filetype. I have a VB.net Windows service that runs 'x' number of minutes, populates a dataset with the email info from SQL Server, builds the emails, and sends them via ASPNet EMail.

Trying to build and add an attachment from a BLOB causes the email send method to time out, even if the attachment is just a 15 byte text file. Hardcoding the path directly to the file works fine. I've tried several methods and can't get it right.

Answer 1

Hmm, I've done a similar thing. For each of my attachments I store the data as a binary column, and a separate column containing the size of the file in bytes. To obtained from the database I wrote something like (usingSqlCommand and SqlDataReader classes):

attachment = new byte[size];

sqlReader.GetBytes(sqlReader.GetOrdinal("attachment"), 0, attachment, 0, size);

System.IO.MemoryStream s = new System.IO.MemoryStream(attachment, true);
s.Write(attachment, 0, attachment.Length);

And too attached to a MailMessage class (mm for short);

System.IO.MemoryStream s = new System.IO.MemoryStream(attachment, false);

Attachment attached = new Attachment(s, "some file name");
mm.Attachments.Add(attached);

Maybe these extracts will help! I've pulled the code from two different custom classes, so it's a little clunky with the MemoryStream object in each extract.