connection pooling

  • Thread starter Thread starter Clipboard J. Spaghetti
  • Start date Start date
C

Clipboard J. Spaghetti

Hello everyone,

Could you please tell me if it is appropriate to use code such as this to
generate a valid SqlConnection object for use with objects that use it?


private SqlConnection GetConnection()
{
try
{
// connection pooling means this should get reused immediately
con.Close();
}
catch(){};

con = new SqlConnection("Database=zzz;Server=X1;Trusted_Connection=Yes");
con.Open();

return con;
}


I am assuming that connection pooling means that doing a Close/Open will
be lightweight enough not to affect performance badly. Is this correct?

Thanks
 
Clipboard J. Spaghetti said:
Hello everyone,

Could you please tell me if it is appropriate to use code such as this to
generate a valid SqlConnection object for use with objects that use it?
Click to expand...

Connection has to be open and closed on the same level - in this case the
code that called GetConnection should close it, too.
private SqlConnection GetConnection()
{
try
{
// connection pooling means this should get reused immediately
con.Close();
}
catch(){};

con = new
SqlConnection("Database=zzz;Server=X1;Trusted_Connection=Yes");
con.Open();

return con;
}


I am assuming that connection pooling means that doing a Close/Open will
be lightweight enough not to affect performance badly. Is this correct?
Click to expand...

Yes, as long as connection string remains exactly the same (including user
if windows auth is used).
 
Clipboard said:
Hello everyone,

Could you please tell me if it is appropriate to use code such as this to
generate a valid SqlConnection object for use with objects that use it?


private SqlConnection GetConnection()
{
try
{
// connection pooling means this should get reused immediately
con.Close();
}
catch(){};

con = new SqlConnection("Database=zzz;Server=X1;Trusted_Connection=Yes");
con.Open();

return con;
}


I am assuming that connection pooling means that doing a Close/Open will
be lightweight enough not to affect performance badly. Is this correct?

Thanks
Click to expand...


yes, but don't forget to close all connections you have opened after
your job is finished with them. if not you may exceed maximum pool size.
 
I am assuming that connection pooling means that doing a Close/Open will
be lightweight enough not to affect performance badly. Is this correct?
Click to expand...

Yes, but I still don't like this factory code you have written. The biggest
downside I see is that you are encouraging bad design by returning an
open-ready to use connection. This would lead to someoen forgetting to
close. Always use SqlConnections in one of the following two -

a) Within a Using block
b) In Try Catch Finally - Finally closes the connection.

Any other usage is serious eyebrow raiser. Even transactions should be done
in the "b" approach above.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________
 
Back
Top