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

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”.

PetaPocoHelper

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).

IDataContext

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”.