jitbit jitbit - 1 year ago 102
C# Question

"open/close" SqlConnection or keep open?

I have my business-logic implemented in simple static classes with static methods. Each of these methods opens/closes SQL connection when called:

public static void AddSomething(string something)
using (SqlConnection connection = new SqlConnection("..."))
// ...

But I think avoiding opening and closing a connection saves performance. I made some tests loooong time ago with OleDbConnection class (not sure about SqlConnection), and it definitely helped to work like this (as far as I remember):

//pass the connection object into the method
public static void AddSomething(string something, SqlConnection connection)
bool openConn = (connection.State == ConnectionState.Open);
if (!openConn) connection.Open();
// ....
if (!openConn) connection.Close();

So the question is - should I choose the method (a) or method (b) ? I read on another stackoverflow question that connection pooling saved performance for me, I don't have to bother at all...

PS. It's an ASP.NET app - connections exist only during a web-request. Not a win-app or service.

Answer Source

Stick to option a.

The connection pooling is your friend.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download