«

DataReaders are meant for operations that involve reading all the data right
away, and closing the reader. This is because the reader maintains an open
connection to the database - a valuable resource. I am not surprised you are
seeing this problem.

Now, a dataview, has nothing to do with database access itself. It's really
the datatable that can be used to place data into from the result of a
query. A dataview is another layer that can be put on top of that, to
provide a sorted or filtered view of the data in the underlying table.

I definitely recommend you go ahead and eliminate the datareader.
Datareader should never be returned from functions - as this requires the
consumer of the function to remember to close the data reader once it is no
longer needed. This isn't something anyone should rely on - not to mention,
you never know how long the consumer of the function will keep that reader
open. It is no wonder you are experiencing all these problems.


"Mark" <> wrote in message
news:chqfnl$i4j$...
> Hi all, quick question [Please correct me where I am wong ], a DataView
is
> memory resident "view" of data in a data table therefore once populated
you
> can close the connection to the database. Garbage collection can then be
> used to "clean up" the DataView once it is not referenced and will not
> effect the number of connections to the database.
>
> A DataReader on the other hand always maintains a connection to the
database
> and must be explicitly closed (Do not rely on garbage collection).
>
> Reason I am asking is that I have been give a heap of vb.net code and
there
> are numerous functions that return a DataReader (Causing all sorts of SQL
> Timeout headaches, maximum connections reached etc). To solve this problem
> in the quickest amount of time I am thinking of replacing the DataReaders
> with DataViews so I can explicitly close the Database connection.
>
> Any other ideas (I would model the database via business objects and
return
> these but I really do not have the time as we need a "quote" quick fix).
>
> Thanks
> Mark
>
>

However, some would argue that a Datareader is much more efficient for
forward-only data reading which is what it's built for. It really depends on
the application.

If your're not gonna do any data transformation after retrieving the data,
why bother with the overhead of a Dataset/Dataview?

As far as keeping connections open, this is just getting into the habit of
using best practices. If you're using a Dataview you still have to close the
SqlConnection, right?

I would not necessarily recommend not using the datareader. It all depends
on the situation and how it's used in the code.

-Frank Mamone

"Marina" <> wrote in message
news:OECuJ$...
> DataReaders are meant for operations that involve reading all the data
right
> away, and closing the reader. This is because the reader maintains an
open
> connection to the database - a valuable resource. I am not surprised you
are
> seeing this problem.
>
> Now, a dataview, has nothing to do with database access itself. It's
really
> the datatable that can be used to place data into from the result of a
> query. A dataview is another layer that can be put on top of that, to
> provide a sorted or filtered view of the data in the underlying table.
>
> I definitely recommend you go ahead and eliminate the datareader.
> Datareader should never be returned from functions - as this requires the
> consumer of the function to remember to close the data reader once it is
no
> longer needed. This isn't something anyone should rely on - not to
mention,
> you never know how long the consumer of the function will keep that reader
> open. It is no wonder you are experiencing all these problems.
>
>
> "Mark" <> wrote in message
> news:chqfnl$i4j$...
> > Hi all, quick question [Please correct me where I am wong ], a
DataView
> is
> > memory resident "view" of data in a data table therefore once populated
> you
> > can close the connection to the database. Garbage collection can then be
> > used to "clean up" the DataView once it is not referenced and will not
> > effect the number of connections to the database.
> >
> > A DataReader on the other hand always maintains a connection to the
> database
> > and must be explicitly closed (Do not rely on garbage collection).
> >
> > Reason I am asking is that I have been give a heap of vb.net code and
> there
> > are numerous functions that return a DataReader (Causing all sorts of
SQL
> > Timeout headaches, maximum connections reached etc). To solve this
problem
> > in the quickest amount of time I am thinking of replacing the
DataReaders
> > with DataViews so I can explicitly close the Database connection.
> >
> > Any other ideas (I would model the database via business objects and
> return
> > these but I really do not have the time as we need a "quote" quick fix).
> >
> > Thanks
> > Mark
> >
> >
>
>

A datareader only keeps the connection open (if closed properly, and using
CommandBehavior.CloseConnection) as long as it takes to read the data.
Using a dataadapter to fill a dataset/dataview uses a datareader behind the
scenes anyways.

Although I haven't look in awhile, wasn't IBuySpy portal built using mostly
datareaders returned from functions? I would ask around a little more
before scraping the datareader solution. I always read the datareaders where
more scalable.

This all sounds wrong to me.

My .02
Greg


