Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Should I have one Global or multiple Module level SQL Connection objects?

I'm writing a vb.net application that connects to a SQL Server database.  Should a create one SQL Connection object at the main form's class level, assign it a connection string and open it in the load of the main form, keep the connection open for the duration of the program and use it whenever there's a SQL Command object in any of the modules that needs an open SQL Connection object?  Or should I only have module level SQL Connection objects, and every time there's a command object in a module that needs a SQL connection object I would declare a local SQL connection object (local to the module), assign it the connection string, and open it specifically for SQL commands in that module?  In the first approach there will always be a connection the SQL Server even when the program is idle and there doesn't need to be a connection; however, in the second approach the program would have to keep instantiating, assigning a connection string, opening and closing a SQL connection every single time it needs one.  Which is better?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Declan Basile

ASKER

Does connection pooling automatically happen?  Do I need to set an option or add code or anything else to activate connection pooling?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I suggest you spend a little time reading up on the subject.  I would start here:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling
Thank you very much.  I just read the article which had exactly what I needed to know.