Problem: Database connections remain open

[Usman Jamil]

Hi

I've a class that creates a connection to a database, gets and loop on a
dataset given a query and then close the connection. When I use netstat
viewer to see if there is any connection open left, I always see that there
are 2 connections open and in "ESTABLISHED" state. Here is the piece of code
that I'm using, please tell where I'm doing it wrong. Since this class is
being used at many placed in my actual web based application that uses it
extensively, so the open connections grow alot, occupying most of the ports
of system and some time creating problem for other port based applications
like ftp.

Few things that I noted on debugging it line by line and monitoring from
netStats viewer.

1. On execution of m_con.Open, it shows 1 or sometimes 2 connections in
"Established" state. No idea why it shows 2 open connections, where it
should only be one.
2. If I only see one connection in established state after m_con.Open
statement, then the second connection in established state comes when i'm
looping over the dataset records. So at the end there are two connections
left open no matter what.
3. Disposing dataset, sqlcommand, sqladapter, or sqlconnection does'nt have
any effect on the state of the connections that are opened.
4. Somewhere on the net I read that the opened connections are not disposed
rather returned to a pool but in a non-established state to improve
performance, but the connection my application opens stay in "established"
state.
5. When I close my application, all the open and established connections
(two in my case) are deleted from the Netstat viewer utility, i.e. the
opened db sessions are closed on application exit.

Please check it and tell me where I'm making the mistake or how to release
the openend connection.

/////////////////////////////////////////////////////CODE//////////////////////////////////////////////////////////
string strSql="select * from mytable";

#region Open connection
SqlConnection m_con = null;
m_con = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=sa;");
m_con.Open();
#endregion

#region Create command
SqlCommand cmd = new SqlCommand(strSql,m_con);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.Text;
#endregion

#region Fill data set
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
#endregion

#region Loop over dataset
if(dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
string szField1 = reader["field1"].ToString();
string szField2 = reader["field2"].ToString();
}
}
#endregion

#region Dispose dataset
dataSet.Clear();
dataSet.Dispose();
#endregion

#region Dispose Adapter and command
adapter.Dispose();
cmd.Dispose();
#endregion

#region Dispose connection object
m_con.Close();
m_con.Dispose();
m_con = null;
#endregion

/////////////////////////////////////////////////////END OF
CODE//////////////////////////////////////////////////////////

 

[Kevin Spencer]

First, .Net uses Connection Pooling, so as long as you close your
Connections, you don't need to worry about it.

Second, ensure that you close your Connections, even if an Exception occurs,
by employing a "using" statement, or try/catch/finally.

Third, a DataSet is a *disconnected* set of data. There is no reason not to
close the Connection immediately after filling the DataSet.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
+

Hi

I've a class that creates a connection to a database, gets and loop on a
dataset given a query and then close the connection. When I use netstat
viewer to see if there is any connection open left, I always see that
there are 2 connections open and in "ESTABLISHED" state. Here is the piece
of code that I'm using, please tell where I'm doing it wrong. Since this
class is being used at many placed in my actual web based application that
uses it extensively, so the open connections grow alot, occupying most of
the ports of system and some time creating problem for other port based
applications like ftp.

Few things that I noted on debugging it line by line and monitoring from
netStats viewer.

1. On execution of m_con.Open, it shows 1 or sometimes 2 connections in
"Established" state. No idea why it shows 2 open connections, where it
should only be one.
2. If I only see one connection in established state after m_con.Open
statement, then the second connection in established state comes when i'm
looping over the dataset records. So at the end there are two connections
left open no matter what.
3. Disposing dataset, sqlcommand, sqladapter, or sqlconnection does'nt
have any effect on the state of the connections that are opened.
4. Somewhere on the net I read that the opened connections are not
disposed rather returned to a pool but in a non-established state to
improve performance, but the connection my application opens stay in
"established" state.
5. When I close my application, all the open and established connections
(two in my case) are deleted from the Netstat viewer utility, i.e. the
opened db sessions are closed on application exit.

Please check it and tell me where I'm making the mistake or how to release
the openend connection.

/////////////////////////////////////////////////////CODE//////////////////////////////////////////////////////////
string strSql="select * from mytable";

#region Open connection
SqlConnection m_con = null;
m_con = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=sa;");
m_con.Open();
#endregion

#region Create command
SqlCommand cmd = new SqlCommand(strSql,m_con);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.Text;
#endregion

#region Fill data set
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
#endregion

#region Loop over dataset
if(dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
string szField1 = reader["field1"].ToString();
string szField2 = reader["field2"].ToString();
}
}
#endregion

