473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DataView vs DataReader

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
20 5501
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
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
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
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
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
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
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
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
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
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
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
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
> 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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Al | last post by:
in order to implement sorting, paging in a datagrid, can I have the datasource of the datagrid a datareader or it must be a dataset then I create a dataview and make it a datasource of the...
3
by: Peter Yin | last post by:
Hi All, Which way is the best way to convert a datareader to a dataview? Thanks, Peter
12
by: Thomas Scheiderich | last post by:
I have 2 dropdowns that are exactly the same and I don't want to re-read for each. Is there a way to do something like below where I read the data, bind to depCity1 and then bind to destCity2. ...
7
by: Bart Schelkens | last post by:
Hi, I have 2 more questions : 1. Can I fill a datagrid by using a DataReader or does it have to be a DataSet or a DataView? 2. In my datagrid I need to display two images and one of does...
7
by: Arsalan | last post by:
I have a function which return datareader Public Shared Function ReturnDReader(ByVal query As String) As OleDbDataReader Dim Connection_String As String =...
7
by: Hardik Shah | last post by:
Hi, Generaly to populate combobox , I use following command :- Combobox1.datasource = dataset.tablename combobox1.displaymember = fieldname1 combobox1.valuemember = fieldname2 As I don't...
7
by: Brett Romero | last post by:
I have a dataset with one table, which has four columns. All are of type INT. I need to convert this dataset into a dataview so I can sort on the last three columns. I may sort one of the three...
4
by: Tedmond | last post by:
Dear all, How to code a Web Service function that returns either a DataTable or DataReader to the clients? Or I should use other alternative? Thanks for any help! Tedmond
3
by: Elmo Watson | last post by:
I previously had a project working, in which the Gridview was populated by a DataSet - then, with a DropDownlist in one of the columns, using the OnrowDataBound event, to populate the DDL with the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.