It's a bit tricky to define the absolute best practice for database access in OOP.
You've hit the nail on the head that there are a lot of factors to consider:
- how are configuration parameters handled?
- is the app multi-threaded? do you need database connection pools?
- do you need database portability (ie: do you use different DBs in dev versus production? are you concerned about vendor lock-in with one DB? Are you distributing the app the other users who may be using a different db?)
- are you concerned with securing your SQL statements, or centrally enforcing other access permissions?
- is there common logic involved when performing some inserts and updates that you'd rather not duplicate everywhere a particular table is touched?
Because of this, many OOP folks gravitate to an ORM framework for anything but the simplest cases. The general idea is that your application logic shouldn't need to talk to the database directly at any point: isolate your business code from the actual persistence mechanism for as long as possible.
Instead, try to design your application so that your business logic talks to a model layer. In other words, have model objects in the system that encapsulate and describe your business data. These model objects then expose methods for obtaining and saving their state into the database, but your logic doesn't need to care about that.
For example, say you have a concept called "Person" in your system. You'd probably model this as a class with some properties. In pseudo-code:
Person:
- first_name
- last_name
Your actual code in the system is then only concerned with instantiating and using Person objects, not with obtaining DB handles or writing SQL:
p = Person.get(first_name='Joe')
p.last_name = 'Bloggs'
p.save()
In an object-oriented world, you'll find that your business logic code becomes cleaner (and shorter!), easier to maintain, and much more testable.
Of course, you're right in that this means you need to now go off and build a database back-end that translates that Person class to one or more tables in your relational database. This is where using an ORM framework comes in handy. In Python, people use Django and SQLAlchemy. I'll let others indicate what folks use in C# (I'm not a C# developer, but you did tag your question OOP, so I'm going for the generic answer here, rather than C# specific).
The point, though, is that the ORM framework puts all the DB access in a single set of classes in the code, so that the DB access, configuration and pools are handled in one place... no need to instantiate them all over the application. What you use "where you need it" is the model object.
Of course, if your app is very simple and you want just a raw DB handle, then I do recommend the dependency injection approach others have listed.
Hope that helps.