Should I persist a sqlconnection in my data access layer?

Question

It seems like there is a lot of overhead involved in rapidly opening and closing sqlconnections. Should I persist a connection (one, per client, per database), or continue declaring a new sqlconnection object whenever I need one, and making sure I clean up after myself?

What have you done? What worked well and what worked poorly?

Answer 1

If you are using the same connection string you're connections will be pooled. You should only have a connection open as long as you need it.

Answer 2

There is not much overhead since, by default settings, pools are stored in the connection pool. Thus, when you open a connection, often you'll just get a ready connection from the pool. Creating SqlConnections has not given me any troubles.

Answer 3

I did have the same thought, so I used the same connection in a tight loop to prevent having to instantiate another one when I needed it. But sometime it's hard to keep track of it and debug, if you get a DataReader off the connection and then try to do another one while the same reader is still active, then you'll get exception. So, I would only recommend it if it's really frequent like a tight loop, otherwise it's not worth the trouble.

Answer 4

In most cases, .NET connection pooling handles this for you. Even though you're opening and closing connections via code, that's not what's happening behind the scenes. When you instantiate and open a connection, .NET looks for an existing connection in the connection pool with the same connectionstring and gives you that instead. When you close the connection, it returns to the connection pool for future use.

If you're using SQL Server: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

OLE DB, ODBC, Oracle: http://msdn.microsoft.com/en-us/library/ms254502.aspx

Dino Esposito article: http://www.wintellect.com/Articles/ADO%20NET%20Connection.pdf

You can override default pooling behavior with connectionstring name/values: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx. See the second table of settings containing 'Connection Lifetime'.

Answer 5

When do connections in the pool time out?

Answer 6

This is generally not a good thing to do (you could cause a leak and eventually run out of connections), but instead rely on the Connection Pool for performance and open connections as needed and close connections as quickly as possible.

Bill Vaughn has a number of useful articles about connection pooling and data access including this one

Answer 7

For years we had the client keep a single persistent connection to the database. The problem comes in detecting an intermittent connection failure and gracefully reconnecting. Quite often you won't know that a connection failed until you try to use it (i.e. issuing a select will throw a 'General SQL Error')

We now use a globally available static class who's job is to hand you a new connection to the database, and when you're done with it you use the same class to get rid of the connection.

DbConnection conn = Database.GetConnection();
try
{
   //do stuff with the connetion
   ...
}
finally
{
   Database.DisposeConnection(conn);
}

We do this because there is initialization needed when we connect to the database (we store information is SQL Server's CONTEXT_INFO, and must empty that information when we disconnect)

Answer 8

Open late, close early.