Thoughts from the Wet Coast

The musings of an ASP.NET Developer from Canada's We(s)t Coast

DAL 2 - A New DotNetNuke Data Layer for a New Decade

Category: DotNetNuke
Tags: dal DNN7 petapoco
Last Modified: May 2 2017
Oct 9 2012

DotNetNuke has essentially used the same data access layer since its earliest versions.  This data layer uses an abstract DataProvider class together with a concrete SqlDataProvider class. 

In theory this model supports the ability to use other database systems by building other concrete DataProvider implementations.  However, in practice this is actually not very practical for a number of reasons, the main reason being the need to create database scripts for each concrete provider.  As a result, there really aren’t any other database implementations available.

The DataProvider/SqlDataProvider model, while quite easy to work with, requires developers to write quite a lot of code.  Let’s consider the simple case of a Task object (and the equivalent Tasks database entity). A module developer would need to provide the following
  • A TaskInfo model class which would model the properties of the Task object
  • A TaskController repository class which would provide the business layer methods (these may or may not match the DataProvider CRUD methods)
    • CreateTask 
    • GetTasks
    • GetTask
    • UpdateTask
    • DeleteTask
  • A DataProvider abstract provider class, with simple CRUD methods
    • CreateTask
    • GetTasks
    • GetTask
    • UpdateTask
    • DeleteTask
  • A SqlDataProvider concrete provider class, with implementations of the CRUD methods (e.g)
  • A SQL script file to contain
    • Sql to create the Tasks Table
    • Stored Procedures to match each CRUD method

In addition to the CRUD methods both the DataProvider class and SqlDataProvider class have ~25 lines of plumbing code to make sure everything works within DotNetNuke.  None of this is particularly difficult, but it is tedious and repetitive.

The DAL+ - A Simplified Data Layer

Several years ago the DAL+ methods were added to the core to try and improve this situation. The principle behind the DAL+ methods is that the core will provide a set of generic data access methods (ExecuteNonQuery, ExecuteReader and ExecuteScalar).

If 3rd party module developers use the DAL+ they effectively no longer need to create their own DataProvider/SqlDataProvider classes, as they can now call the generic methods in the core.   For example, the methods in TaskController would call the core DAL+ generic methods.


Developers do however still need to provide all the stored procedures and schema creation SQL.

So regardless of whether developers use the original DAL or the newer DAL+, the problem is that developers need to do a lot of work to persist their objects in the Data Store.

Enter Object Relational Mapping Frameworks (OR/Ms)

About 5-6 years ago a number of Object Relational Mapping (OR/M) frameworks were introduced on the .NET platform, among the most popular being NHibernate (an Open Source .NET port of the popular java framework Hibernate) and Entity Framework (an Entity Mapping Framework from Microsoft). 

The goal of these frameworks is to provide a way to automatically “map” objects to relational entities.  Maps are created in a number of ways, usually at design time, but in some scenarios maps can be created at run time.  Developers then work just with objects and the OR/Ms take care of persisting the objects to the database, and retrieving the objects from the data layer. 

These frameworks can be quite powerful but they have two major drawbacks:

  1. They can be quite difficult to use due to the complexity of the framework.
  2. Although there are ways to mitigate the problem they do suffer from poorer performance than “close to the metal” solutions, due to the extra layers of abstraction.

A number of dotnetnuke module developers have started to use OR/Ms - Entity Framework in particular.


The biggest problem with most ORMs is that while for the most part they do what they claim, the cost is often in complexity and performance.

As a result of this there has been development recently on a series of lighter weight solutions dubbed “micro-OR/Ms”. Examples in the .NET space of micro-OR/Ms include Dapper, Massive, Simple.Data and PetaPoco.

These micro-OR/Ms aren’t really OR/Ms at all – they don’t do anything to “fix” the Object Relational Impedance mismatch, as they assume that objects relate in a 1:1 relationship with database tables.   In a joking way, Mark Rendle creator of Simple.Data stated:

“It’s not an OR/M. It looks a bit like one, but it doesn’t need objects, it doesn’t need a relational database, and it doesn’t need any mapping configuration. So it’s an O/RM without the O or the R or the M. So it’s just a /.”

What these micro-OR/Ms do well though, either through reflection or through the use of dynamics, is to simplify the deserialization and serialization of the objects.

In a significant way they can be considered to be the modern replacement of SqlHelper and DotNetNuke’s CBO class – light-weight and close to the metal database abstractions but providing a more modern type-safe interface.

DAL 2 - A New Data Layer

We have been talking about introducing a more modern data layer for a number of years and in 7.0 we have done just that.  We chose Peta-Poco because at the time of initial development, it was the most complete of the offerings.  It provides the following:

  • A close-to-the-metal option to execute any sql - basic SqlHelper like functionality.  This would allow us to replace SqlHelper with a PetaPocoHelper class, which we have done in the core.
  • Fetch (retrieve) methods that can take a parameterized SQL snippet as a parameter e.g.. “WHERE PortalID = @0”, thus reducing the overhead of creating and managing stored procedures.
  • Page methods that can auto-generate the sql required to return a page of data from the database.
  • Simple methods to Insert, Update and Delete “objects” in the database, with no need to write any sql.
  • An IMapper interface which, while it doesn’t support cross-table mapping, will support things like an object qualifier (or table prefix)
  • Transaction support

Listing 1 shows an example of how the new Repository in the DAL2 framework can be used to insert (add) a new task.  This code is the only code that a module developer needs to write - no DataProvider, no SqlDataProvider and more importantly no sql.  The only sql that must be created is the sql to create/update the schema (tables) in the database.

Listing 1: Inserting an item into the Data Store


Or maybe I want to get a Page of Tasks to display in a grid.  To do this we can use the GetPage method of the Repository.

Listing 2: Getting a Page of items from the Data Store


Again this is the only code that you need to write to get a page of tasks.

But maybe there is a custom query you need to make.  Maybe you need to find all the completed tasks in any module instance.  Then the Find method is for you.

Listing 3: Using Find to create a custom Query


This was just an appetizer. 

There are a number of classes in the new DAL 2 API and over the course of the next couple of weeks I will blog on how to use them in more detail, and for those of you who are attending DNN World next month, I will be presenting a deep-dive session into the DAL 2 - "DAL2 - A New DotNetNuke Data Layer for a New Decade.


The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.