Connection pooling: Ok, I give up. Where's my leak???????

[X_Factor]

I wrote and re-wrote this thing seven times and I still get the same
error.

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.

at

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)

at System.Data.SqlClient.SqlConnection.Open()
at SiteTools.DB..ctor() in c:\c# projects\sitetools\db.cs:line 27

after about four or five transactions on my ASPX page. Here is my
code for my data access layer.


using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace SiteTools
{
/// <summary>
/// This class will serve to connect to the database.
/// </summary>
///
public class DB
{
protected SiteTools.Email oEmail = new SiteTools.Email();
private SqlConnection oConnection;
private SqlDataAdapter oDataAdapter;
private string Message;

// Constructor
public DB()
{

try
{
oConnection = new
SqlConnection(ConfigurationSettings.AppSettings["Connection"].ToString());
oConnection.Open();
}
catch (Exception ex)
{
// error handling
code
}
}

public SqlCommand ExecuteCommand(string sql)
{
try
{
SqlCommand oCommand = new SqlCommand(sql, oConnection);
oCommand.CommandType = CommandType.Text;
return oCommand;
}
catch (Exception ex)
{

//error handling code
return null;
}
finally
{
oConnection.Close();
}
}

public DataTable GetData(SqlCommand oCommand)
{
DataTable oTable = new DataTable();

oDataAdapter = new SqlDataAdapter();
oDataAdapter.SelectCommand = oCommand;

try
{
oDataAdapter.Fill(oTable);
}
catch(Exception ex)
{
// error
handling code
}
finally
{
oDataAdapter.Dispose();
}

return oTable;
}

~DB()
{
oConnection = null;
oDataAdapter = null;
oEmail = null;
}
}
}


What am I doing wrong????? Line 27 is the oConnection.Open();

 

[Guest]

Hi,

Increase the pool size in Connection string and try again. That may solve
your problem.

Min Pool Size=3;Max Pool Size=20; Add this string to your connection string.

Regards,
Govind.

I wrote and re-wrote this thing seven times and I still get the same
error.

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.

at

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)

at System.Data.SqlClient.SqlConnection.Open()
at SiteTools.DB..ctor() in c:\c# projects\sitetools\db.cs:line 27

after about four or five transactions on my ASPX page. Here is my
code for my data access layer.


using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace SiteTools
{
/// <summary>
/// This class will serve to connect to the database.
/// </summary>
///
public class DB
{
protected SiteTools.Email oEmail = new SiteTools.Email();
private SqlConnection oConnection;
private SqlDataAdapter oDataAdapter;
private string Message;

// Constructor
public DB()
{

try
{
oConnection = new
SqlConnection(ConfigurationSettings.AppSettings["Connection"].ToString());
oConnection.Open();
}
catch (Exception ex)
{
// error handling
code
}
}

public SqlCommand ExecuteCommand(string sql)
{
try
{
SqlCommand oCommand = new SqlCommand(sql, oConnection);
oCommand.CommandType = CommandType.Text;
return oCommand;
}
catch (Exception ex)
{

//error handling code
return null;
}
finally
{
oConnection.Close();
}
}

public DataTable GetData(SqlCommand oCommand)
{
DataTable oTable = new DataTable();

oDataAdapter = new SqlDataAdapter();
oDataAdapter.SelectCommand = oCommand;

try
{
oDataAdapter.Fill(oTable);
}
catch(Exception ex)
{
// error
handling code
}
finally
{
oDataAdapter.Dispose();
}

return oTable;
}

~DB()
{
oConnection = null;
oDataAdapter = null;
oEmail = null;
}
}
}


What am I doing wrong????? Line 27 is the oConnection.Open();

 

[David Browne]

I wrote and re-wrote this thing seven times and I still get the same
error.


System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)

after about four or five transactions on my ASPX page. Here is my
code for my data access layer.

There are several problems with your DB type. You open the connection in the
constructor, but never close it. You return a SqlCommand from a function,
which is a connected object. It has a finalizer, which is just making your
problem worse.

A basic rule that will never fail you is to always open a connection and
close it in the same block of code.

Put this method in your code

private static SqlConnection Connect()
{
SqlConnection con = new SqlConnection(connectioString);
con.Open();
return con;
}

And then always use the connection like this

using (SqlConnection con = Connect()
{
//use the connection here


}

The using block will automatically close the connection.

David

 

[David Browne]

Hi,

Increase the pool size in Connection string and try again. That may
solve
your problem.

That never solves the problem. It just hides it. Eventually, and always at
the worst time, you will run out of connections.

David

 

[Marina]

You are opening a connection in the constructor, but unless you call
ExecuteCommand (which doesn't actually seem to execute a command), I don't
see a way for the connection to be closed.

You should not leave an open connection in the constructor. You can create
the connection object there, but each method using the connection should
open it, and then close it - all inside that method. The connection should
NOT be open in between method calls on this object.

Of course you are running out of pooled connection - you keep creating this
object, which opens a conenction - and then this connection presumably is
never closed.

I wrote and re-wrote this thing seven times and I still get the same
error.

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.

at

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString
options, Boolean& isInTransaction)

at System.Data.SqlClient.SqlConnection.Open()
at SiteTools.DB..ctor() in c:\c# projects\sitetools\db.cs:line 27

after about four or five transactions on my ASPX page. Here is my
code for my data access layer.


using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace SiteTools
{
/// <summary>
/// This class will serve to connect to the database.
/// </summary>
///
public class DB
{
protected SiteTools.Email oEmail = new SiteTools.Email();
private SqlConnection oConnection;
private SqlDataAdapter oDataAdapter;
private string Message;

// Constructor
public DB()
{

try
{
oConnection = new
SqlConnection(ConfigurationSettings.AppSettings["Connection"].ToString());
oConnection.Open();
}
catch (Exception ex)
{
// error handling
code
}
}

public SqlCommand ExecuteCommand(string sql)
{
try
{
SqlCommand oCommand = new SqlCommand(sql, oConnection);
oCommand.CommandType = CommandType.Text;
return oCommand;
}
catch (Exception ex)
{

//error handling code
return null;
}
finally
{
oConnection.Close();
}
}

public DataTable GetData(SqlCommand oCommand)
{
DataTable oTable = new DataTable();

oDataAdapter = new SqlDataAdapter();
oDataAdapter.SelectCommand = oCommand;

try
{
oDataAdapter.Fill(oTable);
}
catch(Exception ex)
{
// error
handling code
}
finally
{
oDataAdapter.Dispose();
}

return oTable;
}

~DB()
{
oConnection = null;
oDataAdapter = null;
oEmail = null;
}
}
}


What am I doing wrong????? Line 27 is the oConnection.Open();

 

[X_Factor]

Thank you. I will try it out when I get home.