logo

Premium Support
24/7/365

Premium support customers may submit help-desk tickets using the form below.
Alternatively, the help-desk system can be accessed directly at support.maxiomtech.com. support@maxiomtech.com
info@maxiomtech.com
+1 (703) 942-9420
 

Embrace The DAL2 and PetaPoco In DNN

Embrace The DAL2 and PetaPoco In DNN

DNNCorp_logo_lrgI spent the past weekend at DNN CON 2014 and had some discussions with fellow developers about the DAL, DAL+ and DAL2 and many of whom are still using DAL+. I myself find the DAL2 to be very nice so I thought I’d share some of the common concerns developers have about switching.

What About My Stored Procedures?

DNN at it’s core has switched to DAL2 but the stored procedures are still the backbone for getting the data out of SQL.

public IEnumerable<Employee> GetEmployees()
{
    using (IDataContext db = DataContext.Instance())
    {
        return db.ExecuteQuery<Employee>("usp_Procedure_Name", 1, 2, 3);
    }
}

This method essentials combines the CBO utility with the DataProvider that we have all grown so fond of in the past.

CBO.FillCollection(DataProvider.Instance().ExecuteReader("sp_Procedure_Name", 1, 2, 3));

Joins Are Hard

As mentioned above you can always just use a stored procedure that handles the joins just as you always have done. However now you can also handle your joins with entities and SQL views.

[PrimaryKey("ID")]
[TableName("vw_InspectorIT_Employee_List")]
public class Employee
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
}

Settling the [TableName] attribute to a SQL view I can now, without any additional code,  automatically get the CRUD (Create, Read, Update, Delete) functions as part of the repository pattern.

// All you need to create a base repository
public class EmployeeRepository: RepositoryImpl<Employee>{}

// New up the repository anywhere you need to access data.
var repo = new EmployeeRepository();
repo.Get(); 		// Fetches all Employees from the view
repo.Update()		// Updates an employee entity
repo.Delete()		// Deletes an employee entity
repo.GetPage(0,25)	// Gets a paged list of employees (index, pageSize).

It literally is that simple. I got all those CRUD features without writing a single stored procedure. I created a table and a view and that’s it. There is nothing stopping you from creating any number of entities and views that map to the same core table.

Transactions

Many people note that the reason they use stored procedures is it’s the only way to do transactions. This is false.

public void UpdateEmployees(List<Employee> employees)
{
    using (IDataContext db = DataContext.Instance())
    {
        try
        {
            db.BeginTransaction();
            var employeeRepo = db.GetRepository<Employee>();
            foreach (var employee in employees)
            {
               employeeRepo.Update(employee);
            }
            db.Commit();
        }
        catch (Exception ex)
        {
            db.RollbackTransaction();
            Exceptions.LogException(ex);
        }
    }
}

One-To-Many Relationships (Advanced Joins)

The ability to optionally hydrate an entity when you need to do a one-to-many relationship in code is very powerful and ensure that you only fetch the data you need when you need it. This is important when accessing data from the web server to the SQL server as well as from the client browser to the web server.

public IEnumerable<Employee> GetEmployeesWithWorkItems()
{
    using (IDataContext db = DataContext.Instance())
    {
        var employeeRepo = db.GetRepository<Employee>();
        var workItemRepo = db.GetRepository<WorkItem>();

        var employees = employeeRepo.Get();
        var workItems = workItemRepo.Find("WHERE EmployeeId in (@0)", string.Join(",",employees.Select(x => x.ID).ToArray()));

        employees = employees.Select(x =>
        {
            x.WorkItems = workItems.Where(w => w.EmployeeId == x.ID).ToList();
            return x;
        });

        return employees;
    }
}

[PrimaryKey("ID")]
[TableName("InspectorIT_Employee_View")]
public class Employee
{
    public Employee()
    {
        WorkItems = new List<WorkItem>();
    }

    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [ResultColumn]
    public List<WorkItem> WorkItems { get; set; }
    public bool IsActive { get; set; }
}

This method ensures that we are only getting exactly the data that we want but has the draw back of making two network requests to the SQL server for data. Note the [ResultColumn] attribute which marks that property as not being directly part of the Employee entity table.

An alternative way is to reference the PetaPoco library directly and gain access to some additional and more advanced features.

public IEnumerable<Employee> GetEmployeesWithWorkItemsAlternative()
{
    using (var db = new PetaPoco.Database("SiteSqlServer"))
    {
        return db.Fetch<Employee, WorkItem, Employee>(new EmployeeWorkItemRelator().MapIt,
            PetaPoco.Sql.Builder.Append("select a.*,b.* from vw_InspectorIT_Employee_List a")
            .Append("left outer join InspectorIT_Employee_WorkItems b on a.ID = b.EmployeeId"));
    }
}

public class EmployeeWorkItemRelator
{
    public Employee current;

    public Employee MapIt(Employee a, WorkItem b)
    {
        // Terminating call.  Since we can return null from this function
        // we need to be ready for PetaPoco to callback later with null
        // parameters
        if (a == null)
            return current;

        // Is this the same Employee as the current one we're processing
        if (current != null && current.ID == a.ID)
        {
            // Yes, just add this post to the current employee's collection of posts
            current.WorkItems.Add(b);

            // Return null to indicate we're not done with this author yet
            return null;
        }

        // This is a different employee to the current one, or this is the
        // first time through and we don't have an employee yet

        // Save the current employee
        var prev = current;

        // Setup the new current employee
        current = a;
        current.WorkItems = new List<WorkItem>();
        current.WorkItems.Add(b);

        // Return the now populated previous employee (or null if first time through)
        return prev;
    }
}

The end result of these two methods is the same. However the alternative method will make a single request to the SQL server for data but will end up returning redundant data for the employees for each work item that is available.

Hidden Benefits

The primary hidden benefit is you no longer have to write massive SqlDataProvider update scripts every time you add a column to one of your tables. A simple update to the table is all you need. Again all the CRUD operations are handled automatically.

Jonathan Sheely

Sr Software Engineer at InspectorIT. Jonathan is an out of the box thinker who has over 10 years experience building and supporting web application software and infrastructure. Jon specializes in ASP.NET C#, Javascript and CSS but is willing to learn anything that gets the job done.

1 Comment
  • Taylor Brazelton
    Posted at 3:57 pm, June 16, 2015

    How did you get access to the [ResultColumn] token? I always get an error as it can’t find the namespace or type.