Designing a data access layer

Goals:
1. Have the rest of the application interact with the data sources only through an interface
2. Make it so the rest of the application doesn’t have (even can’t) to know how the data sources work
3. Make it easy to change the DAL without affecting anything else
4. Keep all of the relevant information together where possible and helpful – ie. sql statements
5. Do as little work as possible to get data persisted – write helper classes and reuse code where possible
6. Keep the internals of the DAL as loosely decoupled as possible
7. The application should never need to query more than one data source to get a complete data object
8. Seperate getting the data and creating the data objects where possible so queries that return the same information can be processed by the same mapping object

I only want my classes to be able to communicate with the DAL using the .NET data objects that they use to communicate with each other already. The rest of the program shouldn’t know whether the data object comes from a web service, a database, an XML file or some exotic source it’s never heard of.

My problems:
1. If I make one interface per data source, the interface class gets huge.
2. If I have lots of different interface classes, then accessing the datasources gets complicated.
3. I end up writing the same code over and over again. It’s mostly just mapping object properties to database fields. It’s incredibly tedious and time consuming.
4. The mappings between the same data type in data sources are often the same or similar, but the processing code is tied to the one type of source so the code’s not reusable.
5. Automating mappings tend to be unreliable, fragile and performance intensive

References:
Writing DAL on the MSDN

Posted on 02 Nov 04 by Helen Emerson (last updated on 02 Nov 04).
Filed under Programming