Disconnected Recordset

  • Thread starter Thread starter BenignVanilla
  • Start date Start date
B

BenignVanilla

I've got a class that includes the following declaration and property...

Private mobjResults As SqlDataReader

Public ReadOnly Property Results() As SqlDataReader
Get
Results = mobjResults
End Get
End Property

I then have a public method that fetches some DB results into a
SQLDataReader, and places this result in mobjResults.

This works fine, except, that I use a shared DB connection, so if I
terminate the connection at any time, this recordset get eliminated, and can
no longer be accessed on the property of the object.

What object can I put the results of the sqlreader into, so that I can
disconnect the sqlreader, and just have the results in the property?

BV.

www.human8ball.com
 
Hi,

Instead you should using a SqlDataAdapter and fill a DataTable using its
Fill method.
The table is filled immediately and then you can drop your connection.
 
BeningVanilla - as Miha mentioned, I think using a DataAdapter/DataTable
approach is the way to go for you. With the advent of ADO.NET 2.0 , you can
use one connection with multiple commands (MARS) but even with MARS, the
problem you describe will persist. DataReaders need open and available
connections to operate so there's really no way around the problem you
describe if you must use a datareader and you must close the connection in
the middle of it. However a datatable will accomplish the same task for you
in terms of the end result b/c the adapter will fill the datatable, then
close the connection and return the data persisted in the datatable. When
used very precisely, a datareader is 'faster' than a data adapter, but with
that performance comes a lot of maintenance , one element of which you have
just come across.
 
Okay, first of all, "Recordset" doesn't exist in ADO.NET, that is a ADO
classic term (I am guessing you already knew this, but just being clear) :)

Secondly, you need a DataTable/DataAdapter as Miha and Bill already pointed
out :)

Finally, "Shared DB Connection" is a bad idea - you should instead leverage
connection pooling.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Back
Top