Data Access Using Delegates, Part 1

Data access layer code is almost universally the same--you get a connection string, you open a connection, you execute your query, you turn your data into objects, you do some clean up of objects you created. You have probably written hundreds of components to do this.

One of my biggest annoyances with data access code is that it is usually decentralized, with each DAL opening and closing the connection, creating and destroying objects, etc. Maybe I want to go back and add in logging to each component and be able to turn it on and off. I can't really do that if I have hundreds of tiny DALs. Also, how do I know that all the developers on the project have been diligent about closing and disposing of all their objects? I don't want to have to verify this is true across everyone's components.

These exact concerns led me to a different design approach--one that would centralize the tedious, repetitive pieces into a component that could be easily managed, while providing all the necessary functionality and efficiency to the developers. This is not an easy task because the simplest approach to solve a data problem (datatables, for example) are typically not the fastest.

My requirements were to design a solution that would:
1) Centralize connection string management, so connection strings existed in exactly one place
2) Centralize object creation, and closing and disposing of objects, to ensure this always occurs
3) Centralize significant events, such as opening a connection or executing a command, to allow capturing of information for troubleshooting or profiling
4) Decentralize necessary functions, such as adding parameters to a stored procedure call
5) Allow for efficient data retrieval, rather than require an expensive retrieval method to meet the above criteria


Looking at these requirements, it didn't take long to realize that delegates were the best solution. Delegates allow efficient calls to be made to functions outside the centralized code, while providing a means to keep all the important code within a main data access component.

I will discuss delegates briefly, then jump into how you can use this approach to simplify and enhance your data access components.