"Marina" <> wrote in message
news:OECuJ$...
> DataReaders are meant for operations that involve reading all the data
> right
> away, and closing the reader. This is because the reader maintains an
> open
> connection to the database - a valuable resource. I am not surprised you
> are
> seeing this problem.
>
> Now, a dataview, has nothing to do with database access itself. It's
> really
> the datatable that can be used to place data into from the result of a
> query. A dataview is another layer that can be put on top of that, to
> provide a sorted or filtered view of the data in the underlying table.
>
> I definitely recommend you go ahead and eliminate the datareader.
> Datareader should never be returned from functions - as this requires the
> consumer of the function to remember to close the data reader once it is
> no
> longer needed. This isn't something anyone should rely on - not to
> mention,
> you never know how long the consumer of the function will keep that reader
> open. It is no wonder you are experiencing all these problems.
>
>
> "Mark" <> wrote in message
> news:chqfnl$i4j$...
>> Hi all, quick question [Please correct me where I am wong ], a DataView
> is
>> memory resident "view" of data in a data table therefore once populated
> you
>> can close the connection to the database. Garbage collection can then be
>> used to "clean up" the DataView once it is not referenced and will not
>> effect the number of connections to the database.
>>
>> A DataReader on the other hand always maintains a connection to the
> database
>> and must be explicitly closed (Do not rely on garbage collection).
>>
>> Reason I am asking is that I have been give a heap of vb.net code and
> there
>> are numerous functions that return a DataReader (Causing all sorts of SQL
>> Timeout headaches, maximum connections reached etc). To solve this
>> problem
>> in the quickest amount of time I am thinking of replacing the DataReaders
>> with DataViews so I can explicitly close the Database connection.
>>
>> Any other ideas (I would model the database via business objects and
> return
>> these but I really do not have the time as we need a "quote" quick fix).
>>
>> Thanks
>> Mark
>>
>>
>
>

Hi all, quick question [Please correct me where I am wong ], a DataView is
memory resident "view" of data in a data table therefore once populated you
can close the connection to the database. Garbage collection can then be
used to "clean up" the DataView once it is not referenced and will not
effect the number of connections to the database.

A DataReader on the other hand always maintains a connection to the database
and must be explicitly closed (Do not rely on garbage collection).

Reason I am asking is that I have been give a heap of vb.net code and there
are numerous functions that return a DataReader (Causing all sorts of SQL
Timeout headaches, maximum connections reached etc). To solve this problem
in the quickest amount of time I am thinking of replacing the DataReaders
with DataViews so I can explicitly close the Database connection.

Any other ideas (I would model the database via business objects and return
these but I really do not have the time as we need a "quote" quick fix).

Thanks
Mark

Hi Marina, thanks, that is exactly what I thought. I cringed when I saw all
the DataReader return types, anyway, thanks again

Cheers
Mark

"Marina" <> wrote in message
news:OECuJ$...
> DataReaders are meant for operations that involve reading all the data
right
> away, and closing the reader. This is because the reader maintains an
open
> connection to the database - a valuable resource. I am not surprised you
are
> seeing this problem.
>
> Now, a dataview, has nothing to do with database access itself. It's
really
> the datatable that can be used to place data into from the result of a
> query. A dataview is another layer that can be put on top of that, to
> provide a sorted or filtered view of the data in the underlying table.
>
> I definitely recommend you go ahead and eliminate the datareader.
> Datareader should never be returned from functions - as this requires the
> consumer of the function to remember to close the data reader once it is
no
> longer needed. This isn't something anyone should rely on - not to
mention,
> you never know how long the consumer of the function will keep that reader
> open. It is no wonder you are experiencing all these problems.
>
>
> "Mark" <> wrote in message
> news:chqfnl$i4j$...
> > Hi all, quick question [Please correct me where I am wong ], a
DataView
> is
> > memory resident "view" of data in a data table therefore once populated
> you
> > can close the connection to the database. Garbage collection can then be
> > used to "clean up" the DataView once it is not referenced and will not
> > effect the number of connections to the database.
> >
> > A DataReader on the other hand always maintains a connection to the
> database
> > and must be explicitly closed (Do not rely on garbage collection).
> >
> > Reason I am asking is that I have been give a heap of vb.net code and
> there
> > are numerous functions that return a DataReader (Causing all sorts of
SQL
> > Timeout headaches, maximum connections reached etc). To solve this
problem
> > in the quickest amount of time I am thinking of replacing the
DataReaders
> > with DataViews so I can explicitly close the Database connection.
> >
> > Any other ideas (I would model the database via business objects and
> return
> > these but I really do not have the time as we need a "quote" quick fix).
> >
> > Thanks
> > Mark
> >
> >
>
>

