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

DataView vs DataReader

P: n/a
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
Nov 18 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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

Nov 18 '05 #2

P: n/a
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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


Nov 18 '05 #3

P: n/a
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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


Nov 18 '05 #4

P: n/a
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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


Nov 18 '05 #5

P: n/a
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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



Nov 18 '05 #6

P: n/a
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
> > 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
> >
> >
>
>



Nov 18 '05 #7

P: n/a
And for gosh sakes, set your clock correctly or people will start ignoring
you.

Greg

"Mark" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
> > 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
> >
> >
>
>



Nov 18 '05 #8

P: n/a
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:Ok**************@TK2MSFTNGP14.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
> > 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
> >
> >
>
>



Nov 18 '05 #9

P: n/a
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:eJ**************@TK2MSFTNGP15.phx.gbl...
And for gosh sakes, set your clock correctly or people will start ignoring
you.

Greg

"Mark" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
> > 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
> >
> >
>
>



Nov 18 '05 #10

P: n/a
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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:eJ**************@TK2MSFTNGP15.phx.gbl...
And for gosh sakes, set your clock correctly or people will start
ignoring
you.

Greg

"Mark" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
>> 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" <so*****@nospam.com> wrote in message
>> news:OE*************@TK2MSFTNGP09.phx.gbl...
>> > 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" <ma**@Z-Zvolution.nZt> wrote in message
>> > news:ch**********@lust.ihug.co.nz...
>> > > 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
>> > >
>> > >
>> >
>> >
>>
>>
>
>



Nov 18 '05 #11

P: n/a
I don't think a reusable component, should ever return a datareader to its
consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain
close it.

The component should open and close its own connection - so a connection
leak should never come from that component. It should then return the data
in a datatable or another data structure, and the consumer can then do with
it whatever is necessary.

Datareader can be more efficient, etc, etc - but an architecture component
should be careful or returning them, as again, this can often be a problem.
You basically have to make sure that developers remember to close the
readers they get - because if not, you will quickly get a connection pool is
out of available connections exception. Then you have to go track down which
part of the code forgot to close the datareader. And believe me, I speak of
this from experience.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:ul**************@TK2MSFTNGP09.phx.gbl...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
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



Nov 18 '05 #12

P: n/a
ACK

But this still goes against most sources that I've seen.

Greg
"Marina" <so*****@nospam.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl...
I don't think a reusable component, should ever return a datareader to its
consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain
close it.

The component should open and close its own connection - so a connection
leak should never come from that component. It should then return the data
in a datatable or another data structure, and the consumer can then do
with
it whatever is necessary.

Datareader can be more efficient, etc, etc - but an architecture component
should be careful or returning them, as again, this can often be a
problem.
You basically have to make sure that developers remember to close the
readers they get - because if not, you will quickly get a connection pool
is
out of available connections exception. Then you have to go track down
which
part of the code forgot to close the datareader. And believe me, I speak
of
this from experience.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:ul**************@TK2MSFTNGP09.phx.gbl...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
>> 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
>>
>>
>
>



Nov 18 '05 #13

P: n/a
> I don't think a reusable component, should ever return a datareader to its
consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain
close it.
Hmm, isn't a DataReader a reusable component? Maybe Microsoft exposed a
component that they shouldn't have? Or maybe they know something you don't?

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Marina" <so*****@nospam.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl... I don't think a reusable component, should ever return a datareader to its
consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain
close it.

The component should open and close its own connection - so a connection
leak should never come from that component. It should then return the data
in a datatable or another data structure, and the consumer can then do with it whatever is necessary.

Datareader can be more efficient, etc, etc - but an architecture component
should be careful or returning them, as again, this can often be a problem. You basically have to make sure that developers remember to close the
readers they get - because if not, you will quickly get a connection pool is out of available connections exception. Then you have to go track down which part of the code forgot to close the datareader. And believe me, I speak of this from experience.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:ul**************@TK2MSFTNGP09.phx.gbl...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
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" <ma**@Z-Zvolution.nZt> wrote in message
news:ch**********@lust.ihug.co.nz...
> 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
>
>



Nov 18 '05 #14

P: n/a
I was talking a data access layer component. There's really no need to make
rude and sarcastic comments, I think it should have been obvious what I
meant.

"Kevin Spencer" <ks******@takempis.com> wrote in message
news:OM**************@TK2MSFTNGP09.phx.gbl...
I don't think a reusable component, should ever return a datareader to its
consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain close it.
Hmm, isn't a DataReader a reusable component? Maybe Microsoft exposed a
component that they shouldn't have? Or maybe they know something you

