kdeutsch
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.
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.InvalidOperationExc eption: 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(CommandB ehavior.Cl oseConnect ion)
Source File: E:\Applications\ReadinessT racker\App _Code\Read inessCommo n.vb Line: 41
Stack Trace:
[InvalidOperationException : The connection was not closed. The connection's current state is open.]
System.Data.ProviderBase.D bConnectio nInternal. TryOpenCon nection(Db Connection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +14
System.Data.SqlClient.SqlC onnection. TryOpen(Ta skCompleti onSource`1 retry) +83
System.Data.SqlClient.SqlC onnection. Open() +96
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
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
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.InvalidOperationExc
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(CommandB
Source File: E:\Applications\ReadinessT
Stack Trace:
[InvalidOperationException
System.Data.ProviderBase.D
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(Configuratio nManager.A ppSettings ("DevConne ction"))
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
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(Configuratio
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?
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
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Kaufmed
Doesnt the dispose do some memory operations that close does not do?
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.
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 for the great points and advice!
ASKER
Thanks all
I think you should be doing:
1) open connection
2) do something with DB
3) close connection