A few months ago, I had an assignment for a multinational beverage company to implement a publication system for their products across Europe. Along with a few other consultants and managers from another unit in my company, they had decided to go for a SharePoint solution as this seemed to meet all business requirements: centralized master data management with the ability to generate XML – and Word (or other human friendly) documents. I got on the project a few months later only to discover that the suggested approach could never work: the entire data model (entirely relational and normalized) was made with many SharePoint lists and the data was retrieved with JSOM. With a limited budget and a tight deadline, the best thing to do was to make a proper design with a solid architecture and minimal amount of code and complexity. The best alternative here was to build a custom SharePoint solution with a custom ORM layer. I considered LINQ 2 SharePoint but in this project where nothing was certain and everything changed rapidly, I quickly decided that a custom ORM layer was the most suitable. If I had the choice to start from scratch, I definitively would have chosen to go for the combination EF + BCS. As this MSDN article explains, the repository pattern is pretty much the way to go when it comes to building a generic, high quality and lightweight data access layer. This also counts for SharePoint solutions, as Chris O’Brien confirms in an old article. Although many articles cover this topic, I never was quite satisfied with the technical solution that was provided.
In what follows is a very simple yet powerful way of creating a generic data access layer for SharePoint (both CSOM and SSOM).
Let’s start with a generic base class that has the core functionality of the entire ORM layer.
This code shouldn’t be too difficult to read: this method takes three parameters:
- The name of the list
- A CAML Query
- An delegate that will be executed for each item that is retrieved from the list with the specified query.
In simple words, all this method does is the following: it queries the specified list with the specified CAML Query on the specified site, and it will invoke the mapper delegate for each item that is retrieved, resulting in a list of objects of type T. The magic of this method lies in the last parameter: this delegate serves as a mapper between a SPListItem object and an instance of type T. As this is a generic method, we need to create a class that inherits from this generic class.
If you look at the GetCountries method, you will notice that the base method is called with three parameters. The interesting parameter here is the mapper delegate. This is the code:
This method will be invoked for each SPListItem that was found in the result set. The delegate takes one parameter (of type SPListItem) and the return type is T – in this case: Country. This implies each SPListItem will be converted to an instance of type T using this method. There is one downside to this approach that still bothers me: it is sensitive to typo’s in the field names. The mapper will check if the current list item has a certain field in its array, and if it doesn’t, it will return a null or default value so you don’t have any trace of missing values (unless you build something extra on top of this of course). On the other hand, it is fail safe: if the developer makes a mistake in the mapping between a field name in SharePoint and the specified field name in the resources file, the application won’t crash. Worst case scenario here is that data will be missing in the mapped objects. The same approach can also be used for the client-side object model, it works equally well although you have to consider which data elements you want to include in the callback.
