Imports Microsoft.VisualBasic
Imports System.Configuration.ConfigurationManager
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.Generic
Public Class DataTracDB
Private Shared connectionstring As String
Shared Sub New()
connectionstring = ConfigurationManager.ConnectionStrings("DMDConnectionString").ConnectionString
End Sub
'returns a dataset full dataset with no parameters
Public Shared Function ExecuteDataSet(ByVal sql As String) As Data.DataSet
Dim ds As New Data.DataSet
Dim da As New SqlDataAdapter(sql, connectionstring)
da.Fill(ds)
Return ds
End Function
Public Shared Function testRow(ByVal column As String, ByVal row As DataRow) As String
If row(column) Is DBNull.Value Then
Return ""
Else
Return row(column)
End If
End Function
End Class
Public Class Broker
Inherits DataTracDB
Private strBranchID As String
Private strBrokerID As String
Public Property brokerID() As String
Get
Return strBrokerID
End Get
Set(ByVal value As String)
strBrokerID = value
End Set
End Property
Public Property branchID() As String
Get
Return strBranchID
End Get
Set(ByVal value As String)
strBranchID = value
End Set
End Property
Public Function SelectBroker() As List(Of Broker)
Dim ds As DataSet = ExecuteDataSet("SELECT brokers_id, idnum, address, city, state, zip, phone_pre, phone, fax_pre, fax, whole_rep, president FROM brokers")
Dim arr As New List(Of Broker)
For Each row As DataRow In ds.Tables(0).Rows
Dim c As New Broker
c.brokerID = testRow("brokers_id", row)
c.branchID = testRow("idnum", row)
arr.Add(c)
Next
Return arr
End Function
End Class
Public Class Loan
Inherits Broker
Private strLoanID As String
Private strloanAmt As String
Private strLien As String
Private strBrokerID As String
Private strFundedDate As String
Public Property LoanID() As String
Get
Return strLoanID
End Get
Set(ByVal value As String)
strLoanID = value
End Set
End Property
Public Property LoanAmount() As String
Get
Return strloanAmt
End Get
Set(ByVal value As String)
strloanAmt = value
End Set
End Property
Public Property lien() As String
Get
Return strLien
End Get
Set(ByVal value As String)
strLien = value
End Set
End Property
Public Property fundedDate() As String
Get
Return strFundedDate
End Get
Set(ByVal value As String)
strFundedDate = value
End Set
End Property
Public Function SelectLoan() As List(Of Loan)
Dim ds As DataSet = ExecuteDataSet("SELECT dbo.gen.file_id, dbo.gen.loan_num, dbo.gen.loan_amt, dbo.fun.funded, dbo.gen.lien, dbo.gen.brokers_id FROM dbo.gen INNER JOIN dbo.fun ON dbo.gen.file_id = dbo.fun.file_id")
Dim arr As New List(Of Loan)
For Each row As DataRow In ds.Tables(0).Rows
Dim c As New Loan
Dim branchBroker As New Broker
For Each broker As Broker In branchBroker.SelectBroker
If testRow("brokers_id", row) = broker.brokerID Then
c.LoanID = DataTracDB.testRow("file_id", row)
c.lien = testRow("lien", row)
c.LoanAmount = testRow("loan_amt", row)
c.brokerID = broker.branchID
c.fundedDate = testRow("funded", row)
arr.Add(c)
End If
Next
Next
Return arr
End Function
End Class