#region Dispose dataset
dataSet.Clear();
dataSet.Dispose();
#endregion

#region Dispose Adapter and command
adapter.Dispose();
cmd.Dispose();
#endregion

#region Dispose connection object
m_con.Close();
m_con.Dispose();
m_con = null;
#endregion

/////////////////////////////////////////////////////END OF
CODE//////////////////////////////////////////////////////////

 

[-pb-]

Hi

I've a class that creates a connection to a database, gets and loop on a
dataset given a query and then close the connection. When I use netstat
viewer to see if there is any connection open left, I always see that there
are 2 connections open and in "ESTABLISHED" state. Here is the piece of code
that I'm using, please tell where I'm doing it wrong. Since this class is
being used at many placed in my actual web based application that uses it
extensively, so the open connections grow alot, occupying most of the ports
of system and some time creating problem for other port based applications
like ftp.

Few things that I noted on debugging it line by line and monitoring from
netStats viewer.

1. On execution of m_con.Open, it shows 1 or sometimes 2 connections in
"Established" state. No idea why it shows 2 open connections, where it
should only be one.
2. If I only see one connection in established state after m_con.Open
statement, then the second connection in established state comes when i'm
looping over the dataset records. So at the end there are two connections
left open no matter what.
3. Disposing dataset, sqlcommand, sqladapter, or sqlconnection does'nt have
any effect on the state of the connections that are opened.
4. Somewhere on the net I read that the opened connections are not disposed
rather returned to a pool but in a non-established state to improve
performance, but the connection my application opens stay in "established"
state.
5. When I close my application, all the open and established connections
(two in my case) are deleted from the Netstat viewer utility, i.e. the
opened db sessions are closed on application exit.

Please check it and tell me where I'm making the mistake or how to release
the openend connection.

/////////////////////////////////////////////////////CODE//////////////////­////////////////////////////////////////
string strSql="select * from mytable";

#region Open connection
SqlConnection m_con = null;
m_con = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=sa;");
m_con.Open();
#endregion

#region Create command
SqlCommand cmd = new SqlCommand(strSql,m_con);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.Text;
#endregion

#region Fill data set
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
#endregion

#region Loop over dataset
if(dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
string szField1 = reader["field1"].ToString();
string szField2 = reader["field2"].ToString();
}
}
#endregion

#region Dispose dataset
dataSet.Clear();
dataSet.Dispose();
#endregion

#region Dispose Adapter and command
adapter.Dispose();
cmd.Dispose();
#endregion

#region Dispose connection object
m_con.Close();
m_con.Dispose();
m_con = null;
#endregion

/////////////////////////////////////////////////////END OF
CODE//////////////////////////////////////////////////////////

HI,

If you want then connection pooling can be disable by specifying
"pooling=false" in the connection string and then use either "using"
keyword for connection and adaptor object. I suggest to use the using
statement as it will call the dispose automatically even in case of
exception.

 

[Usman Jamil]

Hi Kevin

In my actuall application I am closing all the connections. The code is
between proper sections of try/catch statements and connection is being
closed in case of exceptions also. This piece of code is just a test code, I
wrote it just to seperate a single flow of execution of query. I have
debugged this test code and the Close statement does execute for the opened
connection, infact all the dispose,clear, close statements execute in this
test code application given the query and connection string correct. But
even when all these statements execute, I still am left with open
connections according to NetStat Viewer application. Since it shows me 2
open and established connection after one complete execution of this code.
That is my actual problem. As even if Dotnet does connection pooling, the
connection returned back to pool after a SqlConnection.Close() must be in a
TIME_WAIT state and not ESTABLISHED state. Hope I could explain to you what
my actual problem is, i.e. the state of connection stays established instead
of time_wait even after a close. Please note that these states are the ones
shown in Netstats.

Regards

Usman

First, .Net uses Connection Pooling, so as long as you close your
Connections, you don't need to worry about it.

Second, ensure that you close your Connections, even if an Exception
occurs, by employing a "using" statement, or try/catch/finally.

Third, a DataSet is a *disconnected* set of data. There is no reason not
to close the Connection immediately after filling the DataSet.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
+

Hi

I've a class that creates a connection to a database, gets and loop on a
dataset given a query and then close the connection. When I use netstat
viewer to see if there is any connection open left, I always see that
there are 2 connections open and in "ESTABLISHED" state. Here is the
piece of code that I'm using, please tell where I'm doing it wrong. Since
this class is being used at many placed in my actual web based
application that uses it extensively, so the open connections grow alot,
occupying most of the ports of system and some time creating problem for
other port based applications like ftp.