don't?
--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Marina" <so*****@nospam.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl...
I don't think a reusable component, should ever return a datareader to its consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain close it.

The component should open and close its own connection - so a connection
leak should never come from that component. It should then return the data in a datatable or another data structure, and the consumer can then do with
it whatever is necessary.

Datareader can be more efficient, etc, etc - but an architecture component should be careful or returning them, as again, this can often be a

problem.
You basically have to make sure that developers remember to close the
readers they get - because if not, you will quickly get a connection pool is
out of available connections exception. Then you have to go track down which
part of the code forgot to close the datareader. And believe me, I

speak of
this from experience.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:ul**************@TK2MSFTNGP09.phx.gbl...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
>> 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
>>
>>
>
>



Nov 18 '05 #15

P: n/a
I don't know what these sources are doing. It all depends on the purpose and
design of a system/component.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:O7**************@TK2MSFTNGP12.phx.gbl...
ACK

But this still goes against most sources that I've seen.

Greg
"Marina" <so*****@nospam.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl...
I don't think a reusable component, should ever return a datareader to its consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain close it.

The component should open and close its own connection - so a connection
leak should never come from that component. It should then return the data in a datatable or another data structure, and the consumer can then do
with
it whatever is necessary.

Datareader can be more efficient, etc, etc - but an architecture component should be careful or returning them, as again, this can often be a
problem.
You basically have to make sure that developers remember to close the
readers they get - because if not, you will quickly get a connection pool is
out of available connections exception. Then you have to go track down
which
part of the code forgot to close the datareader. And believe me, I speak of
this from experience.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:ul**************@TK2MSFTNGP09.phx.gbl...
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" <so*****@nospam.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
> 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" <ma**@Z-Zvolution.nZt> wrote in message
> news:ch**********@lust.ihug.co.nz...
>> 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
>>
>>
>
>



Nov 18 '05 #16

P: n/a
Maybe you think you know what you meant. A DataReader IS a Data Access layer
component. And it is reusable.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Marina" <so*****@nospam.com> wrote in message
news:e6*************@TK2MSFTNGP12.phx.gbl...
I was talking a data access layer component. There's really no need to make rude and sarcastic comments, I think it should have been obvious what I
meant.

"Kevin Spencer" <ks******@takempis.com> wrote in message
news:OM**************@TK2MSFTNGP09.phx.gbl...
I don't think a reusable component, should ever return a datareader to its consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain close it.
Hmm, isn't a DataReader a reusable component? Maybe Microsoft exposed a
component that they shouldn't have? Or maybe they know something you

don't?

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Marina" <so*****@nospam.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl...
I don't think a reusable component, should ever return a datareader to its consumer. Because, again, you have to rely on the developer using your
component, to remember to close it in a timely fashion - or just to plain close it.

The component should open and close its own connection - so a connection leak should never come from that component. It should then return the data in a datatable or another data structure, and the consumer can then do

with
it whatever is necessary.

Datareader can be more efficient, etc, etc - but an architecture component should be careful or returning them, as again, this can often be a

problem.
You basically have to make sure that developers remember to close the
readers they get - because if not, you will quickly get a connection pool
is
out of available connections exception. Then you have to go track down

which
part of the code forgot to close the datareader. And believe me, I speak
of
this from experience.

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:ul**************@TK2MSFTNGP09.phx.gbl...
> 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" <so*****@nospam.com> wrote in message
> news:OE*************@TK2MSFTNGP09.phx.gbl...
> > 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" <ma**@Z-Zvolution.nZt> wrote in message
> > news:ch**********@lust.ihug.co.nz...
> >> 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
> >>
> >>
> >
> >
>
>



Nov 18 '05 #17

P: n/a
Kevin,

Mostly a quick look at MSDN helps,

SQLDataReader Class
Provides a means of reading a forward-only stream of rows from a SQL Server
database. This class cannot be inherited.

http://msdn.microsoft.com/library/de...classtopic.asp

I hope this helps?

Cor
Nov 18 '05 #18

P: n/a
Helps what?

--
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:#4*************@TK2MSFTNGP11.phx.gbl...
Kevin,

Mostly a quick look at MSDN helps,

SQLDataReader Class
Provides a means of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.

http://msdn.microsoft.com/library/de...classtopic.asp
I hope this helps?

Cor

Nov 18 '05 #19

P: n/a
Useless to tell you.
Helps what?

--
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

Nov 18 '05 #20

P: n/a
IOW, you don't know.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:eI**************@TK2MSFTNGP09.phx.gbl...
Useless to tell you.
Helps what?

--
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living


Nov 18 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.