Mark, here is some code I just grabbed from IBS (IBuySpy) (they are ALL like
this):

Public Function GetUsers() As SqlDataReader

' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
Dim myCommand As New SqlCommand("GetUsers", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Open the database connection and execute the command
myConnection.Open()
Dim dr As SqlDataReader =
myCommand.ExecuteReader(CommandBehavior.CloseConne ction)

' Return the datareader
Return dr

End Function



' bind all portal users to dropdownlist
allUsers.DataSource = roles.GetUsers()
allUsers.DataBind()


Notice that it doesn't appear to close the datareader that is returned from
GetUsers. That is simply because the databind closes automagically when it
is finished binding. (I'm not making that up!) Then, when the dr is closed
the connection is closed because of the command behavior.

In your example, I would add the CommandBehavior.CloseConnection inside the
function.

Then in the code you showed
Dim oPerson as new getPerson
Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)

' do something here with oDR

oDR.Close()

HTH,
Greg


"Mark" <> wrote in message
news:chqo6l$o64$...
> Hi all, below is typically what one of the functions look like inside one
> of
> the classes, note psuedocode only
>
> ======================
> [vbcode]
>
> Public Class getPerson
>
> ' Function returns person details
> Public Function GetPersonDetails(PersonID as integer) as SqlDataReader
> Dim oDataReader as SqlDataReader
> Dim oConn as new SqlConnection("Connection string......")
> ' Connect to database, get rows and return datareader.....
> return oDataReader
> End Function
>
> End Class
>
> [/vbcode]
>
> In the application the Person class is instantiated like so
>
> [vbcode]
> Dim oPerson as new getPerson
> Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)
> [/vbcode]
>
> The problem is that throughout the application the code is never closing
> the
> DataReader object. Also, all the datareader objects, connection objects
> are
> created locally within each routine in the class and are therefore
> private.
> The actual design of the application is very bad (No central database
> class
> for example).
>
> So, given that the DataReader is not being explicitly closed (hence the
> SQL
> maximum connection reached errors etc) any suggestions about how I can
> explicitly retrieve information and then close the datareader are welcome.
> I
> really do not have time to do a major redesign of the application as well.
> I
> know that I can use the CommandBehavior.CloseConnection argument when
> executing the reader but this only closes the connection when the
> SqlDataReader.Close() method is called. As per the original problem, the
> close() method is never getting called.
>
> Using the DataView in the above class I could just go
>
> [vbcode]
>
> Public Class getPerson
>
> ' Function returns person details
> Public Function GetPersonDetails(PersonID as integer) as DataView
> Dim oDataView as DataView
> Dim oConn as new SqlConnection("Connection string......")
> ' Connect to database, get table and return dataview after closing the
> connection......
> oConn.Close()
> return oDataView
> End Class
>
> [/vbcode]
>
> The above piece of code closes the connection to the database and returns
> a
> dataview instead of a datareader, thus closing the connection to the
> database...
>
> Ideas/ comments welcome
> Thanks again
> Mark
>
>
> "Mark" <> wrote in message
> news:chqgfk$ine$...
>> Hi Marina, thanks, that is exactly what I thought. I cringed when I saw
> all
>> the DataReader return types, anyway, thanks again
>>
>> Cheers
>> Mark
>>
>> "Marina" <> wrote in message
>> news:OECuJ$...
>> > DataReaders are meant for operations that involve reading all the data
>> right
>> > away, and closing the reader. This is because the reader maintains an
>> open
>> > connection to the database - a valuable resource. I am not surprised
>> > you
>> are
>> > seeing this problem.
>> >
>> > Now, a dataview, has nothing to do with database access itself. It's
>> really
>> > the datatable that can be used to place data into from the result of a
>> > query. A dataview is another layer that can be put on top of that, to
>> > provide a sorted or filtered view of the data in the underlying table.
>> >
>> > I definitely recommend you go ahead and eliminate the datareader.
>> > Datareader should never be returned from functions - as this requires
> the
>> > consumer of the function to remember to close the data reader once it
>> > is
>> no
>> > longer needed. This isn't something anyone should rely on - not to
>> mention,
>> > you never know how long the consumer of the function will keep that
> reader
>> > open. It is no wonder you are experiencing all these problems.
>> >
>> >
>> > "Mark" <> wrote in message
>> > news:chqfnl$i4j$...
>> > > Hi all, quick question [Please correct me where I am wong ], a
>> DataView
>> > is
>> > > memory resident "view" of data in a data table therefore once
> populated
>> > you
>> > > can close the connection to the database. Garbage collection can then
> be
>> > > used to "clean up" the DataView once it is not referenced and will
>> > > not
>> > > effect the number of connections to the database.
>> > >
>> > > A DataReader on the other hand always maintains a connection to the
>> > database
>> > > and must be explicitly closed (Do not rely on garbage collection).
>> > >
>> > > Reason I am asking is that I have been give a heap of vb.net code and
>> > there
>> > > are numerous functions that return a DataReader (Causing all sorts of
>> SQL
>> > > Timeout headaches, maximum connections reached etc). To solve this
>> problem
>> > > in the quickest amount of time I am thinking of replacing the
>> DataReaders
>> > > with DataViews so I can explicitly close the Database connection.
>> > >
>> > > Any other ideas (I would model the database via business objects and
>> > return
>> > > these but I really do not have the time as we need a "quote" quick
> fix).
>> > >
>> > > Thanks
>> > > Mark
>> > >
>> > >
>> >
>> >
>>
>>
>
>

