Need help with ADO Connection
-
Hi ya,
I've inherited a VB6 project (Yes, I know, most horror stories have a similar beginning
).However, since I am not that much into VB anymore, I just wanted to make sure, to do things right. So, here's a question about ADO's connection object:
- Is there any advantage of closing and reopening an ADO connection in almost any event? I would rather like to open it at the beginning and leave it open as long as the program is running.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I would create/open it at program open and close it at program end. This way you don't have a bunch a open/close which eat the server alive
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks AJ,
that's also my understanding. I've commented the public declarations and now the code is breaking over and over. Aah, I really love cleaning up someone else's crap
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
CLosing your object and destroying it is a good practice from a memory standpoint. It does not hurt the server in any way for the connection to be closed and speed will be high due to connection pooling. However imagine if that person leaves the application open 24/7 now you have network overhead that was never meant to exist because you didn't close the connection and let the pool timeout.
I don't know why people seem to miss connection pooling. Please read here more and of course try MADN Online.
http://www.sql-server-performance.com/sk_connection_pooling_myths.asp
-
Damn, I know this one, but have forgotten. However I should have also mentioned, that this app is used by at most 3 persons (not simultaneously) on two days a week. so, nothing that I would consider resource intensive. Aah, should I do it now quick and dirty or should I do it right? Btw, I guess it also holds true for Recordset object to be closed and destroyed after use, right?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Holds true for all objects Frank... But even more for RecordSets.
-
Okay, okay, I'll do it right......
Hmpfh, php is quite different than VB. Should remember the MS way. So I guess quite a few questions to come very soon.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Note: Closing a connection object does not delete it from memory. Same thing goes for the Recordset. If you close an object and it's no longer needed, you should set the object connection or recordset equal to Nothing to remove it from memory.
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Dale, what a nice suprise!!!
You've updated your profile pic once again, since I last saw it. I like that.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Typically for this app, I have a form with about 5 or 6 comboboxes, which are dependant on each other. In the Click() event, the value is determined and used to populate the next combo. In that case it is really one opening and closing after the other. Is this really more effective than, say opening the connection at Form_Load and closing at Form_Unload?
And, wtf, does "Carpal Tunnel" under my avatar mean?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Good to see you too, Frank! I've been busy with parenthood myself. Only my kids are furry. I have two Silkie Terrier puppies that are six months old now. They just got fixed last week. It's time to get back to work. I'm going crazy!
As for your Open Connection and Close Connection, I would think Form_Load and Form_Unload would work just fine. I can't imagine that the resources would get swallowed up that quickly with a few Click events. Besides, opening and closing the connection with each Click event would probably slow the app down. I would think you'd have more to worry about with the Recordset(s).
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Hi again, Frank,
Have you ever heard of http://www.w3schools.com? It is an awesome free Web site with Web building tutorials. ADO is one of them, http://www.w3schools.com/ado/ado_intro.asp. Check it out! I love anything that is free!
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Actually no won't slow down at all. COnnection pooling does't truely drop the connection like you would think. I puts it off for a quick reuse until the poolts timeout is reached for the connection object.
-
Antares686,
True! True! Assuming you need to use shared database connections. However, don't forget the dreaded exception problem with connection pooling.
"If a connection exists to a server that has disappeared, it is possible for this connection to be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. When this occurs, an exception is generated." -MSDN-
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply