connection pooling

[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

 

[Miha Markic [MVP C#]]

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?

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?

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

 

[Gokce]

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

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.

 

[Sahil Malik [MVP C#]]

I am assuming that connection pooling means that doing a Close/Open will

be lightweight enough not to affect performance badly. Is this correct?

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
__________________________________________________________