And for gosh sakes, set your clock correctly or people will start ignoring
you.

Greg

"Mark" <> wrote in message
news:chqo6l$o64$...
> Hi all, below is typically what one of the functions look like inside one
> of
> the classes, note psuedocode only
>
> ======================
> [vbcode]
>
> Public Class getPerson
>
> ' Function returns person details
> Public Function GetPersonDetails(PersonID as integer) as SqlDataReader
> Dim oDataReader as SqlDataReader
> Dim oConn as new SqlConnection("Connection string......")
> ' Connect to database, get rows and return datareader.....
> return oDataReader
> End Function
>
> End Class
>
> [/vbcode]
>
> In the application the Person class is instantiated like so
>
> [vbcode]
> Dim oPerson as new getPerson
> Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)
> [/vbcode]
>
> The problem is that throughout the application the code is never closing
> the
> DataReader object. Also, all the datareader objects, connection objects
> are
> created locally within each routine in the class and are therefore
> private.
> The actual design of the application is very bad (No central database
> class
> for example).
>
> So, given that the DataReader is not being explicitly closed (hence the
> SQL
> maximum connection reached errors etc) any suggestions about how I can
> explicitly retrieve information and then close the datareader are welcome.
> I
> really do not have time to do a major redesign of the application as well.
> I
> know that I can use the CommandBehavior.CloseConnection argument when
> executing the reader but this only closes the connection when the
> SqlDataReader.Close() method is called. As per the original problem, the
> close() method is never getting called.
>
> Using the DataView in the above class I could just go
>
> [vbcode]
>
> Public Class getPerson
>
> ' Function returns person details
> Public Function GetPersonDetails(PersonID as integer) as DataView
> Dim oDataView as DataView
> Dim oConn as new SqlConnection("Connection string......")
> ' Connect to database, get table and return dataview after closing the
> connection......
> oConn.Close()
> return oDataView
> End Class
>
> [/vbcode]
>
> The above piece of code closes the connection to the database and returns
> a
> dataview instead of a datareader, thus closing the connection to the
> database...
>
> Ideas/ comments welcome
> Thanks again
> Mark
>
>
> "Mark" <> wrote in message
> news:chqgfk$ine$...
>> Hi Marina, thanks, that is exactly what I thought. I cringed when I saw
> all
>> the DataReader return types, anyway, thanks again
>>
>> Cheers
>> Mark
>>
>> "Marina" <> wrote in message
>> news:OECuJ$...
>> > DataReaders are meant for operations that involve reading all the data
>> right
>> > away, and closing the reader. This is because the reader maintains an
>> open
>> > connection to the database - a valuable resource. I am not surprised
>> > you
>> are
>> > seeing this problem.
>> >
>> > Now, a dataview, has nothing to do with database access itself. It's
>> really
>> > the datatable that can be used to place data into from the result of a
>> > query. A dataview is another layer that can be put on top of that, to
>> > provide a sorted or filtered view of the data in the underlying table.
>> >
>> > I definitely recommend you go ahead and eliminate the datareader.
>> > Datareader should never be returned from functions - as this requires
> the
>> > consumer of the function to remember to close the data reader once it
>> > is
>> no
>> > longer needed. This isn't something anyone should rely on - not to
>> mention,
>> > you never know how long the consumer of the function will keep that
> reader
>> > open. It is no wonder you are experiencing all these problems.
>> >
>> >
>> > "Mark" <> wrote in message
>> > news:chqfnl$i4j$...
>> > > Hi all, quick question [Please correct me where I am wong ], a
>> DataView
>> > is
>> > > memory resident "view" of data in a data table therefore once
> populated
>> > you
>> > > can close the connection to the database. Garbage collection can then
> be
>> > > used to "clean up" the DataView once it is not referenced and will
>> > > not
>> > > effect the number of connections to the database.
>> > >
>> > > A DataReader on the other hand always maintains a connection to the
>> > database
>> > > and must be explicitly closed (Do not rely on garbage collection).
>> > >
>> > > Reason I am asking is that I have been give a heap of vb.net code and
>> > there
>> > > are numerous functions that return a DataReader (Causing all sorts of
>> SQL
>> > > Timeout headaches, maximum connections reached etc). To solve this
>> problem
>> > > in the quickest amount of time I am thinking of replacing the
>> DataReaders
>> > > with DataViews so I can explicitly close the Database connection.
>> > >
>> > > Any other ideas (I would model the database via business objects and
>> > return
>> > > these but I really do not have the time as we need a "quote" quick
> fix).
>> > >
>> > > Thanks
>> > > Mark
>> > >
>> > >
>> >
>> >
>>
>>
>
>

