When should I open and close a connection to SQL Server

Question

I have a simple static class with a few methods in it. Each of those methods open a SqlConnection, query the database and close the connection. This way, I am sure that I always close the connection to the database, but on the other hand, I don't like to always open and close connection. Below is an example of what my methods look like.

public static void AddSomething(string something)
{
    using (SqlConnection connection = new SqlConnection("..."))
    {
        connection.Open();
        // ...
        connection.Close();
    }
}

Considering that the methods are inside a static class, should I have a static member containing a single SqlConnection? How and when should I drop it? What are the best practices?

Answer 1

Most programmers believe in open late and close early. This is only a problem if the latency for opening and closing the connection each time causes the entire application to slow down.

In your case with a static class it is probably best to open and close the connection each time.

Answer 2

You are doing the best practices. Only open it right before you are going to query it, and close it as soon as you can. This kind of thing may seem wasteful at first, but it actually makes your application more scalable in the long run.

Answer 3

No, don't keep a static SqlConnection unless you have to. Threading would be one concern, but more importantly - usually you simply don't need to. With your code as presented, the internal connection pooling means that most of the time you will get the same underlying connection on successive calls anyway (as long as you use the same connection string). Let the pooler do its job; leave the code alone.

This also avoids the issues of what happens when you start having two threads... now each can do work on their own connection; with static (assuming you don't use [ThreadStatic]) you'd have to synchronize, introducing delays. Not to mention re-entrancy (i.e. a single thread trying to use the same connection twice at the same time). Yup; leave the code alone. It is fine now, and almost any change you make would make it not fine.

Answer 4

Because the SqlConnection has a connection pool when you call Open() and Close() you aren't actually opening and closing the physical connection to the server. You are just adding / removing the connection from a pool of available connections. For this reason it is a good and best practice to open the connection as late as possible and close the connection as early as possible after executing your command.

Answer 5

In your code sample there is no need to call the close() method on the connection object as it will be handled automatically due to the code residing inside a using block.

Answer 6

But with a return value(say name) from DAL

{

. .

cmd.executenonquery; return cmd.Parameters[0].Value

}

to Presentatation layer it throws exception & cmd.Parameters[0].Value does no longer has value as soon the connection is closed

Pls let me know how should/where should we place conn.close when Stored Proc returns value

Answer 7

Don't ever rely on the connection to close itself. If it's not explicitly closed, it will lead to performance issues. It happened to us on our project. Yes, I'm aware that connections are managed by a connection pool, but they still have to be closed and returned to the pool.