Solved

Problems with open dataReader Connections

Posted on 2013-06-13
11
693 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
  • 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 400 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
 
LVL 74

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 74

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 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 100 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 74

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now