blog.abatir.se

Last post I promised to present a solution to the problem with data access for a custom dotnet repository. I’m going to use generics for this solution. Generics have an efficient implementation on the dotnet platform and they are pretty easy to grok once you have played around with them for a while.

Let’s start by extracting the ItemFromReader method to a separate class. This is needed to be able to pass it to a method. Well sort of, anyway. We will call this the factory class for the object.

namespace Infrastructure
{
  public interface IFactory<T>
  {
    T ItemFromReader(SqlDataReader reader);
  }

  public class OrganizationFactory : IFactory<Organization>
  {
    public Organization ItemFromReader(SqlDataReader reader)
    {
      return new Organization {
        Id = Convert.ToInt32(reader["Id"]),
        Name = Convert.ToString(reader["Name"]),
        Address = Convert.ToString(reader["Address"]),
        ZipCode = Convert.ToString(reader["ZipCode"]),
        City = Convert.ToString(reader["City"]),
        Homepage = Convert.ToString(reader["Homepage"]),
        Phone = Convert.ToString(reader["Phone"])
      };
    }
  }
}

Oh no! What’s that IFactory<Organization> thing? Well, this is where the generics magic happens! We need to create an interface for the generic Factory object to be able to switch the type it operates on. To be able to generalize the method of creating objects, this is one easy (imho) way to do it.

Next, we’ll need a generic repository class. It is implemented using generics so that the type of the object being handled can be defined.

namespace Infrastructure
{
  interface IRepository<T>
  {
    List<T> ListFromQuery(string query);
    T ItemFromQuery(string query);
  }

  public class Repository<T> : IRepository<T>
  {
    private const string CONNECTION_STRING_NAME = "Datastore";
    private IFactory<T> factory;

    public Repository(IFactory<T> factory)
    {
      this.factory = factory;
    }

    public List<T> ListFromQuery(string query)
    {
      var items = new List();
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              items.Add(factory.ItemFromReader(reader));
            }
          }
        }
      }
      return items;
    }

    public T ItemFromQuery(string query)
    {
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            if (reader.Read())
              return factory.ItemFromReader(reader);
          }
        }
      }
      throw new KeyNotFoundException(query);
    }
  }
}

Here we can see the factory method ItemFromReader being used and indeed it will return an object of the type specified in the concrete factory class.

Now, we need to implement a concrete repository based on a specific object. We’ll start with the Organization object.

namespace Infrastructure
{
  public class OrganizationRepository
  {
    private const string BASE_QUERY =
      "SELECT Id,Name,Address,ZipCode,City,Homepage,Phone" +
      " FROM Organizations";

    private static Repository<Organization> CreateRepository()
    {
      var factory = new OrganizationFactory();
      return new Repository<Organization>(factory);
    }

    public List<Organization> FindAll()
    {
      var repository = CreateRepository();
      return repository.ListFromQuery(BASE_QUERY);
    }

    public List<Organization> FindByName(string name)
    {
      var repository = CreateRepository();
      return repository.ListFromQuery(BASE_QUERY +
        " WHERE Name LIKE '%" + name + "%'");
    }

    public Organization GetById(int id)
    {
      var repository = CreateRepository();
      return repository.ListFromQuery(BASE_QUERY +
        " WHERE Id=" + id);
    }
  }
}

Hey, would you look at that!? The amount of code for the concrete repository class has drastically reduced. There’s a number of advantages to this approach:

• Less code in repositories = less chance of errors and bugs.
• Easier to switch to another database provider, i.e. MySql Postgress or Oracle

I consider myself an average programmer so I assume there are more efficient solutions to this but at this point I’m satisfied with the described pattern.

Alright, to continue our quest towards a usable custom repository pattern we will refactor our contrived example of Organizations and Contacts. The domain objects are ok, but we need to simplify the code in the repositories. Specifically, we will try to instill some patterns to enable code reuse.

We start by using the extract method to pull out the creation of the Organization object since this is a repeated three times.

Also, the sql query is the same for all methods except for the where filter. So, we create a simple constant containing the base query and let the methods use it.

namespace Infrastructure
{
  public class OrganizationRepository
  {
    private const string CONNECTION_STRING_NAME = "Datastore";
    private const string BASE_QUERY =
      "SELECT Id,Name,Address,ZipCode,City,Homepage,Phone" +
      " FROM Organizations";

    private Organization ItemFromReader(SqlDataReader reader)
    {
      return new Organization {
        Id = Convert.ToInt32(reader["Id"]),
        Name = Convert.ToString(reader["Name"]),
        Address = Convert.ToString(reader["Address"]),
        ZipCode = Convert.ToString(reader["ZipCode"]),
        City = Convert.ToString(reader["City"]),
        Homepage = Convert.ToString(reader["Homepage"]),
        Phone = Convert.ToString(reader["Phone"])
      };
    }