Few things that I noted on debugging it line by line and monitoring from
netStats viewer.

1. On execution of m_con.Open, it shows 1 or sometimes 2 connections in
"Established" state. No idea why it shows 2 open connections, where it
should only be one.
2. If I only see one connection in established state after m_con.Open
statement, then the second connection in established state comes when i'm
looping over the dataset records. So at the end there are two connections
left open no matter what.
3. Disposing dataset, sqlcommand, sqladapter, or sqlconnection does'nt
have any effect on the state of the connections that are opened.
4. Somewhere on the net I read that the opened connections are not
disposed rather returned to a pool but in a non-established state to
improve performance, but the connection my application opens stay in
"established" state.
5. When I close my application, all the open and established connections
(two in my case) are deleted from the Netstat viewer utility, i.e. the
opened db sessions are closed on application exit.

Please check it and tell me where I'm making the mistake or how to
release the openend connection.

/////////////////////////////////////////////////////CODE//////////////////////////////////////////////////////////
string strSql="select * from mytable";

#region Open connection
SqlConnection m_con = null;
m_con = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=sa;");
m_con.Open();
#endregion

#region Create command
SqlCommand cmd = new SqlCommand(strSql,m_con);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.Text;
#endregion

#region Fill data set
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
#endregion

#region Loop over dataset
if(dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
string szField1 = reader["field1"].ToString();
string szField2 = reader["field2"].ToString();
}
}
#endregion

#region Dispose dataset
dataSet.Clear();
dataSet.Dispose();
#endregion

#region Dispose Adapter and command
adapter.Dispose();
cmd.Dispose();
#endregion

#region Dispose connection object
m_con.Close();
m_con.Dispose();
m_con = null;
#endregion

/////////////////////////////////////////////////////END OF
CODE//////////////////////////////////////////////////////////

 

[Usman Jamil]

Hi

I'm actually using this code in a web based application, where many
concurrent connections are expected all the time. So setting the pooling to
false is just not an option I can afford, keeping in mind the performance
degradation I will get.

Hi

I've a class that creates a connection to a database, gets and loop on a
dataset given a query and then close the connection. When I use netstat
viewer to see if there is any connection open left, I always see that
there
are 2 connections open and in "ESTABLISHED" state. Here is the piece of
code
that I'm using, please tell where I'm doing it wrong. Since this class is
being used at many placed in my actual web based application that uses it
extensively, so the open connections grow alot, occupying most of the
ports
of system and some time creating problem for other port based applications
like ftp.

Few things that I noted on debugging it line by line and monitoring from
netStats viewer.

1. On execution of m_con.Open, it shows 1 or sometimes 2 connections in
"Established" state. No idea why it shows 2 open connections, where it
should only be one.
2. If I only see one connection in established state after m_con.Open
statement, then the second connection in established state comes when i'm
looping over the dataset records. So at the end there are two connections
left open no matter what.
3. Disposing dataset, sqlcommand, sqladapter, or sqlconnection does'nt
have
any effect on the state of the connections that are opened.
4. Somewhere on the net I read that the opened connections are not
disposed
rather returned to a pool but in a non-established state to improve
performance, but the connection my application opens stay in "established"
state.
5. When I close my application, all the open and established connections
(two in my case) are deleted from the Netstat viewer utility, i.e. the
opened db sessions are closed on application exit.

Please check it and tell me where I'm making the mistake or how to release
the openend connection.

/////////////////////////////////////////////////////CODE//////////////////­////////////////////////////////////////
string strSql="select * from mytable";

#region Open connection
SqlConnection m_con = null;
m_con = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=sa;");
m_con.Open();
#endregion

#region Create command
SqlCommand cmd = new SqlCommand(strSql,m_con);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.Text;
#endregion

#region Fill data set
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
#endregion

#region Loop over dataset
if(dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
string szField1 = reader["field1"].ToString();
string szField2 = reader["field2"].ToString();
}
}
#endregion

#region Dispose dataset
dataSet.Clear();
dataSet.Dispose();
#endregion

#region Dispose Adapter and command
adapter.Dispose();
cmd.Dispose();
#endregion

#region Dispose connection object
m_con.Close();
m_con.Dispose();
m_con = null;
#endregion

/////////////////////////////////////////////////////END OF
CODE//////////////////////////////////////////////////////////

HI,

If you want then connection pooling can be disable by specifying
"pooling=false" in the connection string and then use either "using"
keyword for connection and adaptor object. I suggest to use the using
statement as it will call the dispose automatically even in case of
exception.

 

