Multi-Tier CRUDL Archiecture Implementing Entity Framework

Written by Tom White on . Posted in C#

Introduction

For the longest time I balked every time somebody mentioned the Entity Framework. The notion of letting some omnipotent Microsoft demigod handle all my data access routines was absolutely horrific. However, with the release of EF6 and my assignment to a particular project at work, I decided to bite the bullet and give it the benefit of the doubt.

My architecture of choice for developing .Net applications is multi-tier. At a minimum I end up with a Data Access Layer (DAL) for all things data access and a Common Interface Layer (CIL) to store my entity classes. For larger applications, I'll end up with a Business Logic Layer (BLL) and then either a Console Application or a Forms Web Application sitting at the top of the stack depending on the type of project. The advantage of this sort of architecture is that it allows for ultra-simple code modification and it greatly reduces the chance of a database or business logic change breaking the application, with the theory being that your CRUDL (Create, Read, Update, Delete, List) methods are only written once and then accessed by every page in the application that needs to utilize those methods.

The tricky thing about this type of architecture, especially for listing/searching capabilities, is that the DAL is the only layer with access to the database. This means all sorting, paging, and filtering of data needs to occur in the DAL while the parameters for these operations are ultimately generated in the application sitting on top of the BLL. This is desirable because in general you want the lifespan of a database connection to be very short - get in, find the data you want, and get out. Leaving the connection open for extended periods of time can cause any number of issues, and dragging the entire database into server memory and doing data manipulation there is what we in the industry like to call 'a very bad thing'.

The focus of this article is implementation of the Entity Framework into a multi-tiered application and assumes the read has a basic understanding of both concepts.

Installing Entity Framework

With the multi-tiered application open, right click on the DAL, go to "Manage NuGet Packaces", and install the Entity Framework. This would be a good time to go ahead and add a connection string for your database to the Web.Config file of the main application if you haven't already done so.

Once the package installs, right click on the DAL again and go to Add -> New Item -> Data -> ADO.Net Entity Data Model. There will be a number of different ways to create the model, but for me the easiest is "Code First from database" which will generate a model based on an existing database.

After selecting this method of creation, select the connection string and proceed to the next step in the wizard.

For the scope of this article, we'll just select a single table to 'talk' to. Here in my demo database we have a table named Manufacturers with some data. Select your table and click Finish.

This will create a DbContext for your table as well as an entity class for the object the table represents. Up until now, we've played with the Entity Framework just like we would in any other application. However, for a multi-tiered application we'll need to decouple the entity class from the DAL since it's just the object definition and doesn't actually deal with data I/O.

The first thing to do is drag the entity class (in my case, 'Manufacturer.cs') into the CIL and change the namespace on the class from DAL to CIL. An error should now show up in the dbContext extension class stating that the entity class cannot be found. Simply update the missing class reference to point to the CIL and your dbContext has been successfully decoupled from your entity class.

Creating the DAL Methods

With our dbContext in place and successfully decoupled to the CIL, we'll need to write the DAL methods. For each of my entity classes, I have a corresponding DAL class. Listed below is the ManufacturerDAL that I created for my Manufacturers table referenced above. Different developers will have different methods depending on their philosophy of data I/O, but I ended up with the methods GetSkipAndTake() (for populating tables), Get() (for populating a dropdown), GetByID() (for View/Edit purposes), and Update() (for updating a changed item).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class ManufacturerDAL
    {
        public static List GetSkipAndTake(int maximumRows, int startRowIndex, out int totalRowCount, string sortByExpression, string filter)
        {
            using (RailTracker db = new RailTracker())
            {
                List Manufacturers = db.Manufacturers.Where(m => m.Name.Contains(filter)).OrderBy(m => m.Name).SortBy(sortByExpression).Skip(startRowIndex).Take(maximumRows).ToList();
                totalRowCount = db.Manufacturers.Where(m => m.Name.Contains(filter)).Count();
                return Manufacturers;
            }

        }
        public static List Get()
        {
            using (RailTracker db = new RailTracker())
            {
                return db.Manufacturers.ToList();
            }
        }
        public static CIL.Manufacturer GetByID(Int32 ID)
        {
            using (RailTracker db = new RailTracker())
            {
                return db.Manufacturers.FirstOrDefault(m => m.ManufacturerID == ID);
            }
        }
        public static void Update(CIL.Manufacturer Item)
        {
            using (RailTracker db = new RailTracker())
            {
                db.Entry(Item).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
            }
        }

    }
}

One thing to note is that I have an extension sitting inside an Extensions.cs class within the DAL that will let me sort the IEnumerable lists using the sort string generated by a GridView instead of having to generate a massive switch statement using LINQ:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public static class Extensions 
    {
        public static IQueryable SortBy(this IQueryable source, string propertyName)
        {
            if (source == null)
            {
                throw new ArgumentNullException("source");
            }

            // DataSource control passes the sort parameter with a direction
            // if the direction is descending           
            int descIndex = propertyName.IndexOf(" DESC");

            if (descIndex >= 0)
            {
                propertyName = propertyName.Substring(0, descIndex).Trim();
            }

            if (String.IsNullOrEmpty(propertyName))
            {
                return source;
            }

            ParameterExpression parameter = Expression.Parameter(source.ElementType, String.Empty);
            MemberExpression property = Expression.Property(parameter, propertyName);
            LambdaExpression lambda = Expression.Lambda(property, parameter);

            string methodName = (descIndex < 0) ? "OrderBy" : "OrderByDescending";

            Expression methodCallExpression = Expression.Call(typeof(Queryable), methodName,
                                                new Type[] { source.ElementType, property.Type },
                                                source.Expression, Expression.Quote(lambda));

            return source.Provider.CreateQuery(methodCallExpression);
        }
    }
}

The really big thing to notice with the DAL methods is the GetSkipAndTake() method. This method will query the datasource (Manufacturers) and return only a limited number of results that have been sorted and filtered on the SQL server and also return the total number of records in the dataset (not just the ones we're returning). We do this because if our table has 1000 records but we only want to display 25 in a table starting on page 3, we would be able to return only the data we want (efficiency!) but also be able to tell our GridView/table how many total records are in the dataset so we know which paging controls to generate. The parameters are passed down from the main application, which we'll get to in a bit.

Also notice that these methods are returning a generic List<> instead of an IQueryable List<>. Our DAL is going to open a connection, do a smash-and-grab on some data, and close the connection. We don't want to pass objects back up the the BLL that are still wanting the database connection to be open because if we've built our application right then all the sorting/filtering/paging has been done on the SQL server for us and there isn't a need to pass anything but 'dumb' data back up to the top of our layered stack for consumption. For complex object types this will mean having to do .Include() on the virtual properties for every load, or having to create separate methods that will either include or not include virtual properties depending on the context of how the data is going to be consumed.

Creating the BLL

The BLL's sole purpose is to serve as an intermediary between the application and the DAL to apply business rules where necessary and translate the various business events into technical ones. You'll notice that the DAL didn't have a method for Delete. That's because I never delete data - I flip an archive bit on the object so I can retrieve it later if needed. What this means is that my delete operations are really updates, so I use the BLL to expose a Delete() method which really just turns around and calls Update() in the DAL.

Built with HTML5 and CSS3
Copyright © 2014 Sitepearl