    public List<Organization> FindAll()
    {
      var items = new List<Organization>();
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        string query = BASE_QUERY;
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              items.Add(ItemFromReader(reader));
            }
          }
        }
      }
      return items;
    }

    public List<Organization> FindByName(string name)
    {
      var items = new List<Organization>();
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        string query = BASE_QUERY +
          " WHERE Name LIKE '%" + name + "%'";
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              items.Add(ItemFromReader(reader));
            }
          }
        }
      }
      return items;
    }

    public Organization GetById(int id)
    {
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        string query = BASE_QUERY +
          " WHERE Id=" + Id;
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            if (reader.Read())
            {
              return ItemFromReader(reader);
            }
          }
        }
        throw new KeyNotFoundException(query);
      }
    }
  }
}

Now, there’s a lot of data access code that looks almost identical. We could try to extract it into a separate method but there’s a problem. The GetById method does an if (reader.Read()) comparison while all other methods uses while (reader.Read()). How do we generalize this? While trying to solve this, I came to think about functional languages. Some of the functional languages has the notion of higher order functions which basically say that you can submit a method as a parameter to another method. If we just somehow could submit our ItemFromReader method to a generalized data access method we could achieve a much higher degree of code reuse. Stay tuned for my next post to see how this can be achieved.

I’ve spent a good deal of the past couple of years pondering ORM’s and data access technologies for the dotnet platform. Needless to say, it can be quite involved. Before you cite Ayende Rahien and tell me that “Data Access is a solved problem”, I ask you to bear with me for a minute.

A common pattern for data access is to create a domain model with classes for the objects your application needs. Here we are going to use POCO’s (Plain Old Csharp objects):

using System;

namespace Domain
{
  public class Organization
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string ZipCode { get; set; }
    public string City { get; set; }
    public string Homepage { get; set; }
    public string Phone { get; set; }
  }
  public class Contact
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public Organization Organization { get; set; }
  }
}

A hand-coded data access layer might start out like this if you have no intention of code reuse:

namespace Infrastructure
{
  public class OrganizationRepository
  {
    private const string CONNECTION_STRING_NAME = "Datastore";

    public List<Organization> FindAll()
    {
      var items = new List<Organization>();
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        string query =
          "SELECT Id,Name,Address,ZipCode,City,Homepage,Phone" +
          " FROM Organizations";
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              items.Add(new Organization {
                Id = Convert.ToInt32(reader["Id"]),
                Name = Convert.ToString(reader["Name"]),
                Address = Convert.ToString(reader["Address"]),
                ZipCode = Convert.ToString(reader["ZipCode"]),
                City = Convert.ToString(reader["City"]),
                Homepage = Convert.ToString(reader["Homepage"]),
                Phone = Convert.ToString(reader["Phone"])
              });
            }
          }
        }
      }
      return items;
    }

    public List<Organization> FindByName(string name)
    {
      var items = new List<Organization>();
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        string query =
          "SELECT Id,Name,Address,ZipCode,City,Homepage,Phone" +
          " FROM Organizations" +
          " WHERE Name LIKE '%" + name + "%'";
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              items.Add(new Organization {
                Id = Convert.ToInt32(reader["Id"]),
                Name = Convert.ToString(reader["Name"]),
                Address = Convert.ToString(reader["Address"]),
                ZipCode = Convert.ToString(reader["ZipCode"]),
                City = Convert.ToString(reader["City"]),
                Homepage = Convert.ToString(reader["Homepage"]),
                Phone = Convert.ToString(reader["Phone"])
              });
            }
          }
        }
      }
      return items;
    }

    public Organization GetById(int id)
    {
      string connectionString = ConfigurationManager
        .ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();
        string query =
          "SELECT Id,Name,Address,ZipCode,City,Homepage,Phone" +
          " FROM Organizations" +
          " WHERE Id=" + Id;
        using (var command = new SqlCommand(query, connection))
        {
          using (var reader = command.ExecuteReader())
          {
            if (reader.Read())
            {
              return new Organization {
                Id = Convert.ToInt32(reader["Id"]),
                Name = Convert.ToString(reader["Name"]),
                Address = Convert.ToString(reader["Address"]),
                ZipCode = Convert.ToString(reader["ZipCode"]),
                City = Convert.ToString(reader["City"]),
                Homepage = Convert.ToString(reader["Homepage"]),
                Phone = Convert.ToString(reader["Phone"])
              };
            }
          }
        }
        throw new KeyNotFoundException(query);
      }
    }
  }
}

Using this (admittedly contrived) example, you can begin to understand the amount of time required to write even the simplest applications.

Next, we will look at the first iteration for refactoring this thing into something more manageable.