Implementing an architecture with ASP.NET MVC (Part 4) – data access

Continuing on from part3 – the business layer. In this post the focus is on data access. In part 3, I created an interface called “ICustomerRepository” and a class “CustomerRepository” which will change to actually do something. Using the repository interface, our business code can interact with it without being coupled to the technology that is used to communicate with the data store.

This post is part of a series of posts about creating an architecture when creating a line of business application with ASP.NET MVC. The business and persistence layers have nothing to do with ASP.NET MVC or any other UI technology so this approach is relevant to any .net application. The next and last post of the series will be placing the business and persistence behind a WCF endpoint.

What technology?

We have many choices in the data access area. You could have either a ODBMS or a RDBMS. If you are using RDBMS than is a pretty good assumption to say you are using MS SQL server. So what choices do you have:

ADO.NET – Simple to understand, but as your application grows you will end up writing the same code over and over unless you create your own abstraction over the top. You have to write the SQL yourself, which means you will end up with many stored procedures (sprocs). On a positive note, you have control of the SQL and you can use sprocs as a API to your database. You can shape the result set of data in your sproc so its easier to handle in the application. This is the traditional approach, personally i had been using this approach since SQL 7.0 before .NET and also back in .NET 1.0 and 1.1. days. From experience, you do write more code mapping the results of the sproc to your classes. Other ways of using ADO.NET would be to provide the SQL inline with .NET code or use typed DataSets (poor mans attempt of ORM), but these are just wrong on many counts.

Object Relational Mappers (ORMs)

ORM’s have been around for years and have got more popular of the last 2-3 years in .net world. You have two types of ORMs, the first type are based on the Active Record pattern and the next type based on the Data Mapper pattern.  The active Record pattern in short is where your database table and domain class have a 1 to 1 mapping. So your domain classes mirror the database. Works well when you have a good database schema. The Data mapper is where your domains class are different from the data tables. Their are lots of ORMs available, here is a summary I the ones I know well.

NHibernate – IMHO the most powerful ORM to date, supports the data mapping pattern, been around for a while and got a big community around it and a number of tools like NHProf and Fluent NHibernate that improve the experience. Being as powerful as it is, its a bigger learning curve. It has it own methods for querying data using HQL, detached criteria as well as Linq (not fully implemented). Also NH supports many different RDBMS’s. Negatives for me are, session management and that it requires initialising when you start your application. By default uses XML mapping files (I hate writing XML, but enjoy writing XAML. Work that one out). Need a handful of DLL’s that you need to ship with your application.

Castle ActiveRecord – Personal favourite. Built on top of NHIbernate and supports the Active Record Pattern. You add attributes to your classes and properties to set up your mapping. Its simple, but you have the power of nhibernate under the hood if you need it. The session management is simplified, still requires initialisation when your application starts.  The ActiveRecordMediator is so simple to use. Negatives are that it requires shipping the same DLL’s that nhibernate needs plus the the castle ones. Borrows the querying functionality from nhibernate.

Linq for SQL (DLINQ) – Very simple to use, supports the Active Record pattern. Can you attributes or XML to define the mapping. Does come with a designer/SQL metal. Personally i hand craft my domain classes as the designer gives you a lot of boilerplate code that in more cases in not needed, plus i model the domain first and not the database. Its built into the .NET framework so no external DLL’s to manage. No session management, the data contexts only needs a connection string. The data context uses transactions by default. The only querying mechanism is Linq which is fully implemented. Negatives are: Limited to SQL server and SQL CE, have to include properties in your classes that relate to foreign keys in the db.

Linq for Entities – (ADO.NET Entity framework) – Waiting for the next version of this, as the current version is data driven instead of domain driven. So for me its not an option at the moment.

Object Database Management Systems (ODBMS)

I only have experience in using one ODBMS being db4o. Using an object database in a change in mindset and is not that common across the .NET developer community, although products like db4o does have a massive community across .net and java developers. I think the reason for slow uptake is down to the fact that RDBMS have been around for 30 odd years and they have got better and vendors have changed their products to keep supporting the current market trends like XML.

Db4o – Really easy to use, requires a tiny amount of code. Uses a file either locally or on a remote server. No mapping required, you use OO in your domain so why not store it OO in the database. Supports Linq and also has other ways to query the database. Great documentation and support. Negatives… wish it was more a mainstream so i wouldn’t have to use RDBMS’s any more.

