SQLConnection open and close vs. staying open.

[UlrikSL]

My question is performance wize, is it better to open and close each time I make a request to the database, in small periods I make like 5 queryes a second, other times only every 10 seconds.

I can understand that close and open do not close the underlaying connection, so would the correct way be to open and close with each request, and give a better chance of avoiding faulty connection? Or would it cause worse performance?

Thanks.. Ulrik

[Curtis Rutland]

I personally close my database after every transaction, unless I am doing several at a time. For example, if I needed to find the PK of a row to update, I don't close the conn between my select and update. If I need to loop through a list of commands I don't close. But I do close as soon as my chunk of commands are closed. I can always open it again.

[Frinavale]

I personally close my database after every transaction, unless I am doing several at a time. For example, if I needed to find the PK of a row to update, I don't close the conn between my select and update. If I need to loop through a list of commands I don't close. But I do close as soon as my chunk of commands are closed. I can always open it again.

I agree with Insert on this one.
It will prevent problems in the future if you keep things clean and close the connection when you finish with the transaction. The only time it should stay open is if your doing a few transactions at the same time in a function.

Keeping your code clean will help in the long run.

-Frinny

[UlrikSL]

Thanks for the answer..

After implementing the close and open in my singleton I still got some errors with my connection, now it was because of connection was still open. But I was closing the connection in a finalize, so I was sure it was closed everytime. After adding some sync locking to my code, everythings works like a charm.. Didn't know that asp.net runs its code-behind in multiple threads.. hmm..

[Plater]

Didn't know that asp.net runs its code-behind in multiple threads.. hmm..

Every user that connects to your page will effectively have their "own thread".

I also use the open and close method. However I am been "living dangerously" by not locking anything. Fortunatly, it doesn't get used much and it has not yet been a problem. Its on my "to do" list to fix