jitbit jitbit - 1 month ago 7
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("..."))
{
connection.Open();
// ...
connection.Close();
}
}


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

Stick to option a.

The connection pooling is your friend.

Comments