Database Connection questions.

[Guest]

Hi all,

I have a few questions that I have been wanting to ask for long. These are
all related to ADO.net and specifically to conenction to database.

1) If I have opened a connection to a database through Connection.open()
method, and I do not use Connection.close() method, will garbage collector
collect the connection object just because i am not using it any more.

2) I am using a dataset, in which i make some modifications to the data and
submit the modified data back to the server. Will the connection be opened
again to the database server, because dataset is a connectionless object.

3) When i m saying connection.close(), is the connection actually getting
close or does the connection information stays in some memory area, only
waiting for some other connection.open() method to call it.

thanks in advance
pradeep T.P

 

[Cor Ligthert [MVP]]

Pradeep.

I have a few questions that I have been wanting to ask for long. These are
all related to ADO.net and specifically to conenction to database.

1) If I have opened a connection to a database through Connection.open()
method, and I do not use Connection.close() method, will garbage collector
collect the connection object just because i am not using it any more.

The connection object will live as long as there is a reference to or from
it. See later answers as well about this.

2) I am using a dataset, in which i make some modifications to the data
and
submit the modified data back to the server. Will the connection be opened
again to the database server, because dataset is a connectionless object.

The dataset cannot sent to a server. The dataadapter is sending individual
rows to the server. One of the operations from a dataadapter is to open and
close the connection that is in his connection properties if that is/was
not already open when it started.

3) When i m saying connection.close(), is the connection actually getting
close or does the connection information stays in some memory area, only
waiting for some other connection.open() method to call it.

The connection closes and when the connection object is declared globaly (or
by instance a dataadapter which has a reference to it) than the object
stays in memory.

By the way, this are typical questions for the newsgroup.

Microsoft.public.dotnet.framework.adonet

I hope this helps,

Cor

 

[Guest]

1) If I have opened a connection to a database through Connection.open()

method, and I do not use Connection.close() method, will garbage collector
collect the connection object just because i am not using it any more.

The garbage collector will only do this if you do not have any references to
the connection anywhere else in your program. Also the GC is
non-deterministic so the connection may lie open for a long time before the
GC runs. It is best to put your connection useage inside a using statement
that will automatically call dispose automatically (which internally calls
close) or always use a finally statement i.e.

using(SqlConnection connection = new SqlConnection())
{
.......
}

or


SqlConnection connection = null;

try
{
connection = new SqlConnection();
....
}
finally
{
if(connection != null)
{
connection.Close();
}
}

3) When i m saying connection.close(), is the connection actually getting
close or does the connection information stays in some memory area, only
waiting for some other connection.open() method to call it.

When you call Close on a connection or Dispose the underlying connection is
returned to the connection pool, the connection pool group connections
together based on the Connection String, so the next time you try to open a
connection with the same connectionstring you will get a connection in the
pool (if there is one available) very quickly without a performance hit.


Mark R Dawson
http://www.markdawson.org

 

[Guest]

The garbage collector will only do this if you do not have any references to
the connection anywhere else in your program. ...

Does "references" mean opened connection object remaining unclosed. What i
mean is that if after i open the connection object and does not close it for
long, will the GC consider this as unreferenced and try to collect the
object.

 

[Scott Roberts]

Does "references" mean opened connection object remaining unclosed. What i
mean is that if after i open the connection object and does not close it for
long, will the GC consider this as unreferenced and try to collect the
object.

Do you mean like this:

public void LeakConnection()
{
SqlConnection _connection = new SqlConnection("some_connection_string");
_connection.Open();
}

I believe that when "_connection" goes out of scope it is eligible for
garbage collection. The garbage collector automatically calls "Dispose()" on
the "_connection" object which will close the connection.

So the answer is yes, it will *eventually* get closed automatically. But it
would be much, much, much better to either close it yourself or dispose
"_connection" yourself. Someone correct me if I'm wrong here.

 

[Guest]

One point of clarification, the Gabage Collector will not call Dispose
directly, it does not know anything about the IDisposable interface. If you
want Dispose to be called by the GC then you have to make sure you call
Dispose in a Finalizer in the class which is what gets called by the GC when
the object is elligable for cleanup.

 

[Guest]

What I meant was that an object is only elligable for GC once there is
nothing referencing it i.e. you don't have any variables pointing to the
object, for example if you have:


class MyObject
{
private SqlConnection _connection = null;

public MyObject()
{
_connection = new SqlConnection("connection string");
_connection.Open();
}

public void DoSomething()
{
_connection = null;
}
}


The only way the connection object can become eligable for colllection by
the GC is for someone to call DoSomething() which breaks the link between the
_connection variable and the SqlConnection object. Now the SQL connection
object has nothing pointing to it, there is no way you can ever reference the
object again, it has effectively gone out of scope.

Or the instance of MyObject that gets created is no longer pointed to by any
variables then by transitive association ie a->b->c => a->c the _connection
object also has no way of being accessed.

Remember though that the GC is undeterministic and may not run for a long
time so your connection may remain open for a long time, therefore always
best to be very careful with closing connections.


Mark R Dawson
http://www.markdawson.org

 

[Scott Roberts]

One point of clarification, the Gabage Collector will not call Dispose
directly, it does not know anything about the IDisposable interface. If you
want Dispose to be called by the GC then you have to make sure you call
Dispose in a Finalizer in the class which is what gets called by the GC when
the object is elligable for cleanup.

Ah, yes. Thank you.

 

[Guest]

I now understood what mark said. I would also like to know when connection
object is collected by GC, will the "connection object" be given back to the
connection pool. The other day i went throug the MSDN site, there I got an
explanation about connection pooling. I said that, once the connection is
closed, the "connection object" is given back to the connection pool. so if
GC collects the unclosed connection, will the connection object be still
returned back to connection pool

thanks all for your help

 

[Guest]

Hi pradeep,
the GC will call the SQLConnections Finalizer which in turn calls Dispose
which will then internally close the connection sending it back to the
connection pool.

 

[Guest]

Thank you very much Mark, Scott and Cor for helping me through these doubts.
You guys are great !!!

 

[Guest]

Hi pradeep,
if you want to find out things like this in the future Reflector is a
great tool:
http://www.aisto.com/roeder/dotnet/

It allows you to see decompiled IL code so you can look inside the Microsoft
DLL's and see what they are doing. In your case if you look inside the
Dispose method of the SqlConnection class you will see the following code:


protected override void Dispose(bool disposing)
{
if (disposing)
{
switch (this._objectState)
{
case ConnectionState.Open:
{
this.Close();
break;
}
}
this._constr = null;
}
base.Dispose(disposing);
}

If you look inside close you will see how it deals with the connection pool.
It is a really useful tool.

Hope that helps
Mark R Dawson
http://www.markdawson.org