Newbie question about open/close connection

  • Thread starter Thread starter Kay
  • Start date Start date
K

Kay

Hi All,

I have loop that loop thru a data reader (i.e. conn = DB1Conn), in the loop,
I need to select some records from another database (i.e. conn - DB2Conn):

Do while theReader.Read

DB2Conn.Open
CmdSql = New SqlCommand("Select blar where Key = '" & theReader("Key")
& "'",DB2Conn)
Reader2 = Cmd.ExecuteReader
'Do something else...
DB2Conn.Close

DB2Conn.Open
CmdSql = New SqlCommand("Select blarblar where Key = '" &
theReader("Key") & "'",DB2Conn)
Reader2 = Cmd.ExecuteReader
'Do something else...
DB2Conn.Close

loop

Found that before I can do the 2nd select in the loop, I must close the
connection even the second select statment connect to the same database. Is
there any smarter, more efficient way to do the task?

Thanks.

Kay
 
Use datasets or typed data sets. If you are looping through data reader
records and subsequently opening up more data readers to get releated
records the better approach is to get all related data and filling a
data sets and then looping through the in-memory DataRow objects of the
table.

And by using typed data sets you can set relationships between the
seperate tables that will alow you to get related data from detail
tables easily.

Hope this helps...
- NuTcAsE
 
2 DataReaders cannot share 1 Connection, as both are constantly connected to
a database during their lifetimes. You can use 2 Connections, or (better)
use a DataTable to get the first result set. A DataTable is a disconnected
set of records.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
We got a sick zebra a hat,
you ultimate tuna.
 
Back
Top