Solved

Problems with open dataReader Connections

Posted on 2013-06-13
11
723 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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