I have a custom class that consists of patient data. I am pulling data from three different sql tables and combining it into a list of patients. If the patient doesn't exist in the last table then I am inserting the patient into that table. From this last table, I am pulling a list of patients that are in the department for which I am looking. Once I get all the patients in this list, I display the list in a datagridview. All of this works, but I need to sort the final list by Room number so all the rooms are in order i.e. 401 A, 401 B, 402 A, 403 A, ect. I have an order by in my sql statements which works to an extent. When I run the program the first time, the patients that were already in the table are in order and the new ones are tagged on to the bottom. The next time, if no new patients are added, then it is in the correct sort order. I don't have a clue as to where to start on this. I have read about an IComparer, but do not know how such a thing would be implemented. I have posted my code to pull patients and my custom patient class. Any help, suggestions, tips, or advice is greatly appreciated!
'Declare variables
Dim DepartmentsNeeded As String() = DepartmentNumber.Split("|")
Dim PatientListADT As New List(Of Patient)
Dim PatientListCMSC As New List(Of Patient)
Dim PatientListFinal As New List(Of Patient)
'Open and read
Using con As New SqlConnection(My.Settings.CPSISQLConnectionString)
con.Open()
Dim cmd As New SqlCommand(ADTLOSString, con)
Dim reader1 As SqlDataReader
reader1 = cmd.ExecuteReader
'Populate tempPatient1 with Data from ADTLOS tables
While reader1.Read
Dim tempPatient1 As New Patient
With tempPatient1
.PatientNumber = NewCStr(reader1("PatNum"))
.Room = NewCStr(reader1("Room"))
.Department = NewCStr(reader1("Department"))
.FC = NewCStr(reader1("FinClass"))
.Type = NewCInt(reader1("Type"))
.PatientName = NewCStr(reader1("PatientName"))
.AttendingPhy = NewCStr(reader1("AttendingPhy"))
If NewCStr(reader1("AdmitDate")) <> "" Then
Dim value As String = reader1("AdmitDate")
.AdmitDate = NewCDate(value.Substring(0, 2) & "/" & value.Substring(2, 2) & "/" & value.Substring(4, 4))
End If
.AdmitTime = NewCStr(reader1("AdmitTime"))
.AdmitSource = NewCStr(reader1("AdmitSource"))
If NewCStr(reader1("DischargeDate")) <> "" Then
Dim value As String = reader1("DischargeDate")
If value.Count = 8 Then
.DischargeDate = NewCDate(value.Substring(0, 2) & "/" & value.Substring(2, 2) & "/" & value.Substring(4, 4))
End If
End If
.DischargeTime = NewCStr(reader1("DischargeTime"))
.CurrentDRG = NewCStr(reader1("DRG"))
.PLOS = NewCStr(reader1("PLOS"))
.ALOS = NewCStr(reader1("ALOS"))
End With
'Add Patient to the ADTLOS Patient List
PatientListADT.Add(tempPatient1)
End While
'Close Reader1
reader1.Close()
'Declare variables for Reader1
cmd = New SqlCommand(CMSCString, con)
reader1 = cmd.ExecuteReader
'Populate tempPatient2 with Data from CMSCORECARD table
While reader1.Read
Dim tempPatient2 As New Patient
With tempPatient2
.PatientNumber = NewCStr(reader1("PatNum"))
.Room = NewCStr(reader1("Room"))
.Department = NewCStr(reader1("Department"))
.FC = NewCStr(reader1("FinClass"))
.Type = NewCInt(reader1("Type"))
.PatientName = NewCStr(reader1("PatientName"))
.AttendingPhy = NewCStr(reader1("AttendingPhy"))
.AdmitDate = NewCDate(reader1("AdmitDate"))
.AdmitTime = NewCStr(reader1("AdmitTime"))
.AdmitSource = NewCStr(reader1("AdmitSource"))
.DischargeDate = NewCDate(reader1("DischargeDate"))
.DischargeTime = NewCStr(reader1("DischargeTime"))
.LastDRG = NewCStr(reader1("LastDRG"))
.CurrentDRG = NewCStr(reader1("CurrentDRG"))
.Level = NewCInt(reader1("Level"))
.PLOS = NewCStr(reader1("PLOS"))
.ALOS = NewCStr(reader1("ALOS"))
.SeverityOfIllness = NewCStr(reader1("SI"))
.IntensityOfService = NewCStr(reader1("IntensityOfService"))
.DCP = NewCStr(reader1("DCP"))
.DCDate = NewCDate(reader1("DCDate"))
.MD = NewCStr(reader1("MD"))
.PA = NewCStr(reader1("PA"))
.DCM = NewCStr(reader1("DCM"))
.PAD = NewCStr(reader1("PAD"))
.Comments = NewCStr(reader1("Comments"))
.DeniedDays = NewCInt(reader1("DD"))
.SaveDays = NewCInt(reader1("SD"))
.Completed = NewCStr(reader1("Completed"))
End With
'Add Patient to the CMSCORECARD Patient List
PatientListCMSC.Add(tempPatient2)
End While
'Close reader
reader1.Close()
'Combine two Lists/If patient isn't in list, then added to sql table
Dim PatientInList As Boolean
For Each person As Patient In PatientListADT
'Set PatientInList Default Value
PatientInList = False
'Find matching Patients
For i As Integer = 0 To PatientListCMSC.Count - 1
If Trim(person.PatientNumber) = Trim(PatientListCMSC(i).PatientNumber) Then
'Update Room in CMSCORECARD table
If PatientListCMSC(i).Room <> person.Room Then
PatientListCMSC(i).Room = person.Room
UpdateScorecardTable("[ROOM]", person.Room, person.PatientNumber)
End If
'Update Department in CMSCORECARD table
If PatientListCMSC(i).Department <> person.Department Then
PatientListCMSC(i).Department = person.Department
UpdateScorecardTable("[DEPARTMENT]", person.Department, person.PatientNumber)
End If
'Update FC in CMSCORECARD table
If PatientListCMSC(i).FC <> person.FC Then
PatientListCMSC(i).FC = person.FC
UpdateScorecardTable("[FC]", person.FC, person.PatientNumber)
End If
'Update Patient Type in CMSCORECARD table
If PatientListCMSC(i).Type <> person.Type Then
PatientListCMSC(i).Type = person.Type
UpdateScorecardTable("[Patient_Type]", person.Type, person.PatientNumber)
End If
'Update Patient Name in CMSCORECARD table
If PatientListCMSC(i).PatientName <> person.PatientName Then
PatientListCMSC(i).PatientName = person.PatientName
UpdateScorecardTable("[PATIENT_NAME]", person.PatientName, person.PatientNumber)
End If
'Update Attending Physician in CMSCORECARD table
If PatientListCMSC(i).AttendingPhy <> person.AttendingPhy Then
PatientListCMSC(i).AttendingPhy = person.AttendingPhy
UpdateScorecardTable("[PHYSICIAN]", person.AttendingPhy, person.PatientNumber)
End If
'Update Admit Date in CMSCORECARD table
If PatientListCMSC(i).AdmitDate <> person.AdmitDate Then
PatientListCMSC(i).AdmitDate = person.AdmitDate
UpdateScorecardTable("[ADMIT_DATE]", person.AdmitDate, person.PatientNumber)
End If
'Update Admit Time in CMSCORECARD table
If PatientListCMSC(i).AdmitTime <> person.AdmitTime Then
PatientListCMSC(i).AdmitTime = person.AdmitTime
UpdateScorecardTable("[ADMIT_TIME]", person.AdmitTime, person.PatientNumber)
End If
'Update Admit Source in CMSCORECARD table
If PatientListCMSC(i).AdmitSource <> person.AdmitSource Then
PatientListCMSC(i).AdmitSource = person.AdmitSource
UpdateScorecardTable("[ADMIT_SOURCE]", person.AdmitSource, person.PatientNumber)
End If
'Update Discharge Date in CMSCORECARD table
If PatientListCMSC(i).DischargeDate <> person.DischargeDate Then
PatientListCMSC(i).DischargeDate = person.DischargeDate
UpdateScorecardTable("[DISCHARGE_DATE]", person.DischargeDate, person.PatientNumber)
End If
'Update Discharge Time in CMSCORECARD table
If PatientListCMSC(i).DischargeTime <> person.DischargeTime Then
PatientListCMSC(i).DischargeTime = person.DischargeTime
UpdateScorecardTable("[DISCHARGE_TIME]", person.DischargeTime, person.PatientNumber)
End If
'Check for Different DRG in CMSCORECARD table
If PatientListCMSC(i).LastDRG <> PatientListCMSC(i).CurrentDRG Then
PatientListCMSC(i).LastDRG = PatientListCMSC(i).CurrentDRG
UpdateScorecardTable("[LAST_DRG]", PatientListCMSC(i).CurrentDRG, person.PatientNumber)
End If
'Update Current DRG in CMSCORECARD table
If PatientListCMSC(i).CurrentDRG <> person.CurrentDRG Then
PatientListCMSC(i).CurrentDRG = person.CurrentDRG
UpdateScorecardTable("[CURRENT_DRG]", person.CurrentDRG, person.PatientNumber)
End If
'Update ALOS in CMSCORECARD table
If PatientListCMSC(i).ALOS <> person.ALOS Then
PatientListCMSC(i).ALOS = person.ALOS
UpdateScorecardTable("[ALOS]", person.ALOS, person.PatientNumber)
End If
'Update PLOS in CMSCORECARD table
If PatientListCMSC(i).PLOS <> person.PLOS Then
PatientListCMSC(i).PLOS = person.PLOS
UpdateScorecardTable("[PLOS]", person.PLOS, person.PatientNumber)
End If
'Patient Exists in the Table
PatientInList = True
Exit For
End If
Next
'Patient Not found in the CMSCORECARD table/Add Patient to Table
If PatientInList = False Then
'Add the patient to the list
PatientListCMSC.Add(person)
'Build insert string for New Patient in List
cmd = New SqlCommand("INSERT INTO CMSCORECARD " &
"(CMSCORECARD.[PATNUM], " &
"CMSCORECARD.[ROOM], " &
"CMSCORECARD.[FC], " &
"CMSCORECARD.[Patient_Type], " &
"CMSCORECARD.[PATIENT_NAME], " &
"CMSCORECARD.[PHYSICIAN], " &
"CMSCORECARD.[ADMIT_DATE], " &
"CMSCORECARD.[ADMIT_TIME], " &
"CMSCORECARD.[ADMIT_SOURCE], " &
"CMSCORECARD.[LAST_DRG], " &
"CMSCORECARD.[CURRENT_DRG], " &
"CMSCORECARD.[PLOS], " &
"CMSCORECARD.[ALOS], " &
"CMSCORECARD.[DEPARTMENT], " &
"CMSCORECARD.[SI], " &
"CMSCORECARD.[IS], " &
"CMSCORECARD.[DCP], " &
"CMSCORECARD.[DC_Date], " &
"CMSCORECARD.[MD], " &
"CMSCORECARD.[PA], " &
"CMSCORECARD.[DCM], " &
"CMSCORECARD.[PAD], " &
"CMSCORECARD.[LEVEL], " &
"CMSCORECARD.[COMMENTS], " &
"CMSCORECARD.[DD], " &
"CMSCORECARD.[SD], " &
"CMSCORECARD.[DISCHARGE_DATE], " &
"CMSCORECARD.[DISCHARGE_TIME], " &
"CMSCORECARD.[COMPLETED]) " &
"VALUES " &
"(" &
"'" & person.PatientNumber & "'," &
"'" & person.Room & "'," &
"'" & person.FC & "'," &
"'" & person.Type & "'," &
"'" & person.PatientName & "'," &
"'" & person.AttendingPhy & "'," &
"'" & person.AdmitDate & "', " &
"'" & person.AdmitTime & "'," &
"'" & person.AdmitSource & "'," &
"'" & person.CurrentDRG & "'," &
"'" & person.CurrentDRG & "'," &
"'" & person.PLOS & "'," &
"'" & person.ALOS & "'," &
"'" & person.Department & "'," &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"NULL, " &
"" & StrToSql(person.DischargeDate) & ", " &
"" & StrToSql(person.DischargeTime) & ", " &
"NULL" &
")", con)
'Execute SQL String
cmd.ExecuteNonQuery()
End If
Next
'Filter for Needed Department and place person in the Final list for Display
For Each dept As String In DepartmentsNeeded
For Each person In PatientListCMSC
If person.Department = dept Then
PatientListFinal.Add(person)
End If
Next
Next
'Populate DataGridView with the Patient List
With DataGridView1
.DataSource = PatientListFinal
End With
'Close connection/Dispose
con.Close()
cmd.Dispose()
End Using
End Sub
Public Class Patient
'Patient Number
Private _PatientNumber As String
Public Property PatientNumber() As String
Get
Return _PatientNumber
End Get
Set(ByVal value As String)
_PatientNumber = value
End Set
End Property
'Patient Room Number
Private _Room As String
Public Property Room() As String
Get
Return _Room
End Get
Set(ByVal value As String)
_Room = value
End Set
End Property
'Financial Class
Private _FC As String
Public Property FC() As String
Get
Return _FC
End Get
Set(ByVal value As String)
_FC = value
End Set
End Property
'Patient Type
Private _Type As Integer
Public Property Type() As String
Get
Return _Type
End Get
Set(ByVal value As String)
_Type = value
End Set
End Property
'Patient Name
Private _PatientName As String
Public Property PatientName() As String
Get
Return _PatientName
End Get
Set(ByVal value As String)
_PatientName = value
End Set
End Property
'Attending Physician
Private _AttendingPhy As String
Public Property AttendingPhy() As String
Get
Return _AttendingPhy
End Get
Set(ByVal value As String)
_AttendingPhy = value
End Set
End Property
'Admit Date
Private _AdmitDate As Date
Public Property AdmitDate() As Date
Get
Return _AdmitDate
End Get
Set(ByVal value As Date)
_AdmitDate = value
End Set
End Property
'Admit Time
Private _AdmitTime As String
Public Property AdmitTime() As String
Get
Return _AdmitTime
End Get
Set(ByVal value As String)
_AdmitTime = value
End Set
End Property
'Admit Source
Private _AdmitSource As String
Public Property AdmitSource() As String
Get
Return _AdmitSource
End Get
Set(ByVal value As String)
_AdmitSource = value
End Set
End Property
'Last DRG
Private _LastDRG As String
Public Property LastDRG() As String
Get
Return _LastDRG
End Get
Set(ByVal value As String)
_LastDRG = value
End Set
End Property
'Current DRG
Private _CurrentDRG As String
Public Property CurrentDRG() As String
Get
Return _CurrentDRG
End Get
Set(ByVal value As String)
_CurrentDRG = value
End Set
End Property
'Predicted Length of Stay
Private _PLOS As String
Public Property PLOS() As String
Get
Return _PLOS
End Get
Set(ByVal value As String)
_PLOS = value
End Set
End Property
'Actual Length of Stay
Private _ALOS As String
Public Property ALOS() As String
Get
Return _ALOS
End Get
Set(ByVal value As String)
_ALOS = value
End Set
End Property
'SI - Severity of Illness
Private _SeverityOfIllness As String
Public Property SeverityOfIllness() As String
Get
Return _SeverityOfIllness
End Get
Set(ByVal value As String)
_SeverityOfIllness = value
End Set
End Property
'IS Intensity of Service
Private _IntensityOfService As String
Public Property IntensityOfService() As String
Get
Return _IntensityOfService
End Get
Set(ByVal value As String)
_IntensityOfService = value
End Set
End Property
'Discharge Plan
Private _DCP As String
Public Property DCP() As String
Get
Return _DCP
End Get
Set(ByVal value As String)
_DCP = value
End Set
End Property
'Expected Discharge Date
Private _DCDate As Date
Public Property DCDate() As Date
Get
Return _DCDate
End Get
Set(ByVal value As Date)
_DCDate = value
End Set
End Property
'MD
Private _MD As String
Public Property MD() As String
Get
Return _MD
End Get
Set(ByVal value As String)
_MD = value
End Set
End Property
'Physician Advisor
Private _PA As String
Public Property PA() As String
Get
Return _PA
End Get
Set(ByVal value As String)
_PA = value
End Set
End Property
'Director of Case Management
Private _DCM As String
Public Property DCM() As String
Get
Return _DCM
End Get
Set(ByVal value As String)
_DCM = value
End Set
End Property
'Possible Avoidable Days
Private _PAD As String
Public Property PAD() As String
Get
Return _PAD
End Get
Set(ByVal value As String)
_PAD = value
End Set
End Property
'Level of Service
Private _Level As Integer
Public Property Level() As Integer
Get
Return _Level
End Get
Set(ByVal value As Integer)
_Level = value
End Set
End Property
'Comments
Private _Comments As String
Public Property Comments() As String
Get
Return _Comments
End Get
Set(ByVal value As String)
_Comments = value
End Set
End Property
'Denied Days
Private _DeniedDays As Integer
Public Property DeniedDays() As Integer
Get
Return _DeniedDays
End Get
Set(ByVal value As Integer)
_DeniedDays = value
End Set
End Property
'Save Days
Private _SaveDays As Integer
Public Property SaveDays() As Integer
Get
Return _SaveDays
End Get
Set(ByVal value As Integer)
_SaveDays = value
End Set
End Property
'Discharge Date
Private _DischargeDate As Date
Public Property DischargeDate() As Date
Get
Return _DischargeDate
End Get
Set(ByVal value As Date)
_DischargeDate = value
End Set
End Property
'Discharge Time
Private _DischargeTime As String
Public Property DischargeTime() As String
Get
Return _DischargeTime
End Get
Set(ByVal value As String)
_DischargeTime = value
End Set
End Property
'Completed
Private _Completed As String
Public Property Completed() As String
Get
Return _Completed
End Get
Set(ByVal value As String)
_Completed = value
End Set
End Property
'Patient Department
Private _Department As String
Public Property Department() As String
Get
Return _Department
End Get
Set(ByVal value As String)
_Department = value
End Set
End Property
End Class
