I continue my deep dive into DotNetNuke 7’s new DAL 2 with a look at the features that make up the repository component.
The IRepository of T interface can be considered the core of the new DAL 2. While the Execute methods of the IDataContext interface provide a DAL + like API, the benefits of the new API are really found when using the repository. The Repository Pattern is a common design pattern in modern data access layers. Martin Fowler’s definition of the pattern [1] is:
Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.
The pattern at its simplest provides simple CRUD (Create, Retrieve, Update and Delete) methods. In the DotNetNuke implementation there are a few extra overloads to provide flexibility. Listing 1 shows the complete Interface.
Listing 1: The IRepository Interface
|
 |
The Get methods
There are six methods that get (or retrieve) items from the database. Four of these methods return collections and 2 return single items.
- Collection methods
- Get
- Get of TScopeType
- GetPage
- GetPage of TScopeType
- Object methods
- GetById of TProperty
- GetById of TProperty, TScopeType
These six methods are in pairs – 2 Get methods which return a collection, 2 GetPage methods that return a “page” of items and 2 GetById methods that return a single item. The 2 methods differ in each case by the addition of a TScopeType type parameter and parameter.
Let’s first take a look at the two Get methods. Listing 2 shows the use of the parameter-less overload to retrieve all of the tasks from the database.
Listing 2: Using the Get method to Retrieve a List of Tasks
|
 |
The pattern should be familiar by now. The repository is created by the data context and then the Get method is executed. This returns an IEnumerable which is then converted to a List and returned.
Under the covers the repository together with PetaPoco generates the SQL necessary to fetch all the tasks. PetaPoco generates a SQL statement to return every column in the table.
Listing 3: The Generated SQL from the Get method
|
 |
The other overload is used in conjunction with the Scope Attribute. In fact if the type of the repository does not have the Scope attribute then an exception is thrown. We can see how this works if we add a ModuleID property to the TaskInfo class (Listing 4) and a ModuleID column to the Tasks table. We can then use the Get overload as shown in Listing 5 resulting in the generated SQL shown in Listing 6.
Listing 4: Adding Module Scoping to the TaskInfo class
|
 |
Listing 5: Using the Get (by scope) Overload
|
 |
Listing 6: The Generated SQL for the Get (by scope) method
|
 |
As can be seen the generated query is parameterized reducing the risk of SQL injection attacks.
The GetPage methods
The other two methods (GetPage) that return a collection of items from the database are similar to the Get methods. Both methods return a “page” of data, and have two additional parameters - compared with their Get counterparts - the page index and the page size. Listing 7 shows an example of how to use these methods and Listing 8 shows the generated SQL.
Listing 7: Using the GetPage method
|
 |
Listing 8: The Generated SQL for the GetPage method
|
 |
PetaPoco handles the generation of the appropriate “paging” SQL and the repository returns an IPagedList of T. This collection interface and its associated concrete PagedList of T [2] implementation are new (in DotNetNuke 7) custom collections to support this API.
Essentially the IPagedList of T interface just extends the core .NET IList of T collection to add properties that provide metadata about the page of data, as well as the collection as a whole. For example, there are properties which indicate whether there is a previous page or a next page, and there are properties which indicate the number of pages in the complete data set, as well as the total no of items.
The IPagedList interface is shown in Listing 9. The new API also provides methods which allow developers to create paged lists from any IEnumerable collection. In this case the complete set of data is returned from the database and the paging is done using the LINQ Skip and Take methods. For example the call to GetPage in Listing 7 could be replaced by the following:
In fact the RepositoryBase of T class uses this logic internally to return a page of data from the cached collection – see Listing 10.
Listing 9: The IPagedList of T Interface
|
 |
Listing 10: Implementation of the RepositoryBase of T GetPage method
|
 |
As the IPagedList inherits from IList and PagedList inherits from List - both can essentially be used wherever their base class is used and both implement IEnumerable so they can be used with LINQ.
The GetById methods
In addition to the methods that return collections there are two methods that return single objects. Again these methods only differ as to whether they are scoped or not. By default the convention is that the column name for the property used as an “id” is “ID”, but this can be controlled by the PrimaryKey attribute.
Listing 11 shows an example of using the GetById method and Listing 12 shows the generated SQL.
Listing 11: Using the GetById method
|
 |
Listing 12: The Generated SQL for the GetById method
|
 |
[1] For more on the repository pattern see - http://www.martinfowler.com/eaaCatalog/repository.html
[2] IPagedList of T and PagedList of T are heavily based on the ASP.NET MVC Paged List Helper created by Rob Conery - http://blog.wekeroad.com/2007/12/10/aspnet-mvc-pagedlistt/