Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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
0
kdeutsch
Asked:
kdeutsch
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
jay813Commented:
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
 
CodeCruiserCommented:
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
 
kdeutschAuthor Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
käµfm³d 👽Commented:
@jay813

Who (or what) are you quoting?
0
 
jay813Commented:
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
 
käµfm³d 👽Commented:
@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
 
käµfm³d 👽Commented:
@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
 
jay813Commented:
@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
 
käµfm³d 👽Commented:
@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
 
jay813Commented:
@kaufmed

Thanks for the great points and advice!
0
 
kdeutschAuthor Commented:
Thanks all
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now