Link to home
Start Free TrialLog in
Avatar of kdeutsch
kdeutschFlag for United States of America

asked on

Problems with open dataReader Connections

Just lateley on our network we are starting to get errors that have to do with open datareaders which we really do not use we use sqldataAdapters.  So the following are the 2 ways we get data, but letely we are getting a lot of errors of them being open.  We have never had these problems up till a month ago and nothing we have done has changed.  I guess what I am asking is this the correct way to open and close our connections.  We use the getdata to do all crud functions and the reader to fill all of the controls on a page.

 Public Shared Function getData(ByVal sqlString As String) As DataTable
        Dim cmd As New SqlCommand(sqlString, conn)
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter

        da.SelectCommand = cmd
        da.Fill(ds)
        conn.Close()

        getData = ds.Tables(0)
    End Function

Open in new window




 Public Shared Function getReader(ByVal sqlstring As String) As DataTable
        Dim cmd As New SqlCommand(sqlstring, Conn)
        Dim dt As New DataTable
        Conn.Open()
        Dim dr As SqlDataReader = Nothing
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.HasRows Then
            dt.Load(dr)
        End If
        Return dt
    End Function

Open in new window



This is a typical error we are getting.

The connection was not closed. The connection's current state is open.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Source Error:

Line 39:         Dim cmd As New SqlCommand(sqlstring, Conn)
Line 40:         Dim dt As New DataTable
Line 41:         Conn.Open()
Line 42:         Dim dr As SqlDataReader = Nothing
Line 43:         dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Source File: E:\Applications\ReadinessTracker\App_Code\ReadinessCommon.vb    Line: 41

Stack Trace:

[InvalidOperationException: The connection was not closed. The connection's current state is open.]
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +14
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +83
   System.Data.SqlClient.SqlConnection.Open() +96
Avatar of jay813
jay813

How long should the connection to the database remain open?  From what I have learned, It depends, but in general, you should open the connection as late as you can, and close it again as soon as you can.  If I may quote for you as well:

Even if you choose to limit your connection length to the minimum time required to carry
out your database operations, the SQL Server provider might maintain the underlying connection
for a much longer time. That’s because the provider uses connection pooling—the
reuse of identical connection objects to reduce the time needed to establish new connections.
Creating a database connection is somewhat time-consuming because it involves the
overhead of network-level handshaking and security credentialing for each new connection
request. Connection pooling reduces these repetitive activities by keeping prior connections
around in case they are needed again by a new SqlConnection object.
The SQL Server provider maintains separate pools based on different connection strings and
other factors that make shared connections impossible. A single connection pool can include
more than one active connection, each waiting for your code to issue a new Open method
call on a SqlConnection object.

I think you should be doing:

1) open connection
2) do something with DB
3) close connection
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kdeutsch

ASKER

CodeCruiser,

Ok, I see what you are saying, we have used the getData statment on in our programs forever and jjust in last month is when it started squaking about open dtareader connections.  We just built the getReader so that we could fill things quicker verus getdata statement which we use for all insert, update and delete.

So how come we dont need to close the getdata but dispose of it?

 Private Shared Conn As New SqlConnection(ConfigurationManager.AppSettings("DevConnection"))

    Public Shared Function getData(ByVal sqlString As String) As DataTable
        Dim cmd As New SqlCommand(sqlString, Conn)
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter

        da.SelectCommand = cmd
        da.Fill(ds)
        da.Dispose()

        getData = ds.Tables(0)
    End Function
@jay813

Who (or what) are you quoting?
Calling the connection’s Dispose method will automatically Close (if you haven’t closed). Calling Close will not automatically call Dispose.

@kaufmed

I am quoting from my Microsoft ADO.net 4 Book:
http://www.amazon.com/Microsoft-ADO-NET-Step/dp/0735638888/ref=sr_1_1?ie=UTF8&qid=1371212466&sr=8-1&keywords=ado.net+4
@jay813
Calling Close will not automatically call Dispose.
That is inconsequential. From http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx:

Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Kaufmed

Doesnt the dispose do some memory operations that close does not do?

For SqlConnection, from the perspective of the connection itself, they are equivalent. According to Reflector, Dispose() calls Close() as well as doing a few additional memory-freeing operations -- mostly by setting members equal to null.

For Stream, they actually are equivalent. Stream.Dispose() simply calls Close().
@jay813
The point I am making is that it doesn't matter which one you call, so long as you call one or the other. Personally, I use using blocks for the implicit call to Dispose.
@kaufmed

Thanks for the great points and advice!
Thanks all