I am by no means an expert on those kinds of things, but your posts are
showing up more than one hour in the future compared to everyone elses.
That is always a sign that your local pc's clock/time zone is not quite
right.

Somebody who knows something about news servers is already typing an email
to tell me I'm wrong. ;^)

Greg


"Mark" <> wrote in message
news:chqrhs$s0c$...
> Hi Greg, actually, that is the correct time here I'm in New Zealand and
> right now it's lunchtime on Friday
>
>
> "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
> news:...
>> And for gosh sakes, set your clock correctly or people will start
>> ignoring
>> you.
>>
>> Greg
>>
>> "Mark" <> wrote in message
>> news:chqo6l$o64$...
>> > Hi all, below is typically what one of the functions look like inside
> one
>> > of
>> > the classes, note psuedocode only
>> >
>> > ======================
>> > [vbcode]
>> >
>> > Public Class getPerson
>> >
>> > ' Function returns person details
>> > Public Function GetPersonDetails(PersonID as integer) as SqlDataReader
>> > Dim oDataReader as SqlDataReader
>> > Dim oConn as new SqlConnection("Connection string......")
>> > ' Connect to database, get rows and return datareader.....
>> > return oDataReader
>> > End Function
>> >
>> > End Class
>> >
>> > [/vbcode]
>> >
>> > In the application the Person class is instantiated like so
>> >
>> > [vbcode]
>> > Dim oPerson as new getPerson
>> > Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)
>> > [/vbcode]
>> >
>> > The problem is that throughout the application the code is never
>> > closing
>> > the
>> > DataReader object. Also, all the datareader objects, connection objects
>> > are
>> > created locally within each routine in the class and are therefore
>> > private.
>> > The actual design of the application is very bad (No central database
>> > class
>> > for example).
>> >
>> > So, given that the DataReader is not being explicitly closed (hence the
>> > SQL
>> > maximum connection reached errors etc) any suggestions about how I can
>> > explicitly retrieve information and then close the datareader are
> welcome.
>> > I
>> > really do not have time to do a major redesign of the application as
> well.
>> > I
>> > know that I can use the CommandBehavior.CloseConnection argument when
>> > executing the reader but this only closes the connection when the
>> > SqlDataReader.Close() method is called. As per the original problem,
>> > the
>> > close() method is never getting called.
>> >
>> > Using the DataView in the above class I could just go
>> >
>> > [vbcode]
>> >
>> > Public Class getPerson
>> >
>> > ' Function returns person details
>> > Public Function GetPersonDetails(PersonID as integer) as DataView
>> > Dim oDataView as DataView
>> > Dim oConn as new SqlConnection("Connection string......")
>> > ' Connect to database, get table and return dataview after closing
> the
>> > connection......
>> > oConn.Close()
>> > return oDataView
>> > End Class
>> >
>> > [/vbcode]
>> >
>> > The above piece of code closes the connection to the database and
> returns
>> > a
>> > dataview instead of a datareader, thus closing the connection to the
>> > database...
>> >
>> > Ideas/ comments welcome
>> > Thanks again
>> > Mark
>> >
>> >
>> > "Mark" <> wrote in message
>> > news:chqgfk$ine$...
>> >> Hi Marina, thanks, that is exactly what I thought. I cringed when I
>> >> saw
>> > all
>> >> the DataReader return types, anyway, thanks again
>> >>
>> >> Cheers
>> >> Mark
>> >>
>> >> "Marina" <> wrote in message
>> >> news:OECuJ$...
>> >> > DataReaders are meant for operations that involve reading all the
> data
>> >> right
>> >> > away, and closing the reader. This is because the reader maintains
> an
>> >> open
>> >> > connection to the database - a valuable resource. I am not surprised
>> >> > you
>> >> are
>> >> > seeing this problem.
>> >> >
>> >> > Now, a dataview, has nothing to do with database access itself.
>> >> > It's
>> >> really
>> >> > the datatable that can be used to place data into from the result of
> a
>> >> > query. A dataview is another layer that can be put on top of that,
>> >> > to
>> >> > provide a sorted or filtered view of the data in the underlying
> table.
>> >> >
>> >> > I definitely recommend you go ahead and eliminate the datareader.
>> >> > Datareader should never be returned from functions - as this
>> >> > requires
>> > the
>> >> > consumer of the function to remember to close the data reader once
>> >> > it
>> >> > is
>> >> no
>> >> > longer needed. This isn't something anyone should rely on - not to
>> >> mention,
>> >> > you never know how long the consumer of the function will keep that
>> > reader
>> >> > open. It is no wonder you are experiencing all these problems.
>> >> >
>> >> >
>> >> > "Mark" <> wrote in message
>> >> > news:chqfnl$i4j$...
>> >> > > Hi all, quick question [Please correct me where I am wong ], a
>> >> DataView
>> >> > is
>> >> > > memory resident "view" of data in a data table therefore once
>> > populated
>> >> > you
>> >> > > can close the connection to the database. Garbage collection can
> then
>> > be
>> >> > > used to "clean up" the DataView once it is not referenced and will
>> >> > > not
>> >> > > effect the number of connections to the database.
>> >> > >
>> >> > > A DataReader on the other hand always maintains a connection to
>> >> > > the
>> >> > database
>> >> > > and must be explicitly closed (Do not rely on garbage collection).
>> >> > >
>> >> > > Reason I am asking is that I have been give a heap of vb.net code
> and
>> >> > there
>> >> > > are numerous functions that return a DataReader (Causing all sorts
> of
>> >> SQL
>> >> > > Timeout headaches, maximum connections reached etc). To solve this
>> >> problem
>> >> > > in the quickest amount of time I am thinking of replacing the
>> >> DataReaders
>> >> > > with DataViews so I can explicitly close the Database connection.
>> >> > >
>> >> > > Any other ideas (I would model the database via business objects
> and
>> >> > return
>> >> > > these but I really do not have the time as we need a "quote" quick
>> > fix).
>> >> > >
>> >> > > Thanks
>> >> > > Mark
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Hi all, below is typically what one of the functions look like inside one of
the classes, note psuedocode only

