Closing of OleDbConnection and OleDbDataReader. Is there a better way?

[Mel]

I am performing the same recordset multiple times, just passing
different parameters each time. Is there a way to do this more
efficiently without having to close and re-open the connection and
reader? I thought there might be a cleaner way, this code seems to
take "forever" to run.

'-------BEGINNING OF CODE EXAMPLE-------
Dim SBInvNum As String
Dim strConInv As String = "Provider=Microsoft.JET.OLEDB.4.0;Data
Source =" & "\\myserver\invwhs.mdb"
Dim strRecInv As String = "SELECT * FROM
[invwhs_detail_local_with_desc] WHERE [whs] = ? and [ITEM_NBR]
LIKE ?;"
Dim conInv As New System.Data.OleDb.OleDbConnection(strConInv)
Dim comInv As New System.Data.OleDb.OleDbCommand(strRecInv, conInv)
Dim recInv As System.Data.OleDb.OleDbDataReader
For x = 0 To UBound(PartsArray, 2)
SBInvNum = GetInvNum(PartsArray(0, x), PartsArray(1, x),
PartsArray(2, x)) 'generate part number
conInv.Open()

'see if the part is available at all plants, if it isn't clear the
plant from the array.
For j = 0 To i
comInv.Parameters.AddWithValue("P1", GoodPlants(0, j))
comInv.Parameters.AddWithValue("P2", SBInvNum & "%")
recInv = comInv.ExecuteReader
If Not recInv.Read() Then
GoodPlants(0, j) = ""
GoodPlants(1, j) = ""
GoodPlants(2, j) = ""
GoodPlants(3, j) = ""
End If
recInv.Close() 'IS THERE A BETTER WAY? INSTEAD OF CLOSING AND
RE-OPENING?
comInv.Parameters.Clear()
Next j
conInv.Close()
Next x
'-------END OF CODE EXAMPLE-------

[Jesse Houwing]

Hello Mel,

You could just close the reader (you need to pass it a special parameter
so it won't close the connection while it's at it) and open a new reader
on the same connection. You can use the same command as before if you want
to.

By the way. OleDBCommand also implements IDisposable and should be disposed
when you're done with it.

I prefer using the using clause in C#, there's probably a similar thing in
VB.NET. The syntax in C# is:

using(OleDBConnection conInv = new OleDBConnection(...))
{

}

this makes sure that when the end } is passed, close will be called automatically.
Even if an exception occurs. Makes your code a lot easier to read if you
use it consistently.

Jesse

I am performing the same recordset multiple times, just passing
different parameters each time. Is there a way to do this more
efficiently without having to close and re-open the connection and
reader? I thought there might be a cleaner way, this code seems to
take "forever" to run.

'-------BEGINNING OF CODE EXAMPLE-------
Dim SBInvNum As String
Dim strConInv As String = "Provider=Microsoft.JET.OLEDB.4.0;Data
Source =" & "\\myserver\invwhs.mdb"
Dim strRecInv As String = "SELECT * FROM
[invwhs_detail_local_with_desc] WHERE [whs] = ? and [ITEM_NBR]
LIKE ?;"
Dim conInv As New System.Data.OleDb.OleDbConnection(strConInv)
Dim comInv As New System.Data.OleDb.OleDbCommand(strRecInv, conInv)
Dim recInv As System.Data.OleDb.OleDbDataReader
For x = 0 To UBound(PartsArray, 2)
SBInvNum = GetInvNum(PartsArray(0, x), PartsArray(1, x),
PartsArray(2, x)) 'generate part number
conInv.Open()
'see if the part is available at all plants, if it isn't clear the
plant from the array.
For j = 0 To i
comInv.Parameters.AddWithValue("P1", GoodPlants(0, j))
comInv.Parameters.AddWithValue("P2", SBInvNum & "%")
recInv = comInv.ExecuteReader
If Not recInv.Read() Then
GoodPlants(0, j) = ""
GoodPlants(1, j) = ""
GoodPlants(2, j) = ""
GoodPlants(3, j) = ""
End If
recInv.Close() 'IS THERE A BETTER WAY? INSTEAD OF CLOSING AND
RE-OPENING?
comInv.Parameters.Clear()
Next j
conInv.Close()
Next x
'-------END OF CODE EXAMPLE-------