Is it acceptable to keep a db connection open for the life of the page?

Question

Everybody knows that you should close a connection immediately after you finish using it.

Due to a flaw in my domain object model design, I've had to leave the connection open for the full page life cycle. Essentially, I have a Just In Time property which opens a connection on first call, and then on Page.Unload (..) it would check if a db connection was ever open, and then close it if it was. Since it only takes a second, I’ve had the opinion its not too much different than closing it immediately.

Is this ok? Or should it still be closed immediately after every single use?

Thanks in advance.

Answer 1

Yes, it is ok.

Closing the connection as soon as you can is a best practice for preventing orphan open connections, but if you are sure that the connection is being close, there is nothing wrong with that.

Answer 2

What if you page crashes before reaching the Page.Unload event? You will have a opened connection. For me it is better to always close the connection as soon as possible.

Answer 3

No, it is not OK.

If your application will ever need to grow or scale, you'll want to fix this issue. By holding that connection open you're reducing your ability to scale. Keep in mind that open connections take up memory on the server, memory on the client, hold open locks, etc.

Answer 4

page crashes? this is what using and finally are for

that said, for the sake of DB performance (i.e. scaling)* it's best to keep connections open as short a period as possible allowing only that you don't want to open close open close open close for rapidly sequential and predictable work

* I was told this by a mentor early in my career, I must say I've not actually tested this myself but it sounds right theoretically

Answer 5

Every decent ASP.NET app uses connection pooling nowadays, and a pool is basically a bunch of open connections. In your case that would mean that the connection you're holding on to is "occupied" and can't be used to serve other requests.

As far as I see it would be a scalability issue depending on the amount of time your page needs to do work/render. If you expect only 100 users, like you say, then probably it's not an issue - unless it's 100 req/sec of course.

From the technological perspective it's OK. As far as I remember most client-server applications (web- and non-web), including classic ASP-code used to work like that, e.g you declare one connection for the entire page and work with it.

Answer 6

Of course you can keep them open, but no no. Close it after use in finally blocks. A fair trade off from "after every single use" is to close it after every block of use, if you're apt to run a stored proc, update a column, then delete some other row, you could open/close around those three operations, presuming they're all wrapped in a try/catch/finally.

Answer 7

It's not ideal but I wouldn't re-write my application over it. Unless your page is doing a large amount of time-consuming work in various methods, the whole page lifecycle should execute quickly. In practice it may just mean that your connection object is open a few milliseconds longer than it would have been otherwise. That might be significant in some scenarios, but it doesn't sound like it would be in your case.

Answer 8

You should certainly keep the connection open across the lifetime of the page, if you're doing multiple queries during it. Generally, one re-uses connections across many pages, actually.

Answer 9

I think a better question with much more informed and productive feedback would be possibly providing some snippets of what you're doing (code) and expanding on the reasons why you've made this choice. There is most likely a better solution that doesn't require keeping the connection open so long, but at least, for pragmatic reasons, you could get some feedback on whether it's worth revamping.

In future, you definitely want to move away from data access in your code-behind.

Answer 10

I find it convenient to keep the connection open when using ORM (Open Session in View) so that after an initial eager fetch, other data can be lazily loaded as needed. This works well when page response times are reasonable so as not to tie up connections.