While developing a project with Db4o, you realise that you don’t think rows in a table or association tables. And what is even better, when you already have a database in place that contains data and you make changes to your classes like adding properties, do you need a database migration script? no. Some smart stuff inside db4o knows that the type has changed an handles it. You don’t lose data, it just works. This is a big positive for me. When using a RDBMS, tracking database changes in development is a pain and you need a process in place not only for development but when you deploy to other environments. if you have a bug in script it stops you from deploying your release as you need keep your scripts in sync with your code. Needless to say, when managing SQL scripts you need to make changes within a transaction so it can rollback in the event of failure and you must also write your scripts so they can be run more than once. With Db4o its a non-issue, no scripts, no process, no problems.

So what technology?

Ok enough rumbling. With my agile head on. I will chose the simplest option, which i believe its db4o.

Implementing the Repository

At the end of part 3, I created a domain class called “Customer”, the ICustomerRepository interface and a concrete implementation called CustomerRepository.

Using db4o

  1. So if you haven’t got db4o you can download the msi from here. I am using version 7.4 for .net 3.5.
  2. In the Web.Business project, add references to:
    • Db4objects.Db4o.dll
    • Db4objects.Db4o.Linq.dll
    • System.Configuration
  3. One difference between using SQL server and db4o is the connection lifetime, in SQL server connections are opened and closed as quickly as possible and the connection is returned to a pool (if configured to). In db4o, this works differently. When you start your application you open the connection and keep it open until the application ends.  Their are a few ways to do this, one of the common approaches i see in web applications that require initialising a database component like nhibernate and Castle ActiveRecord is that in the Global.asax, its configured in the Application_Start() method. I think this stinks, why does UI application need to know about the persistence. My preferred way is to make it happen where its needed. Because i need to ensure the lifetime is the same as the application, I use a singleton to hold the reference. Here is that class.
    using System;
    using System.Configuration;
    using Db4objects.Db4o;
    
    namespace Web.Business.Persistence.Db4o
    {
        internal class DatabaseContext : IDisposable
        {
            private static DatabaseContext context;
    
            private IObjectContainer database;
    
            static DatabaseContext()
            {
                context = new DatabaseContext();
            }
    
            private DatabaseContext()
            {
                database = Db4oFactory.OpenFile(Db4oFactory.NewConfiguration(),
                    ConfigurationManager.AppSettings["DatabaseFileName"]);
            }
    
            public void Dispose()
            {
                database.Close();
            }
    
            public static DatabaseContext Current
            {
                get { return context; }
            }
    
            public IObjectContainer Client
            {
                get { return database; }
            }
        }
    }
  4. I have added an application setting into the web.config called “DatabaseFileName” which as you might as guessed is the path to the db4o database file.<appSettings><add key=”DatabaseFileName” value=”C:\Web\WebDb.yap”/>

    </appSettings>

  5. Now to make the CustomerRepository use the DatabaseContext to fetch the data. The finished code looks like this.
    using System.Collections.Generic;
    using System.Linq;
    using Db4objects.Db4o.Linq;
    using Web.Business.Domain;
    
    namespace Web.Business.Persistence
    {
        internal class CustomerRepository : ICustomerRepository
        {
            public List<Customer> FindAll()
            {
                return (from Customer customer in DatabaseContext.Current.Client select customer).ToList();
            }
        }
    }
  6. That’s it, done. Only the database is empty. Ideally in the real world you might have screens in your application that you can use to populate the database. As we don’t, i have created a test that can be run to insert data in the database.
    using NUnit.Framework;
    using Web.Business.Domain;
    using Web.Business.Persistence;
    
    namespace Web.Business.Specs
    {
        [TestFixture]
        [Category("Integration")]
        public class DataIntegrationFixture
        {
            [Test]
            [Explicit]
            public void PopulateCustomers()
            {
                Customer customer = new Customer
                {
                    AccountManagerName = "Mr A Manager",
                    AccountNumber = "ABC123",
                    City = "Some big city",
                    Country = "UK",
                    Name = "Big city customer"
                };
    
                DatabaseContext.Current.Client.Store(customer);
            }
        }
    }

If you run the application, the data will be pulled from the database and displayed in the view. If this was a real application, i would create an generic abstract EntityRepository class that took a domain class as its generic type. I would make this base class use the DatabaseContext and that way i would not be repeating code.