[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