Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrgr8avill
Starting Member
16 Posts |
 Posted - 2007-12-15 : 20:46:06
|
| Hello,I am putting all my database logic into a separate class file for ease of organization. It raises the question of when it is best to open the connection.For each page that requires data access, I instantiate the class globally (so all page methods have access to the single class instance). But I am wondering if it is better to:a) open the database connection in the class constructor, so it remains open throughout the page processing cycle, orb) open and close the connection within each method of the database class. Seems if I have 100 SQL commands to run on a page, all the open/close cycles would add overhead, but on the other side, leaving the connection open for the whole time the page is being processed might be worse.Thanks for takinbg the time to read this, and thanks again in advance for any information you can provide. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 21:07:18
|
I would think that you'd want to open/close the connection only once to fetch data for a single page. reopening hundreds of times for a single page load is wasteful.however i'm not sure about the idea of using the same connection for all pages - you'll get into trouble if 2 or more are trying to use SqlDataReader at the same time, unless you use MARS.I don't do web dev though so far from expert here.
elsasoft.org |
 |
|
|
mrgr8avill
Starting Member
16 Posts |
Posted - 2007-12-15 : 21:20:07
|
| Thanks for that -- that is what I thought.Actually that's what I was trying to get around my using a separate class.cs file for the DB routines.I need to ask that question in the .net forums.Thanks for your reply! |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-12-17 : 14:09:35
|
| I'd say it probably is not a good idea to open the connection directly from the constructor. Your constructor may set some properties for that connection though. Theres prolly still debate about this topic. But I don't open the connection unless I need the connection. So I would say dont open it right from the constructor. Open it when you need it and make sure you close it when you're done. You may want additional code in your Finally statement to ensure the connection is closed and cleaned up.Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
JBelthoff
Posting Yak Master
173 Posts |
Posted - 2007-12-18 : 10:43:16
|
.Net Connection pooling makes things a bit faster. So there is no problem opening and closing connections for each db call on a page.However, if you have multiple db calls you might want to consolidate them into one stored proc returning multiple result sets, parameters or use the asp.net caching so you don't have to go to the db each time.Open late and close early!Just my 2 pennies anyway... JBelthoff• Hosts Station is a Professional Asp Hosting Provider› As far as myself... I do this for fun! |
|
|
|
|
|
|
|
|
SQL in separate class, when to open conn?