14426

Enforce SqlConnection to be open for specified duration

Question:

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 SqlConnection.State is Open before executing a query.

Answer2:

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

Recommend

  • Passing props to component in React Router 4
  • Merge and sum two multidimensional arrays in PHP
  • using Switch like logic in T-SQL
  • What are 'best practices' for dealing with transient database errors in a highly concurren
  • exclude html attributes in str_replace
  • Pass compound compiler options using cmake
  • Laravel with different session lifetimes
  • Why __weak object will be added to autorelease pool?
  • Connect ipython-notebook via SSH tunnel from a remote location
  • Similar function in C++ to Python's strip()?
  • How do I check assembly output of Java code?
  • How to remove default command line arguments provided by Eclipse?
  • Array resizing and realloc function
  • ZF2 Doctrine: When to flush ObjectManager
  • Exception handling as per java coding standards
  • Whats the right place for testhelper-classes? (phpunit/best practise)
  • How to best manage SMTP clients
  • Unicorn and Rails eat up 2x MySQL connections
  • SQL query to group by maximal sets of a column having inner consecutive distances below a threshold
  • Scala: Function returning an unknown type
  • LiveData is abstract android
  • Application level floating views with navigation in Android
  • Why use database factory in asp.net mvc?
  • AndEngine Applying Transparancy to AndEngine View
  • Using a canvas object in a thread to do simple animations - Java
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Excel's Macro-Recorder usage
  • How to use carriage return with multiple line?
  • Sencha Touch 2.0 Controller refs attribute not working?
  • Avoid links criss cross / overlap in d3.js using force layout
  • javascript inside java/jsp code
  • Weird JavaScript statement, what does it mean?
  • Android Studio and gradle
  • need help with bizarre java.net.HttpURLConnection behavior
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • How can i traverse a binary tree from right to left in java?
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF