Introduction
The
IRepository<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 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.
public interface IRepository<T> where T : class
{
void Delete(T item);
void Delete(string sqlCondition, params object[] args);
IEnumerable<T> Find(string sqlCondition, params object[] args);
IPagedList<T> Find(int pageIndex, int pageSize, string sqlCondition, params object[] args);
IEnumerable<T> Get();
IEnumerable<T> Get<TScopeType>(TScopeType scopeValue);
T GetById<TProperty>(TProperty id);
T GetById<TProperty, TScopeType>(TProperty id, TScopeType scopeValue);
IPagedList<T> GetPage(int pageIndex, int pageSize);
IPagedList<T> GetPage<TScopeType>(TScopeType scopeValue, int pageIndex, int pageSize);
void Insert(T item);
void Update(T item);
void Update(string sqlCondition, params object[] args);
}
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<TScopeType>
- GetPage
- GetPage<TScopeType>
- Object methods
- GetById<TProperty>
- GetById<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.
public IList<TaskInfo> GetTasks()
{
IList<TaskInfo> tasks;
using (IDataContext db = DataContext.Instance())
{
var rep = db.GetRepository<TaskInfo>();
tasks = rep.Get().ToList();
}
return 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.
SELECT
[dnn_Tasks].[ID],
[dnn_Tasks].[Name],
[dnn_Tasks].[Description],
[dnn_Tasks].[IsComplete]
FROM [dnn_Tasks]
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 our TaskInfo class and a ModuleID column to the Tasks table.
[TableName("Tasks")]
[Scope("ModuleID")]
public class TaskInfo
{
[ColumnName("ID")]
public int TaskID { get; set; }
public int ModuleID { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public bool IsComplete { get; set; }
}
We can then use the Get overload as shown below.
public IList<TaskInfo> GetTasks(int moduleId)
{
IList<TaskInfo> tasks;
using (IDataContext db = DataContext.Instance())
{
var rep = db.GetRepository<TaskInfo>();
tasks = rep.Get(moduleId).ToList();
}
return tasks;
}
which results in the following SQL.
SELECT
[dnn_Tasks].[ID],
[dnn_Tasks].[ModuleID],
[dnn_Tasks].[Name],
[dnn_Tasks].[Description],
[dnn_Tasks].[IsComplete]
FROM [dnn_Tasks]
WHERE [ModuleID] = @0
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.
public IPagedList<TaskInfo> GetPageOfTasks(int pageIndex, int pageSize)
{
IPagedList<TaskInfo> tasks;
using (IDataContext db = DataContext.Instance())
{
var rep = db.GetRepository<TaskInfo>();
tasks = rep.GetPage(pageIndex, pageSize);
}
return tasks;
}
The above code shows an example of how to use these methods and the generated SQL is shown below.
SELECT COUNT(*)
FROM [dnn_Tasks]
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ID) peta_rn,
[dnn_Tasks].[ID],
[dnn_Tasks].[ModuleID],
[dnn_Tasks].[Name],
[dnn_Tasks].[Description],
[dnn_Tasks].[IsComplete]
FROM [dnn_Tasks] ) peta_paged
WHERE peta_rn > @0 AND peta_rn <= @1
PetaPoco handles the generation of the appropriate “paging” SQL and the repository returns an IPagedList<T>. This collection interface and its associated concrete PagedList
implementation are new (in DotNetNuke 7) custom collections to support this API. (Note: These collections are based on the ASP.NET MVC Paged List Helper created by Rob Conery.
Essentially the IPagedList<T> interface just extends the core .NET IList<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<T> interface is shown below.
public interface IPagedList<T> : IList<T>
{
bool HasNextPage { get; }
bool HasPreviousPage { get; }
bool IsFirstPage { get; }
bool IsLastPage { get; }
int PageCount { get; set; }
int PageIndex { get; set; }
int PageSize { get; set; }
int TotalCount { get; set; }
}
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 above could be replaced by the following:
tasks = rep.Get().InPagesOf(pageSize).GetPage(pageIndex);
As the IPagedList<T> inherits from IList<T> and PagedList<T> inherits from List<T> - 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.
public TaskInfo GetTask(int id)
{
TaskInfo task;
using (IDataContext db = DataContext.Instance())
{
var rep = db.GetRepository<TaskInfo>();
task = rep.GetById(id);
}
return task;
}
An example of using the GetById method is shown above and the generated SQL is shown below.
SELECT
[dnn_Tasks].[ID],
[dnn_Tasks].[ModuleID],
[dnn_Tasks].[Name],
[dnn_Tasks].[Description],
[dnn_Tasks].[IsComplete]
FROM [dnn_Tasks]
WHERE [ID] = @0
The Find Methods