I have a c# dll with a number of static utility classes. they all need database access, and at the moment each class has it's own connection. this is getting to be a hassle, and I'd like a single "provider" of connections, so a class could just do something like ConnProvider.Query(...) where ConnProvider is a static class accessible by all the utility classes. I suppose ConnProvider would have to manage a connection pool of some sort, but I'm not entirely sure if this is the right way to implement this and if so how the internals would look. am I on the right track here? this isn't web-based, thread-safety isn't essential (but would be nice for the future), and there are only a handful of apps/machines that will be using this dll, if that helps simplify it at all.
Is it possible in your situation to have all of the helper classes to subclass from a ConnectionBase that has the connection in it?
Have your "ConnProvider" create new connections for each query and let ADO.NET and your RDBMS manage the connection pooling. They are already set up and optimized to do so on your behalf.
You shouldn't be writing your own connection pool. The framework's one is quite good enough.
Any connection provider utility you write should really just be creating the connection using whatever configuration information you've got. Essentially it would just encapsulate the query string.
Whenever you do a query, call into the provider to create the connection, do the work, and close the connection. Let the framework handle pooling the physical connections.
Don't try to handle connection pooling yourself. .NET is surely better at it than you'll ever be. You could work this out as some sort of singleton-pattern db-class for the connections.