Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Wed Dec 1 08:39:51 CST 2010
Hi Vlad --
Yes, that's OK to call .Dispose() on every iteration - you're disposing
current instance.
When you just create a new instance by using the same object variable
previous instance isn't getting disposed: it's supposed to get disposed
later by Garbage Collector (GC).
I must note I have never used .Dispose() for OleDbCommand and all worked
well - checked with memory profiler as I noted here about yesterday.
But I didn't use OleDbCommand with parameters.
In your case it could also be that you use parameters and you use
OleDbDataReader instance reference to set parameter values.
My (wild) guess is that VS uses some optimization while translating your
code into IL, and that translation results in OleDbDataReader reference
"captured" by OleDbCommand reader.
Then you have many cycles of rdr.Read() and on every cycle a new instance of
OleDbCommand is created, and a previous one is left hanging with
OleDbDataReader instance referenced from parameters.
Then GC doesn't work well/promptly somehow.
If you write a function to get values from OleDbDatReader:
private function getFieldValue(ByRef rdr as OleDbDataReader, ByVal fldName
as string) as string
return rdr(fldName).ToString()
end function
your issue might get solved "automagically". But that is just a guess.
I'd also note that the way you set parameters doesn't look good enough from
here - I mean internationalization issues, which may arise if you'll use you
program with different formats of day/time. (My above function sample isn't
good from internationalization point of view too).
If you drop using parameterized OleDbCommand and return to plain dumb
string.Format(....) to make Insert sql command text it would be easier to
handle internationalization issues IMO, and also you can just create
OleDbCommand once and then set its connection property before your
While(rdr.read()) cycle, and set CommandText property on every iteration,
and then calling .Dispose within every cycle will not be needed as you'll
use the same OleDbCommand instance, and I suppose calling .Dispose for
OleDbCommand after End While will not be needed at all...
Thank you.
--
Shamil
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: 1 ??????? 2010 ?. 16:23
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Close DB Connection
Hi Shamil,
I've gotten to the bottom of it. I needed to dispose of the
OleDb.OleDbCommand. I simply added cmd2.Dispose within the While Statement
and all is good. I doesn't seem to have had any unusual performance hit, but
I do find it strange that you need to dispose of it at every iteration. Do
you think that's a problem?
Code:
While rdr.Read()
Dim sql2 As String = String.Format("INSERT INTO
tblMainData (HeaderID, UnitID, RecDate, RecTime, RecDateTime, " & _
"Celsius) values
(@HeaderID, at UnitID, at RecDate, at RecTime, at RecDateTime, at Celsius)")
Dim cmd2 As New OleDb.OleDbCommand(sql2,
destinationConnection)
cmd2.Parameters.Add("@HeaderID",
OleDbType.Integer).Value = gHeaderID
cmd2.Parameters.Add("@UnitID",
OleDbType.Integer).Value = gUnitID
cmd2.Parameters.Add("@RecDate",
OleDbType.Date).Value = rdr("RecDate").ToString()
cmd2.Parameters.Add("@RecTime",
OleDbType.Date).Value = rdr("RecDate").ToString()
cmd2.Parameters.Add("@RecDateTime",
OleDbType.Date).Value = rdr("RecDate").ToString()
cmd2.Parameters.Add("@Celsius",
OleDbType.Double).Value = rdr(strUniColumnName).ToString()
cmd2.ExecuteNonQuery()
cmd2.Dispose()
End While
Many thanks for all your guidance...
Vlad
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, 1 December 2010 5:20 PM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Close DB Connection
Hi Vlad --
<<<
Thanks for that, but it didn't work
>>>
I did use
rdr.Dispose()
after rdr.Close() in my sample code .
Try it...
It will work.
Sorry, I didn't use .Dispose() in my first sample in AccessD - but that has
had "quick&dirty disclaimer" as I noted that time.
Thank you.
--
Shamil
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: 1 ??????? 2010 ?. 6:50
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Close DB Connection
Hi Shamil,
Thanks for that, but it didn't work. I've traced it down and finally found
the he offending code which is below. Can you see anywhere that I can set
the connection to nothing? Like I said, you can't set the connection to
nothing when it's within a Using Statement. Funnily enough, you can set the
OleDbDataReader to nothing though...
Public Sub AppendTempDataToMainTbl(ByVal strUniColumnName As String)
Dim sourceDbFullPath As String = App_Path() & "TempImport.mdb"
Dim destionationDbFullPath As String = GetIniSetting("DBPath",
"SystemSettings")
Try
Using sourceConnection As New
OleDbConnection(GetSourceConnectionString)
sourceConnection.Open()
Dim sql1 As String = "SELECT * FROM tblTempImport WHERE
TempID > 10"
Dim cmd1 As New OleDbCommand(sql1, sourceConnection)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader()
Using destinationConnection As New
OleDbConnection(GetDestConnectionString)
destinationConnection.Open()
While rdr.Read()
Dim sql2 As String = String.Format("INSERT INTO
tblMainData (HeaderID, UnitID, RecDate, RecTime, RecDateTime, " & _
"Celsius) values
(@HeaderID, at UnitID, at RecDate, at RecTime, at RecDateTime, at Celsius)")
Dim cmd2 As New OleDbCommand(sql2,
destinationConnection)
cmd2.Parameters.Add("@HeaderID",
OleDbType.Integer).Value = gHeaderID
cmd2.Parameters.Add("@UnitID",
OleDbType.Integer).Value = gUnitID
cmd2.Parameters.Add("@RecDate",
OleDbType.Date).Value = rdr("RecDate").ToString()
cmd2.Parameters.Add("@RecTime",
OleDbType.Date).Value = rdr("RecDate").ToString()
cmd2.Parameters.Add("@RecDateTime",
OleDbType.Date).Value = rdr("RecDate").ToString()
cmd2.Parameters.Add("@Celsius",
OleDbType.Double).Value = rdr(strUniColumnName).ToString()
cmd2.ExecuteNonQuery()
End While
destinationConnection.Close()
destinationConnection.Dispose()
If destinationConnection.State.ToString IsNot Nothing
Then
destinationConnection.Close()
destinationConnection.Dispose()
'destinationConnection = Nothing
End If
End Using
rdr.Close()
rdr = Nothing
sourceConnection.Close()
sourceConnection.Dispose()
'sourceConnection = Nothing
If rdr IsNot Nothing Then
rdr.Close()
rdr.Dispose()
rdr = Nothing
End If
End Using
Finally
End Try
End Sub
Thnaks
Vlad
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, 1 December 2010 12:31 AM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Close DB Connection
Hello Vlad --
Non-closed OleDbDataReader can keep connection objects alive (not collected
by GC) - you can use the following coding style to have OleDbDataReader
always closed and connection objects released automatically:
Public Shared Sub Run()
Dim rdr As OleDbDataReader = Nothing
Dim sourceDbFullPath As String = "c:\temp\sourceDb.mdb"
Dim destionationDbFullPath As String = "c:\temp\targetDb.mdb"
Try
Using sourceConnection As New
OleDbConnection(MakeAccDdbConnectionStr(sourceDbFullPath))
sourceConnection.Open()
Dim sql1 As String = "select [SourceField] from
[SourceTable]"
Dim cmd1 As New OleDbCommand(sql1, sourceConnection)
rdr = cmd1.ExecuteReader()
Using destinationConnection As New
OleDbConnection(MakeAccDdbConnectionStr(destionationDbFullPath))
destinationConnection.Open()
While rdr.Read()
Dim sql2 As String =
String.Format("insert into [MyTable](MyField) values ('{0}')",
rdr("SourceField").ToString())
Dim cmd2 As New OleDbCommand(sql2,
destinationConnection)
cmd2.ExecuteNonQuery()
End While
End Using
rdr.Close()
rdr.Dispose()
rdr = Nothing
End Using
Finally
If rdr IsNot Nothing Then
rdr.Close()
rdr.Dispose()
End If
End Try
End Sub
.NET Memory Profiler (http://memprofiler.com/) is a great tool, which helped
me to solve all the memory leakage issues in a large VS2008 solution having
about 40 projects.
Thank you.
--
Shamil
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: 30 ?????? 2010 ?. 16:07
To: 'dba-vb'
Subject: [dba-VB] Close DB Connection
Hi Everyone,
I've got this issue with my app where the app keeps it's connection to the
Access DB open even after the application has finished it's task. I can't
figure out for the life of me where I've missed closing it in the various
Functions and Sub Routines.
I've ensured that cn.Close and cn = Nothing, has been included in all the
code where the DB is opened. The only place I can't include cn = Nothing is
when the connection is encapsulated within a Using Statement where for some
reason this is not allowed. Could this be the problem? If so, how can I
release the connection?
Also, is there any way of finding out which bit of code is keeping the
connection open?
Many Thanks
Vlad
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com