Design Patterns for Interfacing with Relational Databases?

Question

Does anyone know of any design patterns for interfacing with relational databases? For instance, is it better to have SQL inline in your methods, or instantiate a SQL object where you pass data in and it builds the SQL statements? Do you have a static method to return the connection string and each method just gets that string and connects to the DB, performs its action, then disconnects as needed or do you have other structures that are in charge of connecting, executing, disconnecting, etc?

In otherwords, assuming the database already exists, what is the best way for OO applications to interact with it?

Thanks for any help.

Answer 1

Everything you will identify by googling for DAL describes a design pattern. Seems like standing in the middle of the forest and asking to see a tree. There are dozens if not thousands.

Here's a quote from a book I'm reading to start with for looking for resources.

... it is impossible to discuss ORM without talking about patterns and best practices for building persistence layers. Then again, it is also impossible to discuss ORM patterns without calling out the gurus in the industry, namely Martin Fowler, Eric Evans, Jimmy Nilsson, Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides, the last four of whom are known in the industry as the Gang of Four (GoF). The purposes of this chapter are to explain and expand some of the patterns created by these gurus and to provide concrete examples using language that every developer can understand.

Answer 2

I personally would hate to work with a database without an ORM. NHibernate is preferable but iBatis is also an option for existing databases (not to say that NH can't handle existing databases).

Answer 3

In general, the best way for OO applications to interface with a relational database is through an ORM; while this isn't a design pattern per se, it's a type of tool that has a specific usage pattern, so it's similar enough. Object Relational Mapping (ORM) tools provide a mapping between a database and a set of objects in memory; usually, these tools provide means for managing things such as sessions, connections, and transactions. A good example of an ORM that works fantastically well would be Hibernate (NHibernate on .NET).

Answer 4

There are a few patterns you can use:

• Repository Pattern
• Active Record Pattern

Answer 5

In my experience it is best to have no SQL statements at all (most ORMs will allow that), and it is best not to have any knowledge of connection details (connection string, etc). Even better if you can have the exact same piece of code working with any major db vendor.

Answer 6

I recommend the book Patterns of Enterprise Application Architecture by Martin Fowler for a thorough review of the most common answers to these questions.

Answer 7

POEAA has a wealth of knowledge on the issue if you intend to roll your own.