Introduction

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.

public interface IDataContext : IDisposable

{

    void BeginTransaction();



    void Commit();



    void Execute(CommandType type, string sql, params object[] args);



    IEnumerable<T> ExecuteQuery<T>(CommandType type, string sql, params object[] args);



    T ExecuteScalar<T>(CommandType type, string sql, params object[] args);



    T ExecuteSingleOrDefault<T>(CommandType type, string sql, params object[] args);



    IRepository<T> GetRepository<T>() where T : class;



    void RollbackTransaction();

}

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.

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<T>
• ExecuteScalar<T>
• ExecuteSingleOrDefault<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<T> and ExecuteSingleOrDefault<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<T>

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

IList<TaskInfo> tasks;



using (IDataContext db = DataContext.Instance())

{

    tasks = db.ExecuteQuery<TaskInfo>(CommandType.Text, "SELECT * FROM dnn_Tasks").ToList();

}



return tasks;

Under the covers the ExecuteQuery<T> method calls PetaPoco’s Fetch<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 below.

IList<TaskInfo> tasks;



using (IDataContext db = DataContext.Instance())

{

    tasks = db.ExecuteQuery<TaskInfo>(CommandType.Text, "").ToList();

}



return tasks;

PetaPoco will build the SELECT statement automatically using any Custom Mappings that have been defined.In addition you can execute a query with a sql condition fragment in order to return a filtered List of objects, in this case a list of completed tasks.

IList<TaskInfo> tasks;



using (IDataContext db = DataContext.Instance())

{

    tasks = db.ExecuteQuery<TaskInfo>(CommandType.Text, "WHERE ISComplete = @0", true).ToList();

}



return tasks;

ExecuteSingleOrDefault<T>

The ExecuteSingleOrDefault<T> method is similar to the ExecuteQuery<T> method except that it returns a single object rather than a list of objects. As with the ExecuteQuery<T> 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.

TaskInfo task;



using (IDataContext db = DataContext.Instance())

{

    task = db.ExecuteSingleOrDefault<TaskInfo>(CommandType.Text, "WHERE ID = @0", id);

}



return task;

ExecuteScalar<T>

The ExecuteScalar<T> 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.

using (IDataContext db = DataContext.Instance())

{

    task.TaskID = db.ExecuteScalar<int>(CommandType.Text, 

                "INSERT INTO dnn_Tasks " +

                "       (Name, Description, IsComplete) " +

                "       VALUES (@0, @1, @2)",

                task.Name, 

                task.Description, 

                task.IsComplete);

}

Execute

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.

using (IDataContext db = DataContext.Instance())

{

    db.Execute(CommandType.Text, "DELETE FROM dnn_Tasks  WHERE ID=@0", id);

}

The GetRepository<T> Method

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

Using the repository we can get a list of tasks quite simply using the following code.

IList<TaskInfo> tasks;



using (IDataContext db = DataContext.Instance())

{

    var rep = db.GetRepository<TaskInfo>();

    tasks = rep.Get().ToList();

}



return tasks;

The IRepository<T> interface also has methods to get filtered lists.

IList<TaskInfo> tasks;



using (IDataContext db = DataContext.Instance())

{

    var rep = db.GetRepository<TaskInfo>();

    tasks = rep.Find("WHERE IsComplete = @0", true).ToList();

}



return tasks;

The page on the Repository Component goes into more detail on this component.

Unit of Work and Transactions

PetaPocoDataContext implements an important design pattern – the Unit of Work. This is primarily through its implementation of the IDisposable interface. This can be seen below, where a list of tasks are retrieved from the database and then each one’s IsComplete property is set to true.

using (IDataContext db = DataContext.Instance())

{

    var rep = db.GetRepository<TaskInfo>();

    tasks = rep.Find("WHERE IsComplete = @0", true).ToList();

    foreach(TaskInfo task in tasks)

    {

        task.IsComplete = true;

        rep.Update(task);

    }

}

These two actions are considered a single Unit of Work and are therefore carried out using the same context instance within the using statement.

While the code above 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.

IList<TaskInfo> tasks;



using (IDataContext db = DataContext.Instance())

{

    try

    {

        db.BeginTransaction();



        var rep = db.GetRepository<TaskInfo>();

        tasks = rep.Get().ToList();

        foreach (TaskInfo task in tasks)

        {

            task.IsComplete = true;

            rep.Update(task);

        }

        db.Commit();

    }

    catch (Exception)

    {

        db.RollbackTransaction();

        throw;

    }

}

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.