Database Connection questions.

[pradeep_TP]

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.clos e() 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.clos e(), 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.clos e() 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.clos e(), 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.publi c.dotnet.framew ork.adonet

I hope this helps,

Cor

[Mark R. Dawson]

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

method, and I do not use Connection.clos e() 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(SqlConnec tion connection = new SqlConnection() )
{
.......
}

or
SqlConnection connection = null;

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

3) When i m saying connection.clos e(), 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 connectionstrin g 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

"pradeep_TP " wrote:
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.clos e() 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.clos e(), 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

[pradeep_TP]

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.

"Mark R. Dawson" wrote:

1) If I have opened a connection to a database through Connection.open ()
method, and I do not use Connection.clos e() 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(SqlConnec tion connection = new SqlConnection() )
{
......
}

or
SqlConnection connection = null;

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

3) When i m saying connection.clos e(), 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 connectionstrin g 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

"pradeep_TP " wrote:

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.clos e() 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.clos e(), 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

[Scott Roberts]

"pradeep_TP " <pr*******@disc ussions.microso ft.com> wrote in message
news:B0******** *************** ***********@mic rosoft.com...

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.

Do you mean like this:

public void LeakConnection( )
{
SqlConnection _connection = new SqlConnection(" some_connection _string");
_connection.Ope n();
}

I believe that when "_connectio n" goes out of scope it is eligible for
garbage collection. The garbage collector automatically calls "Dispose()" on
the "_connectio n" 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
"_connectio n" yourself. Someone correct me if I'm wrong here. :)

[Mark R. Dawson]

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.

"Scott Roberts" wrote:

"pradeep_TP " <pr*******@disc ussions.microso ft.com> wrote in message
news:B0******** *************** ***********@mic rosoft.com...

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.

Do you mean like this:

public void LeakConnection( )
{
SqlConnection _connection = new SqlConnection(" some_connection _string");
_connection.Ope n();
}

I believe that when "_connectio n" goes out of scope it is eligible for
garbage collection. The garbage collector automatically calls "Dispose()" on
the "_connectio n" 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
"_connectio n" yourself. Someone correct me if I'm wrong here. :)

[Mark R. Dawson]

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.Ope n();
}

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
"pradeep_TP " wrote:

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.

"Mark R. Dawson" wrote:

1) If I have opened a connection to a database through Connection.open ()
method, and I do not use Connection.clos e() 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(SqlConnec tion connection = new SqlConnection() )
{
......
}

or
SqlConnection connection = null;

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

3) When i m saying connection.clos e(), 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 connectionstrin g 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

"pradeep_TP " wrote:

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.clos e() 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.clos e(), 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

[Scott Roberts]

"Mark R. Dawson" <Ma*********@di scussions.micro soft.com> wrote in message
news:19******** *************** ***********@mic rosoft.com...

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.

[pradeep_TP]

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 :)
"Mark R. Dawson" wrote:

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.Ope n();
}

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
"pradeep_TP " wrote:

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.

"Mark R. Dawson" wrote:

> 1) If I have opened a connection to a database through Connection.open ()
> method, and I do not use Connection.clos e() 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(SqlConnec tion connection = new SqlConnection() )
{
......
}

or
SqlConnection connection = null;

try
{
connection = new SqlConnection() ;
....
}
finally
{
if(connection != null)
{
connection.Clos e();
}
}
> 3) When i m saying connection.clos e(), 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 connectionstrin g 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

"pradeep_TP " wrote:

> 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.clos e() 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.clos e(), 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

[Mark R. Dawson]

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.

"pradeep_TP " wrote:

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 :)
"Mark R. Dawson" wrote:

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.Ope n();
}

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
"pradeep_TP " wrote:

> 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.

"Mark R. Dawson" wrote:

> > 1) If I have opened a connection to a database through Connection.open ()
> > method, and I do not use Connection.clos e() 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(SqlConnec tion connection = new SqlConnection() )
> {
> ......
> }
>
> or
>
>
> SqlConnection connection = null;
>
> try
> {
> connection = new SqlConnection() ;
> ....
> }
> finally
> {
> if(connection != null)
> {
> connection.Clos e();
> }
> }
>
>
> > 3) When i m saying connection.clos e(), 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 connectionstrin g 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
>
>
>
> "pradeep_TP " wrote:
>
> > 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.clos e() 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.clos e(), 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