======================
[vbcode]

Public Class getPerson

' Function returns person details
Public Function GetPersonDetails(PersonID as integer) as SqlDataReader
Dim oDataReader as SqlDataReader
Dim oConn as new SqlConnection("Connection string......")
' Connect to database, get rows and return datareader.....
return oDataReader
End Function

End Class

[/vbcode]

In the application the Person class is instantiated like so

[vbcode]
Dim oPerson as new getPerson
Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)
[/vbcode]

The problem is that throughout the application the code is never closing the
DataReader object. Also, all the datareader objects, connection objects are
created locally within each routine in the class and are therefore private.
The actual design of the application is very bad (No central database class
for example).

So, given that the DataReader is not being explicitly closed (hence the SQL
maximum connection reached errors etc) any suggestions about how I can
explicitly retrieve information and then close the datareader are welcome. I
really do not have time to do a major redesign of the application as well. I
know that I can use the CommandBehavior.CloseConnection argument when
executing the reader but this only closes the connection when the
SqlDataReader.Close() method is called. As per the original problem, the
close() method is never getting called.

Using the DataView in the above class I could just go

[vbcode]

Public Class getPerson

' Function returns person details
Public Function GetPersonDetails(PersonID as integer) as DataView
Dim oDataView as DataView
Dim oConn as new SqlConnection("Connection string......")
' Connect to database, get table and return dataview after closing the
connection......
oConn.Close()
return oDataView
End Class