[Kevin Spencer]

It's not a problem. Althogh I have not poked into the underlying network
operations that are performed by Connection Pooling, I have been using it
for years without any problems. Also, you mentioned that when your app
closed, the connections were cleaned up. What I suspect is a form of
"caching" is going on. The .Net platform gets a lot of performance boost by
avoiding repetetive operations that are costly. This is achieved by means of
a rather complex set of intelligent mechanisms like Connection Pooling that
monitor reusable objects that have been released without immediately
destroying them. A network database connection is expensive to establish,
particularly with regards to high-security database servers like SQL Server.
I imagine that if you were to watch these underlying connections long
enough, they would go away even without the application shutting down. In
any case, as I mentioned, from my experience, Connection Pooling works
beautifully in the .Net platform. The only exception is when Connection
classes are not properly closed and/or disposed. As long as the Connection
object is opened, your app has not relinquished control of it to the
platform.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

Hi Kevin

In my actuall application I am closing all the connections. The code is
between proper sections of try/catch statements and connection is being
closed in case of exceptions also. This piece of code is just a test code,
I wrote it just to seperate a single flow of execution of query. I have
debugged this test code and the Close statement does execute for the
opened connection, infact all the dispose,clear, close statements execute
in this test code application given the query and connection string
correct. But even when all these statements execute, I still am left with
open connections according to NetStat Viewer application. Since it shows
me 2 open and established connection after one complete execution of this
code. That is my actual problem. As even if Dotnet does connection
pooling, the connection returned back to pool after a
SqlConnection.Close() must be in a TIME_WAIT state and not ESTABLISHED
state. Hope I could explain to you what my actual problem is, i.e. the
state of connection stays established instead of time_wait even after a
close. Please note that these states are the ones shown in Netstats.

Regards

Usman

First, .Net uses Connection Pooling, so as long as you close your
Connections, you don't need to worry about it.

Second, ensure that you close your Connections, even if an Exception
occurs, by employing a "using" statement, or try/catch/finally.

Third, a DataSet is a *disconnected* set of data. There is no reason not
to close the Connection immediately after filling the DataSet.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
+

Hi

I've a class that creates a connection to a database, gets and loop on a
dataset given a query and then close the connection. When I use netstat
viewer to see if there is any connection open left, I always see that
there are 2 connections open and in "ESTABLISHED" state. Here is the
piece of code that I'm using, please tell where I'm doing it wrong.
Since this class is being used at many placed in my actual web based
application that uses it extensively, so the open connections grow alot,
occupying most of the ports of system and some time creating problem for
other port based applications like ftp.

Few things that I noted on debugging it line by line and monitoring from
netStats viewer.

1. On execution of m_con.Open, it shows 1 or sometimes 2 connections in
"Established" state. No idea why it shows 2 open connections, where it
should only be one.
2. If I only see one connection in established state after m_con.Open
statement, then the second connection in established state comes when
i'm looping over the dataset records. So at the end there are two
connections left open no matter what.
3. Disposing dataset, sqlcommand, sqladapter, or sqlconnection does'nt
have any effect on the state of the connections that are opened.
4. Somewhere on the net I read that the opened connections are not
disposed rather returned to a pool but in a non-established state to
improve performance, but the connection my application opens stay in
"established" state.
5. When I close my application, all the open and established connections
(two in my case) are deleted from the Netstat viewer utility, i.e. the
opened db sessions are closed on application exit.

Please check it and tell me where I'm making the mistake or how to
release the openend connection.

/////////////////////////////////////////////////////CODE//////////////////////////////////////////////////////////
string strSql="select * from mytable";

#region Open connection
SqlConnection m_con = null;
m_con = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=sa;");
m_con.Open();
#endregion

#region Create command
SqlCommand cmd = new SqlCommand(strSql,m_con);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.Text;
#endregion

#region Fill data set
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
#endregion

#region Loop over dataset
if(dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow reader in dataSet.Tables[0].Rows)
{
string szField1 = reader["field1"].ToString();
string szField2 = reader["field2"].ToString();
}
}
#endregion

#region Dispose dataset
dataSet.Clear();
dataSet.Dispose();
#endregion

#region Dispose Adapter and command
adapter.Dispose();
cmd.Dispose();
#endregion

#region Dispose connection object
m_con.Close();
m_con.Dispose();
m_con = null;
#endregion

/////////////////////////////////////////////////////END OF
CODE//////////////////////////////////////////////////////////