reusing same reader object throughout lifetime of app

[vbDavidC]

Hi,

I am fairly new to .net and objects.

I learned to create a reader object in method 1, however if I wanted
to create multiple select queries in the same module I did not know
how to reuse the same code, I would like to verify if method 2 is the
right way to reuse the same reader object.

If this is the case could I open a reader object (that is global) at
the beginning of my program and just change run a .commandtext
and .executereader commands whenever I wanted to load it with
different data. My database is single user and contains very small
datasets.

(method 1)
Dim SQL As String = "SELECT Max(ID) FROM TempInventory WHERE Test =
'SRT'"
Dim cmd As New OleDbCommand(SQL, con)
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()

Do While reader.Read
FindLastSRTNumber = reader(0)
Loop

reader.Close()

(end method 1)

and/or

(method 2)

Dim SQL as String = "SELECT Max(ID) FROM TempInventory WHERE Test =
'SRT'"
Dim cmd As New OleDbCommand(sql, con)
Dim reader As OleDbDataReader

reader = cmd.ExecuteReader()

Do While reader.Read
. (whatever)
. (whatever)
Loop

reader.Close()

'2nd query in same module (private sub)
sql = "SELECT ID1, ID2 FROM TempInventory WHERE TestNumber =
'03097'
cmd.CommandText = sql
reader = cmd.ExecuteReader()

Do While reader.Read

(end method 2)

 

[sloan]

You need to create and dispose of you IDataReader objects as late (create)
and as soon (destroy) as you can.


Global DataReader object for reuse = BAD IDEA


...

On the other side of things, you can use the
(IDataReader.NextResult()
method.


http://sholliday.spaces.live.com/blog/
5/24/2006
Custom Objects/Collections and Tiered Development

CHeck out that example. Its in C#, but you'll be able to pick up the
principles.

AGain, I'm going to say this again so you're absolutely clear:

//quote

If this is the case could I open a reader object (that is global) at
the beginning of my program and just change run a .commandtext
and .executereader commands whenever I wanted to load it with
different data.

//end quote

This is a BAD idea. No matter how small your app or resultsets are.