Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problems with open dataReader Connections

Posted on 2013-06-13
11
Medium Priority
?
741 Views
Last Modified: 2013-06-14
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
0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 1

Expert Comment

by:jay813
ID: 39245773
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
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1600 total points
ID: 39247128
Your 2nd function (by the way, I don't understand why you need it) never closes datareader object which has to be done as the connection remains occupied other wise. So change it to

 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
        dr.Close
        Return dt
    End Function

Open in new window



Your first function never opens a connection but does close it. Where is it opened? That function should not be closed the connection if it did not open it. I would change it to

 Public Shared Function getData(ByVal sqlString As String) As DataTable
        Dim dt As New DataTable
        Dim da As New SqlDataAdapter(sqlString, "Connection String")

        da.Fill(dt)
        da.Dispose

        Return dt
    End Function

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 39247319
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39247347
@jay813

Who (or what) are you quoting?
0
 
LVL 1

Expert Comment

by:jay813
ID: 39247364
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
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39247397
@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.
0
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 400 total points
ID: 39247517
@kdeutsch

A DataAdapter handles the opening and closing of a connection for you. You don't have to worry about the connection at all when using a DataAdapter. You dispose of the DataAdapter to ensure that it releases all of the unmanaged resources (like the connection) it has acquired.

When you work directly with a connection, you need to explicitly (or implicitly via a using construct) call the Dispose (or as I referenced above the Close) method to release any unmanaged resources that the connection has acquired.
0
 
LVL 1

Expert Comment

by:jay813
ID: 39247566
@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().
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39247584
@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.
0
 
LVL 1

Expert Comment

by:jay813
ID: 39247643
@kaufmed

Thanks for the great points and advice!
0
 

Author Closing Comment

by:kdeutsch
ID: 39247821
Thanks all
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question