CODEWITHSUNDEEP
c#sql
george9170
george9170

Reputation:

SQL CONNECTION best Practices

Currently there is discussion as to what are the pros and cons of having a single sql connection architecture.

To elaborate what we are discussing is, at application creation open a sql connection and at application close or error closing the sql connection. And not creating another connection at all, but using just that one to talk with the DB.

We are wondering what the community thinks.

Upvotes: 5

Views: 10832

Answers (6)

Jay
Jay

Reputation: 27464

But on the flip side, I'd be cautious about opening and closing connections too often. This is a lot cheaper with connection pooling than without, but even with pooling, the pool manager may decide to grow or shrink the pool, turning it back into an expensive operation.

My general rule is to open a connection when the user initiates some action, do the work, then close the connection before waiting for the next user input. For any given "Update" button click or whatever, I'll generally have only one connection. But you definately do not want to keep connections open while waiting for user input if you can at all help it for all the reasons others have mentioned. You could literally wait for days before the user presses another key or touches another button -- what if he leaves his computer on and goes on vacation? Tying up a resource for unpredictable amounts of time like that is bad news. In most cases, the elapsed time waiting for user input will far exceed the time doing actual work.

Upvotes: 1

cjk
cjk

Reputation: 46415

I use a helpdesk system called Richmond Systems that uses one connection for the life of the application, and as a laptop user, it is a royal pain in the behind. Even when I carry my laptop around open, the jumps between the wireless access points are enough to drop the DB conenction. The software then complains about the DB conenction, gets into an error state and won't close. It has to be killed manually from Task Manager.

In short, DON'T HOLD OPEN A DATABASE CONNECTION FOR LONGER THAN NECESSARY.

Upvotes: 1

Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

Follow this simple rule... Open connection as late as possible and close it as soon as possible.

Upvotes: 7

BFree
BFree

Reputation: 103742

Under the covers, ADO.NET uses connection pooling to manage the connections to the database. I would suggest leaving it up to the connection pool to take care of your connection needs. Keeping a connection open for the duration of your application is a bad idea.

Upvotes: 1

Rob
Rob

Reputation: 45761

I think it's a bad idea, for several reasons.

  1. If you have 10,000 users using your application, that's 10,000 connections open constantly
  2. If you have to restart your Sql Server, all those 10,000 connections are invalidated and your application will suddenly - assuming you've included reconnect logic - be making 10000 near-simultaneous re-connect requests.

To expand on point 1, you should close connections as soon as you can because otherwise you're using up a finite resource for, potentially, an inifinite period of time. If you had Sql Server configured to allow a maximum of 10,001 simultaneous connections, then you can only have 10,001 users running your application at any one time. If you open/close connections on demand then your application will scale much further as the likelihood of all the active users making use of the database simultaneously is, realistically, low.

Upvotes: 2

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

Close the connection as soon as you do not longer need it for an undefined amount of time. By doing so, the connection returns to the connection-pool (if connection pooling is enabled), and can be (re)used by someone else.

(Connections are expensive resources, and are sometimes limited).

If you keep hold on a connection for the entire lifetime of an application, and you have multiple users for that application (thus multiple instances of the app, and multiple connections), and if your DB server is limited to have only x number of concurrent connections, then you could have a problem ....

See also best practices for ado.net

Upvotes: 10

Related Questions