global DB connection in classes?

[kleinma]

I am writing some classes that will be used to hold and deal with customer info stored in a SQL Server DB.

Now when loading or saving data, I need a connection to the SQL Server. What is the best way to impliment this so that each time a class is created it doesn't create its own connection to the database? Could I create a base class that holds the DB connection and each class could inherit from this class to use its connection to the database?

[Mike Hildner]

Are you wanting to keep a connection open all the time? Probably not a good idea as it uses resources, so it's recommended to create your connection, do what you want, then close the connection.

Not sure if this is the best way, but in one project, I had a SQLStuff class, which had a method GetConnection, to retrieve a new connection. Other classes would get their connection through this method, then close it when done.

Recently I've been working on a project where the classes don't touch the DB at all, just call methods, like GetTable, UpdateTable etc. in a seperate class. This seperate class handles all the connecting, disconnecting etc.

Not sure if this helps, but what the heck.
Mike

[SeanGrebey]

Or you can pass in a connection object reference as part of your class constructor. personally I usually just create one object to handle by SQL Interface, give it a private SQL Connection object, and then just open and close it as needed.

[kleinma]

yeah i was thinking of this (using a connection per class basis) just seemed like a waste of time as far as connection time, because this database is a remote SQL Server and when you open a connection to it, it takes about 5-10 seconds to establish the connection.

[Mike Hildner]

I see. How about a public static (shared in VB?) variable in a SQL class?

[kleinma]

well the other classes that need it would have to create an instance of the class, creating a new instance creates a new copy of that static variable, not use the same one over and over (unless i am wrong?)

[Mike Hildner]

You're wrong That's the point of shared (static in C#). You reference them directly without actually creating an object from your class.

Attached is a little app to show - hope it helps.