Database access from everywhere in application

问题:

If I wanted to access a database in Delphi, I could add a datamodule to a project, configure it from my mainform and then access it anywhere in the application; a reference would be stored in a global variable.

I know that in C# and other more modern OO languages global variables are frowned upon. So how can I access my database from where I need it? The biggest problem I have is the configuration: location, user, password, etc. are unknown at design time.

I now have a db-class and make a new instance when I need it, but then I would have to store those settings in some globally accessible thing, and I have simply moved the problem.

What's the standard solution?

Thanks, regards, Miel.

回答1:

It seems to me that you need to create an appropriate object (containing the connection or similar), and pass that instance to each object requiring access (see dependency injection)

This is different from using singletons. By using this mechanism, it'll free you from the dependency on one object and (perhaps a more compelling reason in this instance) allow you to perform testing by injecting mock objects or similar in place of the originally-injected database accessor object. I would definitely shy away from the singleton mechanism in this scenario.

回答2:

I always use the singleton pattern. As for configuration, look at the System.Configuration.ConfigurationManager class which allows you to read settings from your project's app.config/web.config file.

回答3:

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.

回答4:

I actually use a repository class that takes in the db information in its constructor and have the classes that need it get it passed in. I actually use an Inversion of Control (IOC) tool to inject that values in.

回答5:

You could store the user information in a flat file somewhere, then read / write to it from your db-class

This way you won't duplicate the settings in your code, but the user can still modify the settings.

回答6:

SubSonic is the "Swiss Army knife" for object relational mapping, and offers the ability to execute stored procedures and return results to List. You can have it up and running within a half hour.