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 3^rd 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 http://www.martinfowler.com/eaaCatalog/repository.html

[2] For a description of this design pattern see http://www.martinfowler.com/eaaCatalog/unitOfWork.html