Creating a Repository Pattern without an ORM

If you don't feel like using an ORM, like Entity Framework, you can build your own data layer. In this post, I talk about a different way to pull data from a database using a strategy pattern.

ORMs have been around for a while to map our objects to fields and vice-versa, but some developers see a tremendous amount of overhead when working with ORMs and decide not to use them.

Personally, I don't see a problem with the "overhead" since I would gladly trade-off a little bit of reflection performance for maintainability.

So you're probably wondering "How can you remove an ORM, like Entity Framework, and still get the performance while still creating a maintainable code base?"

One solution is to take the repository class and make it more ADO-based.

If you look back at the generic Repository class built in the post called "ASP.NET MVC Data Layer: Access Your Data Layer Through Unique Requests," we hooked up Entity Framework to that repository class.

With that said, if we removed Entity Framework, we would have to perform the mapping of the fields to the objects ourselves. That's one of the benefits of using an ORM. You don't have the hassle of performing mapping routines from your records to your objects.

However, there is a concern with using an ORM. If you are using SQL Server 2000 (don't laugh, there are people still using it), Entity Framework WILL NOT work on SQL Server 2000.

If that's the case with some legacy C# and SQL code, then I would recommend you either 1). Talk to your manager about upgrading SQL Server 2000 (THAT WAS 15 YEARS AGO!), or 2). Rethink how you want to handle your record retrieval (hence, this post).

A New Type of Repository

At one point, there was a company that DID have a SQL Server 2000 box and I couldn't use any kind of ORM at the time. So I decided to build a repository pattern using ADO.NET.

Repository\AdoRepository.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace DataLayer.Repository
{
    public abstract class AdoRepository<T> where T : class
    {
        private static SqlConnection _connection;
        public AdoRepository(string connectionString)
        {
            _connection = new SqlConnection(connectionString);
        }
        public virtual T PopulateRecord(SqlDataReader reader)
        {
            return null;
        }
        protected IEnumerable<T> GetRecords(SqlCommand command)
        {
            var list = new List<T>();
            command.Connection = _connection;
            _connection.Open();
            try
            {
                var reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                        list.Add(PopulateRecord(reader));
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            finally
            {
                _connection.Close();
            }
            return list;
        }
        protected T GetRecord(SqlCommand command)
        {
            T record = null;
            command.Connection = _connection;
            _connection.Open();
            try
            {
                var reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        record = PopulateRecord(reader);
                        break;
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            finally
            {
                _connection.Close();
            }
            return record;
        }
        protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
        {
            var list = new List<T>();
            command.Connection = _connection;
            command.CommandType = CommandType.StoredProcedure;
            _connection.Open();
            try
            {
                var reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        var record = PopulateRecord(reader);
                        if (record != null) list.Add(record);
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            finally
            {
                _connection.Close();
            }
            return list;
        }
    }
}

I am using a combination of the repository pattern with a strategy pattern, so we have to inherit from the ADORepository to create other table repositories for this to work properly.

Let's go back to our FaqRepository from our thin controller days.

If you look over the code, we just had a GetAll() and a GetById(). Pretty simple, right?

With the AdoRepository class, we have to add one more method called PopulateRecord.

This method receives a SqlDataReader and requires the creation and mapping of the fields to the object's properties.

Our OldFaqRepository will now look like this:

Repository\OldFaqRepository.cs

using System.Collections.Generic;
using System.Data.Entity.Core.Objects;
using System.Data.SqlClient;
using BusinessLayer.Models;
namespace DataLayer.Repository
{
    public class OldFaqRepository: AdoRepository<Faq>
    {
        public OldFaqRepository(string connectionString)
            : base(connectionString)
        {
        }
        public IEnumerable<Faq> GetAll()
        {
            // DBAs across the country are having strokes 
            //  over this next command!
            using (var command = new SqlCommand("SELECT * FROM Faq"))
            {
                return GetRecords(command);
            }
        }
        public Faq GetById(string id)
        {
            // PARAMETERIZED QUERIES!
            using (var command = new SqlCommand("SELECT * FROM Faq WHERE Id = @id"))
            {
                command.Parameters.Add(new ObjectParameter("id", id));
                return GetRecord(command);
            }
        }
        public override Faq PopulateRecord(SqlDataReader reader)
        {
            return new Faq
            {
                Question = reader.GetString(0),
                Answer = reader.GetString(1)
            };
        }
    }
}

So now, our AdoRepository has almost the same interface as our Entity Framework FaqRepository, but it doesn't require the heavy ORM and it doesn't address writing data out to the database.

However, each class that inherits from the AdoRepository must contain a PopulateRecord so that the GetRecords() and GetRecord() methods can call it to return a populated object (or objects).

This particular repository just handles the reading of the data. We haven't even talked about writing the objects out to the database, but I feel that the Save could have a similar technique: One method in the base class, a virtual method to SaveObject, and two Save methods (SaveRecord and SaveRecords) in the repository class.

Conclusion

Today, we have taken the ORM out of our repositories and made a simpler AdoRepository for older, legacy applications running on older machines that don't support the newer and shiny ORMs.

Let me know if this helped at all. Post a comment below.