How to encapsulate database access?

Question

I am developing a transactional application in .NET and would like to get some input on how to properly encapsulate database access so that:

• I don't have connection strings all over the place
• Multiple calls to the same stored procedure from different functions
• or WORSE, multiple stored procedures that are different by a single column

I am interested in knowing if using an ORM like NHibernate is useful, as it may just add another layer of complexity to a rapidly changing data model, and artifacts need to be produced on a tight schedule.

I am more interested in methods or patterns OTHER than ORM packages.

Answer 1

There are at least two widely accepted design patterns used to encapsulate data access:

• repository (DDD)
• DAO (Data Access Object)

For the sake of completeness I suggest you these books:

• Patterns of Enterprise Application Architecture (Fowler)
• Domain Driven Design (Evans)

Answer 2

If, as it appears, this is an important project and the DAL is a major risk factor, get someone involved who has done it before. You're exactly right that there are too many ways to run off the rails by trying to get this right the first time without solid experience.

There are any number of patterns for accomplishing this, but I'd look for someone who has a simple set of well-defined patterns they are fully comfortable with.

Answer 3

as stated above, check out the repository and unit of work patterns. the books by fowler and evans are highly recommended. so is karl seguin's reader which gave me a cooler introduction to the just mentioned books. grab it at http://codebetter.com/blogs/karlseguin/archive/2008/06/24/foundations-of-programming-ebook.aspx

Answer 4

As Java Developer, i could suggest to read about jdbc templates, despite of it s'n not .NET you could learn how Spring framework encapsulates data access tier and get some ideas.