.NET SqlConnection class, connection pooling and reconnection logic

No, it's not inefficient to create lots of SqlConnection objects and close each of them when you're done. That's exactly the right thing to do. Let the .NET framework connection pooling do its job - don't try to do it yourself. You don't need to do anything specific to enable connection pooling (although you can disable it by setting Pooling=false in your connection string).

There are many things that could go wrong if you try to cache the connection yourself. Just say no :)

You should enable connection pooling on your connection string. In that case the runtime will add back your connections to the 'pool' when you close them, instead of really disconencting. When a 'new' connection is taken out of the pool it will be reset (ie. sp_reset_connection is called ) then presented to your application as a brand new, fresh connection. The pool is handling transparently such cases as if the connection is closed while idling in the pool.

The cost of creating a new connection 'from scratch' is significant because the authentication requires several roundtrips between client and server (depending on the authentication method and on SSL settings it can be 1 roundtrip in best case vs. about 10 in worse).

And to answer your question, connection raise the OnStateChange event when their state changes, but you shouldn't care about this if you use the pooling.

In my recent experience if you use this code:

using(SQLConnection conn = new SQLConnection(connectionString)){    // do stuff with conn}

have an error, and do not explicitly close the connection, it will not be closed or checked back into the pool. So use a catch or finally block to close the connection