Jerry Miller
asked on
Asp.Net email attachment issues
I am using a stored procedure to pull in the correct data to create my email attachment. I found code on this site to use a stream for the data.
http://weblogs.asp.net/rrobbins/archive/2008/03/01/send-email-attachment-using-a-memory-stream.aspx
It seems to work except that I am not getting the entire dataset in the attachment. It is not a size issue as far as I can tell, since I have pulled in only the Top 50 for the stored procedure and it only loads 35 of them. When it is pulling in the entire dataset, there is currently 354 rows, I get 351, and only part of the last row. Something in my code is cutting off the last of the data. I need a fresh set of eyes to see what I have that is screwed up.
Here is the function to create the stream:
Public Function createCSVAttachment(ByVal spName As String) As Stream
Dim conn As SqlConnection = New SqlConnection(commonFuncti ons.getDSN )
Dim cmd = New SqlCommand()
Dim ad As SqlDataAdapter
Dim tempdata As DataTable = New DataTable
cmd.Connection = conn
cmd.CommandText = spName
cmd.CommandType = CommandType.StoredProcedur e
ad = New SqlDataAdapter(cmd)
ad.Fill(tempdata)
Dim stream As New MemoryStream()
Dim sw As New StreamWriter(stream)
Dim sbColumns As New StringBuilder()
Dim sbRows As New StringBuilder()
Dim col As DataColumn
Dim row As DataRow
'Format the csv file
For Each col In tempdata.Columns
sbColumns.Append(col.Colum nName).App end(",")
Next
sw.WriteLine(sbColumns.ToS tring().Tr imEnd(",") )
For Each row In tempdata.Rows
For Each col In tempdata.Columns
sbRows.Append(If(row(col.C olumnName) Is DBNull.Value, "", row(col.ColumnName).ToStri ng())).App end(",")
Next
sw.WriteLine(sbRows.ToStri ng().TrimE nd(","))
sbRows.Remove(0, sbRows.ToString().Length)
Next
stream.Position = 0
Return stream
sw.Flush()
sw.Close()
stream.Flush()
stream.Close()
stream.Dispose()
End function
And the one to send the email:
Public Sub SendEmail()
'General Message
Dim mailSubject As String = "Test File"
Dim message As String = String.Empty
Dim messageBuilder As New StringBuilder
Dim fileName As String = createFileName()
Dim path As String = HttpContext.Current.Reques t.Url.GetL eftPart(Ur iPartial.A uthority)
l_from = HttpContext.Current.Sessio n("adid") & "@test.com"
messageBuilder.Append("Tes t Body")
message = messageBuilder.ToString()
If getEnvironment() = "Localhost" Then
l_to = "test@test.com"
Else
l_to = Session.SessionManager.ADI D
End If
l_cc = "test@test.com"
'email
Dim Mail As New MailMessage()
Dim attachment As New EmailAttachments
Dim attachmentStream As System.IO.MemoryStream = attachment.createCSVAttach ment("stor edProcName ")
'Dim data As New Attachment(attachmentStrea m, fileName, "text/csv")
Mail.From = New MailAddress(l_from)
Mail.To.Add(l_to)
Mail.CC.Add(l_cc)
'Send failure back to sender
Mail.DeliveryNotificationO ptions = DeliveryNotificationOption s.OnFailur e
'set the content
Mail.Subject = mailSubject
Mail.IsBodyHtml = True
Mail.Body = message
'Mail.Attachments.Add(data )
Mail.Attachments.Add(New Attachment(attachmentStrea m, fileName, "text/csv"))
'send the message
Dim smtp As New SmtpClient(commonFunctions .getSMTPse rver())
smtp.Send(Mail)
insertNewSequenceNumber(_s equenceNum ber)
'release resources
'Mail.Dispose()
End Sub
http://weblogs.asp.net/rrobbins/archive/2008/03/01/send-email-attachment-using-a-memory-stream.aspx
It seems to work except that I am not getting the entire dataset in the attachment. It is not a size issue as far as I can tell, since I have pulled in only the Top 50 for the stored procedure and it only loads 35 of them. When it is pulling in the entire dataset, there is currently 354 rows, I get 351, and only part of the last row. Something in my code is cutting off the last of the data. I need a fresh set of eyes to see what I have that is screwed up.
Here is the function to create the stream:
Public Function createCSVAttachment(ByVal spName As String) As Stream
Dim conn As SqlConnection = New SqlConnection(commonFuncti
Dim cmd = New SqlCommand()
Dim ad As SqlDataAdapter
Dim tempdata As DataTable = New DataTable
cmd.Connection = conn
cmd.CommandText = spName
cmd.CommandType = CommandType.StoredProcedur
ad = New SqlDataAdapter(cmd)
ad.Fill(tempdata)
Dim stream As New MemoryStream()
Dim sw As New StreamWriter(stream)
Dim sbColumns As New StringBuilder()
Dim sbRows As New StringBuilder()
Dim col As DataColumn
Dim row As DataRow
'Format the csv file
For Each col In tempdata.Columns
sbColumns.Append(col.Colum
Next
sw.WriteLine(sbColumns.ToS
For Each row In tempdata.Rows
For Each col In tempdata.Columns
sbRows.Append(If(row(col.C
Next
sw.WriteLine(sbRows.ToStri
sbRows.Remove(0, sbRows.ToString().Length)
Next
stream.Position = 0
Return stream
sw.Flush()
sw.Close()
stream.Flush()
stream.Close()
stream.Dispose()
End function
And the one to send the email:
Public Sub SendEmail()
'General Message
Dim mailSubject As String = "Test File"
Dim message As String = String.Empty
Dim messageBuilder As New StringBuilder
Dim fileName As String = createFileName()
Dim path As String = HttpContext.Current.Reques
l_from = HttpContext.Current.Sessio
messageBuilder.Append("Tes
message = messageBuilder.ToString()
If getEnvironment() = "Localhost" Then
l_to = "test@test.com"
Else
l_to = Session.SessionManager.ADI
End If
l_cc = "test@test.com"
Dim Mail As New MailMessage()
Dim attachment As New EmailAttachments
Dim attachmentStream As System.IO.MemoryStream = attachment.createCSVAttach
'Dim data As New Attachment(attachmentStrea
Mail.From = New MailAddress(l_from)
Mail.To.Add(l_to)
Mail.CC.Add(l_cc)
'Send failure back to sender
Mail.DeliveryNotificationO
'set the content
Mail.Subject = mailSubject
Mail.IsBodyHtml = True
Mail.Body = message
'Mail.Attachments.Add(data
Mail.Attachments.Add(New Attachment(attachmentStrea
'send the message
Dim smtp As New SmtpClient(commonFunctions
smtp.Send(Mail)
insertNewSequenceNumber(_s
'release resources
'Mail.Dispose()
End Sub
I think you would be better off creating a temp csv file and then attaching that to the email.
ASKER
I have been swamped and not able to research this approach or try this yet. I will test in the next couple of days and get back with you.
ASKER
I still haven't had a chance to try this. The stream approach mostly works, but it is cutting off the last part of the data no matter how large (or small) the stream is. I would really like to figure out what is wrong with the above code to make it work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For anyone reading this thread, I finally got back to this project after a few months. I found the answer on this forum:
http://forums.asp.net/t/1451590.aspx/1
I only had to move sw.Flush() after all of the data is written, but before I set the stream.position = 0
Apparently flushing the streamwriter pushes the last of the data into the stream. I knew that I was getting all of the data from the database, but puzzled as to why it wasn't writing the entire datatable to the stream.
http://forums.asp.net/t/1451590.aspx/1
I only had to move sw.Flush() after all of the data is written, but before I set the stream.position = 0
Apparently flushing the streamwriter pushes the last of the data into the stream. I knew that I was getting all of the data from the database, but puzzled as to why it wasn't writing the entire datatable to the stream.