[dba-VB] Close DB Connection

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




More information about the dba-VB mailing list