D
David Kirkman
I have a C# Windows Forms app. (Framework 1.1 VS 2003) using a SQL
Server 2K database. It appears that despite closing and disposing all
connections immediately after use and always using the same connection
string the connections in the connection pool are not being reused.
When the code attempts to open the 100th connection, the following
exception is thrown:
System.InvalidOperationException: Timeout expired. The timeout period
elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size
was reached.
Using SQL Enterprise manager it can be seen that the connections are
indeed being closed i.e there is only ever one connection open - so
what is going on? According to everything I have read about connection
pooling, the connections should be stored in the pool when they are
closed and if an attempt is made to open a new one with the same
connection string, a connection in the pool is used.
Here is some code which uses Northwind so anyone can run it. Just
create a form and put a button on it, You will need to make sure that
the Output window is visible in VS:
private DataSet GetDataSetSql(string sqlString)
{
string cnString = "server=" + "Put your server name here" +
";Trusted_Connection=yes; database=Northwind";
SqlConnection cn;
try
{
cn = new SqlConnection (cnString);
cn.Open();
SqlDataAdapter da = new SqlDataAdapter(sqlString, cn);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
cn.Dispose();
return ds;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
Console.Write(ex.ToString());
return null;
}
}
private void button1_Click(object sender, System.EventArgs e)
{
string sql;
DataSet ds;
for (int x = 10247; x < 10447; x++)
{
sql = "select orderid from orders where orderid = " +
x.ToString();
ds = GetDataSetSql(sql);
Console.Write(x.ToString() + "\n");
}
}
On my machine the OrderIDs are shown in the output window up to 10346
(the 99th order in the table) it then fails. I would be interested to
know if this behaviour occurs for others. I have tried increasing the
Pool size in the connection string and this allows more connections to
be made but there should be no need to do this as 100 should be more
than enough.
Server 2K database. It appears that despite closing and disposing all
connections immediately after use and always using the same connection
string the connections in the connection pool are not being reused.
When the code attempts to open the 100th connection, the following
exception is thrown:
System.InvalidOperationException: Timeout expired. The timeout period
elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size
was reached.
Using SQL Enterprise manager it can be seen that the connections are
indeed being closed i.e there is only ever one connection open - so
what is going on? According to everything I have read about connection
pooling, the connections should be stored in the pool when they are
closed and if an attempt is made to open a new one with the same
connection string, a connection in the pool is used.
Here is some code which uses Northwind so anyone can run it. Just
create a form and put a button on it, You will need to make sure that
the Output window is visible in VS:
private DataSet GetDataSetSql(string sqlString)
{
string cnString = "server=" + "Put your server name here" +
";Trusted_Connection=yes; database=Northwind";
SqlConnection cn;
try
{
cn = new SqlConnection (cnString);
cn.Open();
SqlDataAdapter da = new SqlDataAdapter(sqlString, cn);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
cn.Dispose();
return ds;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
Console.Write(ex.ToString());
return null;
}
}
private void button1_Click(object sender, System.EventArgs e)
{
string sql;
DataSet ds;
for (int x = 10247; x < 10447; x++)
{
sql = "select orderid from orders where orderid = " +
x.ToString();
ds = GetDataSetSql(sql);
Console.Write(x.ToString() + "\n");
}
}
On my machine the OrderIDs are shown in the output window up to 10346
(the 99th order in the table) it then fails. I would be interested to
know if this behaviour occurs for others. I have tried increasing the
Pool size in the connection string and this allows more connections to
be made but there should be no need to do this as 100 should be more
than enough.