In Part 1 of this series we started with a basic Data-Reader / SQL Connection/Command pattern and illustrated how it is possible to abstract the parsing of the Data Reader into a standalone object that can be fully unit tested in isolation of the calling code. In Part 2 of the series we made a very simple optimisation to the “DataReader” convertor and updated the tests to capture/verify the changes.
In part 3 of the series we put this all together into a repository pattern to create a reusable and testable data access layer. The first step is to create an interface for the repository.
The intent implied by the interface is that "Get" will be responsible for returning an enumerable list of a generic. To do this we pass in an implementation of IQuery and an implementation of IConvertDataReader for the generic we are to return. We already have an implementation of IConvertDataReader
The final piece of the jigsaw is the implementation of the repository interface, in this instance for SQL but it could be any data provider that returns an implementation of IDataReader. A basic implementation of the SQL repository is shown below:
We now have a repository object that can be used against any SQL database to return a list of any type of object that can be populated using a standard SQL SELECT statement; the code below shows how to put this together.
To understand the power of this pattern, consider the following code that is all that is needed to update the previous code to return a different list of customers; in this case all those with the first name of "Paul".
Part 4 shows how the code can be modified so it can be extended to handle different "query types".