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 - Working Close to the Metal

So far in my review of the new DAL2 I have focused on the features designed to improve developer productivity - i.e. the Repository.  In this post I am going to step back and look at other features which mimic the existing DAL+, and provide the developer with a sense of working “close to the metal”.


In earlier versions of DotNetNuke, we used the Microsoft Data Applications Block – in particular the SqlHelper class as a very lightweight abstraction over ADO.NET.  This class is a utility class that enables developers to avoid the need to manage their own ADO.NET connections, commands and readers (for example see Listing 1).

Listing 1: The DAL+ method ExecuteNonQuery prior to 7.0


With the introduction of the new DAL2, we have taken the opportunity to remove our dependency on SqlHelper.  This was done in a two step process.  First, all the DataProvider methods were converted to use the DAL+, which meant that the existing core SqlDataProvider class only had the DAL+ methods left - ExecuteNonQuery, ExecuteScalar, ExecuteReader etc.  These methods were then modified to use the new PetaPocoHelper class, as shown in Listing 2.

Listing 2: The DAL+ method ExecuteNonQuery in 7.0


The PetaPocoHelper class is similar to the SqlHelper class and provides 4 methods:

  1. ExecuteNonQuery
  2. ExecuteReader
  3. ExecuteScalar
  4. ExecuteSQL

Listing 3 shows the ExecuteNonQuery method in detail.

Listing 3: The PetaPocoHelper ExecuteNonQuery method


The main thing to note here is that PetaPoco does not have the concept natively (unlike SqlHelper) of stored procedures so we have to generate dynamic sql from the stored procedure passed in.  Basically, if the CommandType is CommandType.StoredProcedure we generate sql something like:

;Exec SPName, @0, @1, @2 …

The bottom line though is that ALL data calls in the core are now processed through PetaPoco, through the PetaPocoHelper class, and if you like to work really “close to the metal” then you can use this utility class instead of SqlHelper. (While no longer required by the core we are still including the Microsoft Data Applications Block in the core package to support 3rd party usage).


We have seen the IDataContext interface in previous articles.  It provides the Unit Of Work abstraction (similar to Entity Framework’s DataContext and DbContext classes) over the Database for the DAL 2. 

The IDataContext interface provides 4 methods that allow you to get “close to the metal”.

  1. Execute
  2. ExecutyQuery of T
  3. ExecuteScalar of T
  4. ExecuteSingleOrDefault of T

As they are methods of IDataContext, you don’t need to pass any connection string, so they are very similar to the methods in PetaPocoHelper with one major exception - there is no ExecuteReader that returns an IDataReader - PetaPoco doesn’t actually expose data readers, but converts a reader internally into an IEnumerable of T.  (Note: In order to provide the ExecuteReader method in the PetaPocoHelper, we had to create an extension method on PetaPoco’s Database class).  You can think of these methods as DAL ++.

So even without the Repository - we can still replace the CBO.  Lets look at an example - DNNRoleProvider.GetUserRoles().  This is shown in Listing 4.

Listing 4: GetUserRoles using the DAL +


This method calls one of two methods on the DataProvider class depending on the value of the includePrivate parameter - see Listing 5.

Listing 5: GetUserRoles & GetServices methods of DataProvider


So lets change the GetUserRoles method in Listing 4 to use the IDataContext method ExecuteQuery - see Listing 6.

Listing 4: GetUserRoles using ExecuteQuery


Notice now that we don’t have to create any methods in a “DataProvider” class - ExecuteQuery essentially combines the CBO.FillCollection method and the DAL + ExecuteReader method into a single call, and anything that reduces the amount of work we have to do as developers is a plus.

Similarly, ExecuteSingleOrDefault combines the CBO.FillObject method and the DAL+ ExecuteReader method into a single call. (Note: The IDataContext methods were still under review and are not all included in the CTP, but they should be in the next pre-release).

I took this detour to demonstrate that while the DAL 2 includes the productivity gains of a simple Repository, we have not forgotten those developers who prefer to work in SQL and who would like to stick with their stored procedures and develop “close to the metal”.

This blog is cross-posted from my personal blog.


There are currently no comments, be the first to post one.

Comment Form

Only registered users may post comments.


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Daniels (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