[/vbcode]

The above piece of code closes the connection to the database and returns a
dataview instead of a datareader, thus closing the connection to the
database...

Ideas/ comments welcome
Thanks again
Mark


"Mark" <> wrote in message
news:chqgfk$ine$...
> Hi Marina, thanks, that is exactly what I thought. I cringed when I saw
all
> the DataReader return types, anyway, thanks again
>
> Cheers
> Mark
>
> "Marina" <> wrote in message
> news:OECuJ$...
> > DataReaders are meant for operations that involve reading all the data
> right
> > away, and closing the reader. This is because the reader maintains an
> open
> > connection to the database - a valuable resource. I am not surprised you
> are
> > seeing this problem.
> >
> > Now, a dataview, has nothing to do with database access itself. It's
> really
> > the datatable that can be used to place data into from the result of a
> > query. A dataview is another layer that can be put on top of that, to
> > provide a sorted or filtered view of the data in the underlying table.
> >
> > I definitely recommend you go ahead and eliminate the datareader.
> > Datareader should never be returned from functions - as this requires
the
> > consumer of the function to remember to close the data reader once it is
> no
> > longer needed. This isn't something anyone should rely on - not to
> mention,
> > you never know how long the consumer of the function will keep that
reader
> > open. It is no wonder you are experiencing all these problems.
> >
> >
> > "Mark" <> wrote in message
> > news:chqfnl$i4j$...
> > > Hi all, quick question [Please correct me where I am wong ], a
> DataView
> > is
> > > memory resident "view" of data in a data table therefore once
populated
> > you
> > > can close the connection to the database. Garbage collection can then
be
> > > used to "clean up" the DataView once it is not referenced and will not
> > > effect the number of connections to the database.
> > >
> > > A DataReader on the other hand always maintains a connection to the
> > database
> > > and must be explicitly closed (Do not rely on garbage collection).
> > >
> > > Reason I am asking is that I have been give a heap of vb.net code and
> > there
> > > are numerous functions that return a DataReader (Causing all sorts of
> SQL
> > > Timeout headaches, maximum connections reached etc). To solve this
> problem
> > > in the quickest amount of time I am thinking of replacing the
> DataReaders
> > > with DataViews so I can explicitly close the Database connection.
> > >
> > > Any other ideas (I would model the database via business objects and
> > return
> > > these but I really do not have the time as we need a "quote" quick
fix).
> > >
> > > Thanks
> > > Mark
> > >
> > >
> >
> >
>
>

