New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

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

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.

This blog was cross-posted from my personal blog.


Gail Lampinen
In your example of inserting an item, if the repository is pointing to an underlying SQL Server table, what if the insert fails? I used this in a try/catch block, the insert failed, but no error was ever raised. How can one catch insert failures? I notice there is no return integer of either ID or error code. Are error codes generated? Can you give an example of error trapping?
Gail Lampinen Friday, January 10, 2014 12:30 PM (link)
Todd Menier
Any plans to directly support asynchronous I/O (of the async/await variety) in future versions of DNN? If so, I just wanted to point out my async port of PetaPoco:

It's stable and actively being used in a number of live sites, including a couple of my own. I'm just trying to get the word out to larger projects that are currently using PetaPoco, and DNN certainly fits the bill. Thanks!
Todd Menier Tuesday, April 8, 2014 10:29 AM (link)
Jacques Woolston
@Todd Menier - I am super interested in getting DAL2 using AsyncPoco

This is something I will look at in the new year and contribute to DNN.
Jacques Woolston Thursday, December 15, 2016 10:53 AM (link)
Michael Durthaler
The images don't show up :) But I'm familiar enough with having had to write abstracts, providers, info classes (models) etc. that it's etched in my brain.

Why would images not show up? It would still be helpful.

Also, is there a way to get the whole series of your co-posted DAL blogs? I want to read them in sequence and not have to google for everything past your third one.


Mike Durthaler
Michael Durthaler Monday, June 26, 2017 9:12 PM (link)

Comment Form

Only registered users may post comments.


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Davies (3)
Alex Shirley (10)
Andrew Hoefling (3)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (37)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (213)
Chris Paterra (55)
Clint Patterson (108)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (181)
Daniel Valadas (48)
Dave Buckner (2)
David Poindexter (12)
David Rodriguez (3)
Dennis Shiao (1)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (80)
Francisco Perez Andres (17)
Geoff Barlow (12)
George Alatrash (12)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Kelly Ford (4)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matt Rutledge (2)
Matthias Schlomann (16)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (40)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Oliver Hine (1)
Patricio F. Salinas (1)
Patrick Ryan (1)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Sacha Trauwaen (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott Schlesier (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Steven Fisher (1)
Tony Henrich (3)
Torsten Weggen (3)
Tycho de Waard (4)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (180)
William Severance (5)
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out