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 – DataContext Deep Dive

So far I have done a relatively high level review of the DAL 2 API. In this blog article I will start to take a deep-dive into the components that make up the API, beginning with the DataContext.

The IDataContext interface and the PetaPocoDataContext concrete implementation act as the entry point into most aspects of the DAL 2 API. While implemented as an Interface there is no expectation that any other implementation will be created, but the interface allows us to mock a concrete implementation for Unit Testing purposes. Listing 1 shows the complete IDataContext Interface.

Listing 1: The IDataContext Interface


If you are familiar with Linq 2 Sql or Entity Framework the PetaPocoDataContext concrete implementation is like the DataContext, ObjectContext or DbContext objects in those frameworks.

In addition to the IDataContext interface and the PetaPocoDataContext concrete implementation there is a Factory class – DataContext which provides two Instance overloads. These Instance overloads create and return PetaPocoDataContext objects. The parameter less overload returns a PetaPocoDataContext object which represents the current (default) database connection identified in the data provider configuration, while the overload with a single parameter returns a PetaPocoDataContext object which represents a database defined by a named connection string.

Listing 2: DataContext class’s Instance methods


Notice that both methods create new PetaPocoDataContext instances, rather than returning a Singleton instance. Each PetaPocoDataContext basically represents an ADO.NET Connection and we need to let ADO.NET manage those extensions.

As the second overload provides access to a specific named connection string this allows 3rd party developers – in principal – to use a different database than the core, while retaining all the benefits of the core API.

The PetaPocoDataContext class implements IDisposable so we can use C#’s using statement (Listing 3) to manage the context.

Listing 3: Getting the default IDataContext


The Execute Methods

The IDataContext interface provides 4 Execute methods that enable the developer to implement DAL+ like Data Access. These are summarized below

  • Execute
  • ExecuteQuery of T
  • ExecuteScalar of T
  • ExecuteSingleOrDefault of T

All the Execute methods have a similar signature. The first parameter defines the type of Command which can be a CommandType.StoredProcedure or CommandType.Text. The second parameter is a string. If the first parameter is CommandType.StoredProcedure then the second parameter is the name of a stored procedure, and if the first parameter is CommandType.Text then the second parameter is dynamic SQL e.g. “SELECT * FROM dnn_Tasks”.

These methods can be divided into two groups; methods that return either an object or a collection of objects, and methods that do not return an object.

The first group includes ExecuteQuery of T and ExecuteSingleOrDefault of T - these two methods are equivalent to using the FillCollection and FillObject methods of the core CBO class – except they don’t require you to pass an IDataReader.

ExecuteQuery of T Method

This method could be used as follows to fetch a list of tasks from the database - Listing 4.

Listing 4: Using the ExecuteQuery of T method


Under the covers the ExecuteQuery of T method calls PetaPoco’s Fetch of T method, so it fully supports both the table Prefix (or object qualifier) and the custom mapping attributes. This means that we can actually simplify this code as shown in Listing 5. PetaPoco will build the SELECT statement automatically using any Custom Mappings that have been defined.

Listing 5: Shorthand Form of the Call to ExecuteQuery of T


Listing 6 shows how you can execute a query with a sql condition in order to return a filtered List of objects, in this case a list of completed tasks.

Listing 6: Filtering the Data by Applying a Custom Condition


ExecuteSingleOrDefault of T Method

The ExecuteSingleOrDefault method is similar to the ExecuteQuery method except that it returns a single object rather than a list of objects. As with the ExecuteQuery method we only need to write the SQL for the WHERE clause, as PetaPoco will build the full SELECT statement using the Custom Mappings which have been defined – see Listing 7.

Listing 7: Using the ExecuteSingleOrDefault of T method


ExecuteScalar of T Method

The ExecuteScalar method and the Execute method are different from the previous two methods in that they do not support the automatic generation of a SELECT clause and they do not support custom mappings. The former is because for the most part they are not designed to be used with SELECT clauses, they are designed to be used with DELETEs, UPDATEs and INSERTs. The ExecuteScalar method returns a single value which represents the first column of the first row of the data that is returned. Usually this is either the ID of a newly inserted record or an Error code.

Listing 8: Using the ExecuteScalar of T method


Execute Method

The Execute method is like the ExecuteNonQuery method of the original DAL+. It is not designed to return anything at all so it can be used to pass SQL that does updates or deletes.

Listing 9 Using the Execute method


The GetRepository of T Method

One of the most powerful features of the DAL 2 is that it provides an implementation of the Repository Pattern [1]. The GetRepository of T Method is the entry point to this pattern as it returns an instance of IRepository of T.

Using the repository we can rewrite the example from Listing 5 as seen in Listing 10.

Listing 10: Using the Repository to get a List of Tasks


The IRepository of T interface also has methods to get filtered lists – Listing 11 shows the example from Listing 6 rewritten to use the repository.

Listing 11: Using the Repository to get a filtered List of Tasks


The next blog post will go into much more detail on the uses of the Repository.

Unit of Work and Transactions

PetaPocoDataContext implements an important design pattern – the Unit of Work [2]. This is primarily through its implementation of the I Disposable interface. This can be seen in Listing 12, where a list of tasks are retrieved from the database and then each one’s IsComplete property is set to true. These two actions are considered a single Unit of Work and are therefore carried out using the same context instance within the using statement.

Listing 12: The Unit Of Work Pattern


While the code in Listing 12 is a Unit of Work in the sense that the complete block of work is encapsulated in the using statement, there is no guarantee that the complete Unit of Work can be completed. If there is an exception in any one of the update calls before the complete set has been updated the database will be left in an unknown state.

The IDataContext interface therefore provides transaction support with three methods to manage transactions.

  • BeginTransaction
  • Commit
  • RollbackTransaction

These methods can be used to ensure a Unit of Work is either completed in its entirety or no changes are made (see Listing 13).

Listing 13: Adding Transactions to the Unit Of Work


For the most part I expect developers will use the Repository methods rather than the Execute methods that are part of IDataContext. However, regardless of which approach you use the DAL 2 now provides the ability to do two things that were not available in the original DAL; work with a different database identified by a named connection string and use transactions.

[1] For a description of this design pattern see

[2] For a description of this design pattern see


M Bouwman
In your examples, I can see you are using IList.
I’ve looked around to find out the difference between IList and List, but I do not -really- see the direct advantage of using IList.
Could you give me a little explanation, or perhaps some tips on what to do.
Should I rewrite a lot(?) of code and use IList, or is it not all that important.
M Bouwman Tuesday, December 3, 2013 5:06 AM (link)

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