DataView vs DataReader

[Marina]

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.

 

[Frank Mamone]

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

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.

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

 

[Greg Burns]

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

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.

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]

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]

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

Cheers
Mark

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.

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

 

[Greg Burns]

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("connectionString"))
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.CloseConnection)

' 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

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

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

Cheers
Mark

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.


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

 

[Greg Burns]

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

Greg

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

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

Cheers
Mark

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.


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

 

[Greg Burns]

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

Hi Greg, actually, that is the correct time here I'm in New Zealand and
right now it's lunchtime on Friday

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

Greg

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


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

Cheers
Mark

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.


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]

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

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

Cheers
Mark

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.

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]

Hi Greg, thanks for that information. I'll give it a try
Thanks again
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("connectionString"))
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.CloseConnection)

' 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

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

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

Cheers
Mark

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.


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]

Hi Greg, actually, that is the correct time here I'm in New Zealand and
right now it's lunchtime on Friday

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

Greg

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

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

Cheers
Mark

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.


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

 

[Marina]

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.

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

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.

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

 

[Greg Burns]

ACK

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

Greg

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.

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

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.


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

 

[Kevin Spencer]

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

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.

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

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.


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

 

[Marina]

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.

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

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.

behind
the requires
the

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.


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

 

[Marina]

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

ACK

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

Greg

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.

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


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.


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

 

[Kevin Spencer]

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

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.

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

pool speak

maintains

an surprised
you

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.


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

 

[Cor Ligthert]

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/d...ystemdatasqlclientsqldatareaderclasstopic.asp

I hope this helps?

Cor

 

[Kevin Spencer]

Helps what?

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

 

[Cor Ligthert]

Useless to tell you.