By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,784 Members | 3,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,784 IT Pros & Developers. It's quick & easy.

reusing same reader object throughout lifetime of app

P: n/a
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)

Mar 27 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a

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.

"vbDavidC" <ch*************@yahoo.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...
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)

Mar 27 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.