Keep Sqlconnection open or close it after every access? 1

[rickinger]

Hi fellows,

quick beginner's question... I using sort of a database access layer class to collect and prepare all my sql server's data for use in different places of my application. Now I wonder, should I open and close the SqlConnection every time I access the database or I should I keep the connection open all of the time while the application is running? Is there a performance/security difference?

Thanks in advance
Best wishes
waldemar

 

[nevets2001uk]

I always ensure I close the connection when I'm not using it. There are some performance reasons behind this. Not sure about security as I've not delved far enough into database access to be any more than a amateur so far.

From everything I've read however they suggest having an open connection to the database for a short a time as possible to reduce load on both the app server and db server. I would imagine it helps with locking and connection pooling issues as well.

Always create your SQL command string. Open the connection. Execute the Command. Close the database. Only leave it open if you're executing multiple commands immediately after each other.

Hope this helps.

Merry Chrismas,
Steve

 

[rickinger]

Perfect! Thanks a lot Steve!

 

[chiph]

As long as your SQL connect string is identical every time, you will gain the benefit of ADO.NET's connection pooling. And once you've gotten that, opening/closing connections as-needed is quite fast.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[tgreer]

I would add that your connection string(s) should be stored in web.config.



Thomas D. Greer

http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

http://www.brownfedora.com

Haiku workshop and community.

 

[DaZZleD]

also bear in mind that each connection made requires its own authentication! this is time lost.

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[rickinger]

Sounds like there is not really a boolean answer to this question?

Maybe let me explain a little more detail...

Originally this is a two dozens users access-client to access-server database and things just got too slow in the last months (the frontend app is about 20 mb in size now because of forms and reports).
My plan is to completly migrate to c# frontend plus sql server (originally I planned to also include some remoting with a cute little server demon to handle all db queries but I guess this way too exagerated ...). So I'm learing everything at once... Trying to put everything in stored procedures etc... but I'm a little scared that I'm doing typical access mistakes (packing everything on one form) and I might be missing some essential asp.net handling procedures that only comes out of experience...

This forum is great ressource for me; thanks everyone, hope I can give help back asap.

 

[rickinger]

(continued)

Still so many questions remaining... it seems there is a gazillion of options how to do things now... filling text boxes programatically or databinding; returning stored procedure results as hash tables or as datatables (or sets) - another interesting idea was to build a complete object model in the client that is identical to the table structure (but then how can the server trigger the client, that some data was modified and needs to be requested again).... questions over questions...

 

[chiph]

Oh, this is Access.

I don't know if anything I said earlier is true, then, since Access is a personal desktop-sized database, and not really suitable for multi-user applications.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first