"Open/Close" SQLconnection or Keep Open

open/close SqlConnection or keep open?

Stick to option a.

The connection pooling is your friend.

What is Best Approach for Opening/Closing SqlConnection in C#

Your pattern for open and close is correct. However you must note that this doesn't open and close the connection to the SQL Server so doesn't really address your concerns over memory usage and CPU - in fact it wont make any difference.

What Open and Close does is lease and return a connection to the ADO Connection Pool on the client PC. This means that Closing an ADO connection is not guaranteed (and in most cases will not) close and release the connection to the SQL Server. This is becasue establishing and authenticating a connection is relatively expensive and slow, so the ADO connection pool keeps your connections in a pool, still open, just in case you want to re-establish a connection.

What makes the difference to SQL Server is the number of concurrent queries it needs to execute - and the dataset size of the queries, and the total size of the data in the database.

Concurrent queries squeeze CPU, and the datasets returned squeeze the RAM available. Obviously the bigger your database the less can be cached in RAM and so the less likely you are to get a cache hit when querying.

In practice my experience with SQL Express editions is that you wont notice any difference between it and the full edition of SQL Server unless you are doing some very specific things;

1) Writing a BI style tool which allows the user to construct user-defined or user-scoped queries.
2) Writing terrible SQL - "big SQL" may mask your bad query syntax, but Express wont be able to because it has less available RAM to play with.

If you write efficient, constrained SQL, you probably wont actually ever hit any of SQL Express's limitations.

When should I open and close a connection to SQL Server

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.

Is it ok to leave a sql connection open?

A database supports more than one concurrent connection, so yes it is very feasible in this scenario to leave the DB connection open - you will only lock out others if you have i.e. a long running query that results in row/ table locking. Just close the connection when you are done.

Also consider though that most DBs (i.e. SQL Server) use connection pooling internally, so even though you close a DB connection it just goes back to the pool and is not physically closed - the pool manages the physical DB connections - this results in much better performance, so the impact of opening/closing connections rapidly is reduced.

From MSDN:

Connection pooling reduces the number of times that new connections
must be opened. The pooler maintains ownership of the physical
connection. It manages connections by keeping alive a set of active
connections for each given connection configuration. Whenever a user
calls Open on a connection, the pooler looks for an available
connection in the pool. If a pooled connection is available, it
returns it to the caller instead of opening a new connection. When the
application calls Close on the connection, the pooler returns it to
the pooled set of active connections instead of closing it. Once the
connection is returned to the pool, it is ready to be reused on the
next Open call.

What is the best time to open and close a connection to database?

ASP.NET / Web API / (...)

• Usually tied to the unit of work pattern, a connection is created for an incoming request then closed when the request has been processed
  
  
  • deviations may occur when you have long running background tasks

WPF / Window Forms

• In this case I would suggest that it's more on a per action basis, even when polling for changes - if that scenario exists
  
  
  • Open, Query, Close, repeat

Proper way to open and close connection in loop

ADO.NET uses Connection Pooling under the hood. That is why opening a new connection each time should not be an issue. Your call to myConnection.Open() will not actually result in opening of physical connection to the Database each time.

Check also this question. It's author made a measure test for connection opening. I have shown him that time of subsequent calls to DbConnection.Open() approaches to 0.

The most preferred way of using DbConnection is to open it for the minimal time period. If your MyProcess.Execute() has a lot of other non-DB related logic that takes considerable time, you should not keep an opened connection during this execution.

Check also Best Practices for Using ADO.NET article. It has following explicit statement:

High performance applications keep connections to the data source in
use for a minimal amount of time, as well as take advantage of
performance enhancing technology such as connection pooling.

So when does Ado.net removes the connection from connection pool?

This article provides some internal details on connection pooling:

The connection pooler removes a connection from the pool after it has
been idle for approximately 4-8 minutes, or if the pooler detects that
the connection with the server has been severed. Note that a severed
connection can be detected only after attempting to communicate with
the server. If a connection is found that is no longer connected to
the server, it is marked as invalid. Invalid connections are removed
from the connection pool only when they are closed or reclaimed.

This article also provides some details on pooling tuning if it's required. However you should consider such manual configuration only if you experience some performance issues caused by the pooling.

C# sql create one connection and open and close for each query

Either one of these are acceptable. A SqlConnection uses a connection-pool so it shouldn't affect performance much. Having multiple SqlConnection objects isn't going to hurt anything. This comes down to a preference.

I would suggest encapsulating the commands in a using statement if you keep the connections inside the methods, such as:

using (SqlConnection conn = new SqlConnection(...))
{
    conn.Open();
    //commands
    conn.Close();
}

This ensures proper disposal of the connection.

How do I keep an SqlConnection open for the entire time that the ASP.NET application is running?

SqlConnection is not thread-safe.

Since ASP.Net requests can arrive on multiple threads, you must not do that.

SqlConnection is already pooled; you don't have anything to worry about.

Why is the SqlConnection still open after forcing .Close() and with using(...)?

By default, ADO.Net uses connection pooling. From the docs (emphasis mine):

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

---

Related Topics