Last active
July 29, 2016 15:43
-
-
Save joshyates1980/ef19f6c949bff227b6464f7eaf4ca7b9 to your computer and use it in GitHub Desktop.
Fill dataset with VB.Net and Oracle database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Imports System.Data | |
Imports System.IO | |
Imports System.Threading | |
Imports System.Net | |
Imports System.Net.Mail | |
Imports Project.EmailService | |
Imports Oracle.DataAccess.Client | |
Partial Class Email | |
Public Const Page_Name As String = "email.aspx" | |
Dim dsEmail As New DataSet | |
Dim allEmails As String = "" | |
Dim emailService As New EmailService | |
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load | |
Dim connectStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString | |
dsEmail = emailService.GetEmailsByPageName(Page_Name) | |
For Each table As DataTable In dsEmail.Tables | |
For Each dr As DataRow In table.Rows | |
Dim rdrEmail = dr("email").ToString() + "," | |
allEmails = allEmails + rdrEmail | |
Next | |
allEmails = allEmails.Substring(0, allEmails.Length - 1) | |
Next | |
Dim Mail As New MailMessage | |
Dim smtp As New SmtpClient | |
If Application("mailcounter") = 0 Then | |
With Mail | |
.From = New MailAddress("Test@Email.com") | |
.To.Add(allEmails) | |
.IsBodyHtml = True | |
.Subject = "Test Email Subject" | |
.Body = "Test Email Body" | |
End With | |
'send the message | |
smtp = New SmtpClient("smtp.mail") | |
With smtp | |
.DeliveryMethod = SmtpDeliveryMethod.Network | |
.Port = 25 | |
.UseDefaultCredentials = True | |
Try | |
.Send(Mail) | |
Application("mailcounter") = 1 | |
Catch exp As Exception | |
Dim str As String | |
str = exp.Message | |
End Try | |
End With | |
End If | |
End Sub |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Imports Microsoft.VisualBasic | |
Imports Oracle.DataAccess | |
Imports System.Data | |
Imports Oracle.DataAccess.Client | |
Public Class EmailRepository | |
Function GetEmailsByPageName(ByVal pageName As String) As DataSet | |
Dim cn As New OracleConnection | |
Dim cmd As New OracleCommand | |
cn = New OracleConnection | |
cn.ConnectionString = (ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) | |
cmd = New OracleCommand("ORACLEDBA.PACKAGE_EMAIL.SP_EMAIL_LISTING_BY_NAME") | |
cmd.CommandType = Data.CommandType.StoredProcedure | |
cmd.Connection = cn | |
cmd.BindByName = True | |
Dim paramCursor As OracleParameter = New OracleParameter("email_list_cursor", OracleDbType.RefCursor) | |
With cmd.Parameters | |
.Add(New OracleParameter("a_page_name", OracleDbType.Varchar2)).Value = pageName | |
.Add("a_err_code", OracleDbType.Int32, Data.ParameterDirection.Output) | |
.Add("a_err_msg", OracleDbType.Varchar2, 300).Direction = Data.ParameterDirection.Output | |
.Add(paramCursor).Direction = Data.ParameterDirection.Output | |
End With | |
Dim da As New OracleDataAdapter(cmd) | |
Dim dsEmail As DataSet = New DataSet | |
Try | |
da.SelectCommand = cmd | |
da.Fill(dsEmail) | |
Return dsEmail | |
Catch ex As Exception | |
Throw | |
Finally | |
da.Dispose() | |
cmd.Dispose() | |
cn.Dispose() | |
If cn.State = ConnectionState.Open Then | |
cn.Close() | |
End If | |
End Try | |
End Function | |
End Class |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Imports Microsoft.VisualBasic | |
Imports System.Data | |
Imports Project.EmailRepository | |
Public Class EmailService | |
Dim emailRepository As New EmailRepository | |
Function GetEmailsByPageName(ByVal pageName As String) As DataSet | |
GetEmailsByPageName = emailRepository.GetEmailsByPageName(pageName) | |
End Function | |
End Class |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment