Should I persist a sqlconnection in my data access layer?
.net, ado.net, sql
Solution
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'.
Problem
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?