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 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
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
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
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
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
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 > > > > > >
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 > > > > > >
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 > > > > > >
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 > > > > > >
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 >> > > >> > > >> > >> > >> >> > >
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
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 >> >> > >
> 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 > >
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 >> >> > >
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 >> >> > >
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 > >> > >> > > > > > >
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
Useless to tell you. Helps what?
-- Kevin Spencer .Net Developer Microsoft MVP I get paid good money to solve puzzles for a living
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: Peter Yin |
last post by:
Hi All,
Which way is the best way to convert a datareader to a
dataview?
Thanks,
Peter
|
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. ...
|
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...
|
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 =...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
| |