How should I encapsulate database access?

Question

What are some examples of good class structures used to manage database access? I am a fan of class encapsulation and would prefer the containers (e.g. car) not to perform database tasks.

I would also like the ability to easily drop in things like a database cache in future.

I often take the pattern of container classes, complete with getters and setters for validation and database access performed by a single singleton class. That being said, this often gets mixed between the two and becomes quite confusing.

Sorry if my question is hard to understand; I'm not absolutely sure on terms regarding databases. Please feel free to ask for clarification if needed.

Answer 1

I prefer the Repository Pattern to encapsulate data access. In a nutshell, the repository is responsible for loading all the data required for a specific object. Say you have a Car object, like in your example. But all the attributes for the car, make, model, year, owners, features (CD player, 4wd etc) are stored in various tables throughout the database. The repository determines how to load and save the data. If multiple smaller queries are needed, fine, but only the repository pattern needs to know that. The service layer invoking the repository only needs to know which repository to invoke.

That can then be combined with the unit of work pattern. So in your example, the service layer would say it needs to load a car entity, it has some sort of unique identifier, and sends that identifier down to the repository. The repository returns the car entity. Some other code manipulates the car entity and sends that entity back to the repository so it can be saved.

If you really want to go all out, the repository layer would only expose interfaces, such as ICarRepository. The repository would contain a factory which the service layer would use to get the ICarRepository interface. All database access would be hidden behind an interface, which makes unit testing much much easier.

Answer 2

I have used the Strategy Pattern to encapsulate data access. This pattern allows you to hide the the type of storage you are using behind a common interface. In the interface, define your data access methods with disregard for the type of storage (file,database,web). Then for your current storage choice, in a class realizing the strategy interface, implement the data access details. This way your application doesn't care about the data source you are using.

You can also build a service layer that uses the current data storage strategy instance to define more application specific details instead of mixing data access and business logic together.

Answer 3

This is an example of database Factory pattern;

using System.Reflection;
using System.Configuration;

public sealed class DatabaseFactory
{
    public static DatabaseFactorySectionHandler sectionHandler = (DatabaseFactorySectionHandler)ConfigurationManager.GetSection("DatabaseFactoryConfiguration");

    private DatabaseFactory() { }

    public static Database CreateDatabase()
    {
        // Verify a DatabaseFactoryConfiguration line exists in the web.config.
        if (sectionHandler.Name.Length == 0)
        {
            throw new Exception("Database name not defined in DatabaseFactoryConfiguration section of web.config.");
        }

        try
        {
            // Find the class
            Type database = Type.GetType(sectionHandler.Name);

            // Get it's constructor
            ConstructorInfo constructor = database.GetConstructor(new Type[] { });

            // Invoke it's constructor, which returns an instance.
            Database createdObject = (Database)constructor.Invoke(null);

            // Initialize the connection string property for the database.
            createdObject.connectionString = sectionHandler.ConnectionString;

            // Pass back the instance as a Database
            return createdObject;
        }
        catch (Exception excep)
        {
            throw new Exception("Error instantiating database " + sectionHandler.Name + ". " + excep.Message);
        }
    }
}