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

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 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.