Filling Int Array from DataReader

[Guest]

Hi,
I wanted to run this by the gurus out there to see if my method of filling
an integer array from a DataReader makes sense. The number of rows the
DataReader will return would not be known beforehand, unless I make a
separate ExecuteScalar call to the database and use a sproc that will return
COUNT(ApptIDs) from my table. Since I do not want to make this extra call to
the DB, I am creating an empty integer array and then using ReDim with
Preserve to resize the array for each record returned by the DataReader.
Does this make sense? Is there a better way? Thanks. JT

Here's my code:

Private Function GetMissedAppts() As Integer()

Dim ApptIDs() As Integer = New Integer() {}

Dim dR As SqlDataReader

Dim count As Integer

Try

Me.cnSA.Open()

dR = Me.sqlCmdGetMissedAppts.ExecuteReader(CommandBehavior.CloseConnection)

With dR

If .HasRows Then

While .Read

ReDim Preserve ApptIDs(count)

ApptIDs(count) = .GetInt32(0)

count += 1

End While

End If

End With

dR.Close()

Me.cnSA.Close()

Return ApptIDs

Catch ex As Exception
LogExceptions(Me.Name, "GetMissedAppts", ex)
MessageBox.Show("An error occurred retrieving the list of missed
appointments", "Missed Appts Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)

Return Nothing

Finally
If dR.IsClosed = False Then dR.Close()
SlamConnectionShut(Me.cnSA)
End Try

End Function

 

[Miha Markic [MVP C#]]

Hi,

Hi,
I wanted to run this by the gurus out there to see if my method of filling
an integer array from a DataReader makes sense. The number of rows the
DataReader will return would not be known beforehand, unless I make a
separate ExecuteScalar call to the database and use a sproc that will
return
COUNT(ApptIDs) from my table. Since I do not want to make this extra call
to
the DB, I am creating an empty integer array and then using ReDim with
Preserve to resize the array for each record returned by the DataReader.
Does this make sense? Is there a better way? Thanks. JT

Yes, there is. Use an ArrayList instance to add items at invoke ToArray
method at the end if you need a static array.
ReDiming all the time is a big slowdown as it copies entire array every
time.

 

[Guest]

Thanks Miha. This is what I came up with (it works!)

Private Function GetApptsForReminder() As Integer()

Dim myList As New ArrayList

Dim dR As SqlDataReader

Dim count As Integer

Try

Me.cnSA.Open()

dR = Me.sqlCmdGetMissedAppts.ExecuteReader(CommandBehavior.CloseConnection)

With dR

If .HasRows Then

While .Read

myList.Add(.GetInt32(0))
count += 1

End While

End If

End With

dR.Close()

Me.cnSA.Close()

If myList.Count > 0 Then
Dim ApptIDs() As Integer = Array.CreateInstance(GetType(Integer),
myList.Count)
myList.CopyTo(ApptIDs)
Return ApptIDs
End If


Catch ex As Exception
LogExceptions(Me.Name, "GetMissedAppts", ex)
MessageBox.Show("An error occurred retrieving the list of missed
appointments", "Missed Appts Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)

Return Nothing

Finally
If dR.IsClosed = False Then dR.Close()
SlamConnectionShut(Me.cnSA)
End Try

End Function
JT