SendMail values from VB.NET into database table 1

[xcaliber2222]

Hello, I have a global utility class which does the following:

Public Overloads Shared Function SendEmail(ByVal to_emails As String, ByVal from_email As String, ByVal message As String, ByVal subject As String, ByVal cc_emails As String, ByVal bcc_emails As String, ByVal priority As Priority, ByVal is_html As Boolean, ByVal email_host As String, ByVal attachment As ArrayList) As Boolean

        Dim strDebug As String
        Dim strArrToAddr As String()
        Dim strArrCCAddr As String()
        Dim strArrBCCAddr As String()

        Dim i As Integer
        Dim j As Integer
        Dim bValid As Boolean = True
        Dim bInValid As Boolean = False
        ' Set Mail Message
        Dim MailMsg As MailMessage = New MailMessage()
        ' Check To Email
        If to_emails = "" Then
            Return bInValid
        End If
        ' Check From Email
        If from_email = "" Then
            Return bInValid
        End If
        ' Check Email Host
        If email_host = "" Then
            Return bInValid
        End If
        MailMsg.From = New MailAddress(from_email.Trim())
        ' Split the Email
        strArrToAddr = to_emails.Replace(",", ";").Split(";")
        ' Set To Email List
        For i = 0 To strArrToAddr.Length - 1
            If strArrToAddr(i) <> "" Then
                MailMsg.To.Add(New MailAddress(strArrToAddr(i).Trim()))
            End If
        Next
 strArrCCAddr = cc_emails.Replace(",", ";").Split(";")
        ' Set CC Email List
        If Not cc_emails Is Nothing Then
            If cc_emails <> "" Then
                For j = 0 To strArrCCAddr.Length - 1
                    If strArrCCAddr(j) <> "" Then
                        MailMsg.CC.Add(New MailAddress(strArrCCAddr(j).Trim()))
                    End If
                Next
            End If
        End If

        strArrBCCAddr = bcc_emails.Replace(",", ";").Split(";")
        ' Set BCC Email List
        If Not bcc_emails Is Nothing Then
            If bcc_emails <> "" Then
                For j = 0 To strArrBCCAddr.Length - 1
                    If strArrBCCAddr(j) <> "" Then
                        MailMsg.Bcc.Add(New MailAddress(strArrBCCAddr(j).Trim()))
                    End If
                Next
            End If
        End If

        MailMsg.Subject = subject
        MailMsg.Body = message
        MailMsg.IsBodyHtml = is_html
        ' Set Email priority to High or Normal
        Select Case priority
            Case priority.High
                MailMsg.Priority = MailPriority.High
            Case priority.Normal
                MailMsg.Priority = MailPriority.Normal
        End Select

        If Not attachment Is Nothing Then
            For Each strFilePath As String In attachment
                If File.Exists(strFilePath) Then
                    Dim att As New Net.Mail.Attachment(strFilePath)
                    MailMsg.Attachments.Add(att)
                End If
            Next
        End If

        Dim SmtpMail As New Net.Mail.SmtpClient()
        ' Set Email Host
        SmtpMail.Host = email_host
        ' Check the Email, it will return error message if it fail 
        Try
            SmtpMail.Send(MailMsg)
            strDebug = "Email Sent"

        Catch ex As Exception
            strDebug = "Email Sending Error. " & ex.Message
            Return bInValid

        End Try

        Return bValid
    End Function

Can someone please show me how I would fire an inline SQL statement (or stored proc) and grab these values (like and write(insert) them into a SQL Server log table. Also, can you please show me how to pass in the "application" (project name) and "class" (class name) in also with the email values like "subject" and "message"? I have a separate email_info class which I will pass in as an object to the "email_info" parm.

(
application
class
subject
message
email_info
)

Any help would be greatly appreciated, hope this makes sense.

Thank you,
Alejandro

 

[jebenson]

Well, here's a quick "primer" to get you started.

Din conn As SQLConnection

Conn = New SQLConnection("Your connection string here")

Dim cmd As SQLCommand

Dim SQLStr As String

SQLStr = "Insert Into <your table name> (application, class, subject, message, email_info) VALUES (@application, @class, @subject, @message, @email_info)"

cmd = New SQLCommand(SQLStr, conn)

'note: use appropriate values for SqlDbType
cmd.Parameters.Add("@application", SqlDbType.VarChar)
cmd.Parameters.Add("@class", SqlDbType.VarChar)
cmd.Parameters.Add("@subject", SqlDbType.VarChar)
cmd.Parameters.Add("@message", SqlDbType.VarChar)
cmd.Parameters.Add("@email_info", SqlDbType.Binary)

'note: use appropriate values
cmd.Parameters("@application").Value = "Project Name"
cmd.Parameters("@class").Value = "Class"
cmd.Parameters("@subject").Value = "Subject"
cmd.Parameters("@message").Value = "Message"
cmd.Parameters("@email_info").Value = SerializedObject

'!!NOTE: to save your email_info class you will need to serialize the object, and use that for SerializedObject

'check this link for an example of object serialization:

http://www.devcity.net/Articles/113/1/dotnet_serialization.aspx

'Finally, save the data

Dim Trans As SQLTransaction

Try
Trans = conn.BeginTransaction
cmd.Transaction = Trans
cmd.ExecuteNonQuery()
Trans.Commit()
Catch ex As Exception
If Not Trans Is Nothing Then
Trans.RollBack()
End If
End Try


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[xcaliber2222]

Thank you!