Hi Greg, thanks for that information. I'll give it a try
Thanks again
Mark

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:...
> Mark, here is some code I just grabbed from IBS (IBuySpy) (they are ALL
like
> this):
>
> Public Function GetUsers() As SqlDataReader
>
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
> Dim myCommand As New SqlCommand("GetUsers", myConnection)
>
> ' Mark the Command as a SPROC
> myCommand.CommandType = CommandType.StoredProcedure
>
> ' Open the database connection and execute the command
> myConnection.Open()
> Dim dr As SqlDataReader =
> myCommand.ExecuteReader(CommandBehavior.CloseConne ction)
>
> ' Return the datareader
> Return dr
>
> End Function
>
>
>
> ' bind all portal users to dropdownlist
> allUsers.DataSource = roles.GetUsers()
> allUsers.DataBind()
>
>
> Notice that it doesn't appear to close the datareader that is returned
from
> GetUsers. That is simply because the databind closes automagically when it
> is finished binding. (I'm not making that up!) Then, when the dr is closed
> the connection is closed because of the command behavior.
>
> In your example, I would add the CommandBehavior.CloseConnection inside
the
> function.
>
> Then in the code you showed
> Dim oPerson as new getPerson
> Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)
>
> ' do something here with oDR
>
> oDR.Close()
>
> HTH,
> Greg
>
>
> "Mark" <> wrote in message
> news:chqo6l$o64$...
> > Hi all, below is typically what one of the functions look like inside
one
> > of
> > the classes, note psuedocode only
> >
> > ======================
> > [vbcode]
> >
> > Public Class getPerson
> >
> > ' Function returns person details
> > Public Function GetPersonDetails(PersonID as integer) as SqlDataReader
> > Dim oDataReader as SqlDataReader
> > Dim oConn as new SqlConnection("Connection string......")
> > ' Connect to database, get rows and return datareader.....
> > return oDataReader
> > End Function
> >
> > End Class
> >
> > [/vbcode]
> >
> > In the application the Person class is instantiated like so
> >
> > [vbcode]
> > Dim oPerson as new getPerson
> > Dim oDR as SqlDataReader = oPerson.GetPersonDetails(12)
> > [/vbcode]
> >
> > The problem is that throughout the application the code is never closing
> > the
> > DataReader object. Also, all the datareader objects, connection objects
> > are
> > created locally within each routine in the class and are therefore
> > private.
> > The actual design of the application is very bad (No central database
> > class
> > for example).
> >
> > So, given that the DataReader is not being explicitly closed (hence the
> > SQL
> > maximum connection reached errors etc) any suggestions about how I can
> > explicitly retrieve information and then close the datareader are
welcome.
> > I
> > really do not have time to do a major redesign of the application as
well.
> > I
> > know that I can use the CommandBehavior.CloseConnection argument when
> > executing the reader but this only closes the connection when the
> > SqlDataReader.Close() method is called. As per the original problem, the
> > close() method is never getting called.
> >
> > Using the DataView in the above class I could just go
> >
> > [vbcode]
> >
> > Public Class getPerson
> >
> > ' Function returns person details
> > Public Function GetPersonDetails(PersonID as integer) as DataView
> > Dim oDataView as DataView
> > Dim oConn as new SqlConnection("Connection string......")
> > ' Connect to database, get table and return dataview after closing
the
> > connection......
> > oConn.Close()
> > return oDataView
> > End Class
> >
> > [/vbcode]
> >
> > The above piece of code closes the connection to the database and
returns
> > a
> > dataview instead of a datareader, thus closing the connection to the
> > database...
> >
> > Ideas/ comments welcome
> > Thanks again
> > Mark
> >
> >
> > "Mark" <> wrote in message
> > news:chqgfk$ine$...
> >> Hi Marina, thanks, that is exactly what I thought. I cringed when I saw
> > all
> >> the DataReader return types, anyway, thanks again
> >>
> >> Cheers
> >> Mark
> >>
> >> "Marina" <> wrote in message
> >> news:OECuJ$...
> >> > DataReaders are meant for operations that involve reading all the
data
> >> right
> >> > away, and closing the reader. This is because the reader maintains
an
> >> open
> >> > connection to the database - a valuable resource. I am not surprised
> >> > you
> >> are
> >> > seeing this problem.
> >> >
> >> > Now, a dataview, has nothing to do with database access itself. It's
> >> really
> >> > the datatable that can be used to place data into from the result of
a
> >> > query. A dataview is another layer that can be put on top of that, to
> >> > provide a sorted or filtered view of the data in the underlying
table.
> >> >
> >> > I definitely recommend you go ahead and eliminate the datareader.
> >> > Datareader should never be returned from functions - as this requires
> > the
> >> > consumer of the function to remember to close the data reader once it
> >> > is
> >> no
> >> > longer needed. This isn't something anyone should rely on - not to
> >> mention,
> >> > you never know how long the consumer of the function will keep that
> > reader
> >> > open. It is no wonder you are experiencing all these problems.
> >> >
> >> >
> >> > "Mark" <> wrote in message
> >> > news:chqfnl$i4j$...
> >> > > Hi all, quick question [Please correct me where I am wong ], a
> >> DataView
> >> > is
> >> > > memory resident "view" of data in a data table therefore once
> > populated
> >> > you
> >> > > can close the connection to the database. Garbage collection can
then
> > be
> >> > > used to "clean up" the DataView once it is not referenced and will
> >> > > not
> >> > > effect the number of connections to the database.
> >> > >
> >> > > A DataReader on the other hand always maintains a connection to the
> >> > database
> >> > > and must be explicitly closed (Do not rely on garbage collection).
> >> > >
> >> > > Reason I am asking is that I have been give a heap of vb.net code
and
> >> > there
> >> > > are numerous functions that return a DataReader (Causing all sorts
of
> >> SQL
> >> > > Timeout headaches, maximum connections reached etc). To solve this
> >> problem
> >> > > in the quickest amount of time I am thinking of replacing the
> >> DataReaders
> >> > > with DataViews so I can explicitly close the Database connection.
> >> > >
> >> > > Any other ideas (I would model the database via business objects
and
> >> > return
> >> > > these but I really do not have the time as we need a "quote" quick
> > fix).
> >> > >
> >> > > Thanks
> >> > > Mark
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>