It seems that SqlConnections close after some period of time of inactivity. I need to be able to control for how long the connection remains open. There is a "connection timeout" and "Connection Lifetime" properties that can be configured for connections, but they do not do what I need.
The problem is that our application is huge and in lots of places connections don't get closed when some screens are open. A user can open a screen, then go to lunch, then come back, then try to do something and get a "connection has been lost" exception. I know that it is an antipattern, but for now it would be easier to extend the connection lifetime in one place, then to open/close and pool connections in thousands of places.Answer1:
Connections don't close automatically. Something has failed, like the network.
That is expected, though. Don't hold connections open! You are working against how the system was meant to be used, and you'll suffer for it.
Change the app so that it uses one connection per unit of work, and the closes it. As a band-aid fix, ensure that
Open before executing a query.
You are designing your app very wrong. Connections should never stay open for any longer than a single set of queries (that is, the queries needed for a single operation). Don't worry, there is no performance issue as SqlConnection internally pools connections based on connection string. So even if you open and close a connection, the REAL connection will stay open for a period of time.
What's more, internally, if the connection is closed, then the pooler will re-open it when needed, so you don't have to worry about it.
Seriously, just open and close your connections as you use them. That's how it's supposed to work.
FYI, the reason the connections are closed is that the internal connection pooler will "reclaim" connections that have been open for a long time. So what you're doing by keeping the connections open is fighting the connection pooler.Answer3:
Its not good programming to open connection so long. Open connection whenever need and close to after completion of database task. If you feel it will impact on your application performance then use connection pooling