Design pattern for saving entities into database

Question

I have a class similar to the below (C#):

public class Product {

    public int ID {get;set;}
    public string Name {get;set;}
    public double Price {get;set;}

    public void Save() {
        string sql = "INSERT INTO Product.....";
        Database.Execute(sql);
    }

    public void Delete() {
        string sql = "DELETE Product WHERE.....";
        Database.Execute(sql);
    }
}

My main concern is that the code above violates SOLID principles, since it takes responsibility for creating and deleting itself.

Perhaps these Save and Delete methods should be placed somewhere outside the Product entity (Factory/Repository maybe?).

Answer 1

I would introduce your model entity, a command and query pattern and a database layer or repository.

Your model is your Product and this object should be a plain object:

public class Product : IEntity {
    public int ID { get; set; }
    public string Name { get; set; }
    public double Price { get; set; }
}

Next I would create a command and query interface for working with this entity:

public interface ICommand {} // Marker interface

public interface IQuery<TResult> {} // Marker interface

Next define the handlers for ICommand and IQuery:

public interface IHandleQuery<TQuery, TResult> where TQuery : IQuery<TResult> 
{
    TResult Handle(TQuery query);
}

public interface IHandleCommand<TCommand> where TCommand : ICommand
{
    void Handle(TCommand command);
}

Now you have a clear indication and separation of your write (command) and read (query) sides.

This means we can create a command and its handler for saving your Product like:

public class SaveProduct : ICommand 
{
    public string Name { get; private set; }
    public double Price { get; private set; }

    public SaveProduct(string name, double price) 
    {
        Name = name;
        Price = price;
    }
}

public class HandleSaveProduct : IHandleCommand<SaveProduct> 
{
    private readonly IRepository<Product> _productRepository;

    public HandleSaveProduct(IRepository<Product> productRepository) 
    {
        _productRepository = productRepository;
    }

    public void Handle(SaveProduct command) 
    {
        var product = new Product {
            Name = command.Name,
            Price = command.Price
        };

        _productRepository.Save(product);
    }
}

In the above we have defined an repository for handling this entity, you can however depend directly on your database context here and do the queries/commands to it or you can implement the repository pattern using an GenericRepository<TEntity> : IRepository<TEntity> or just the separate product repository:

public interface IEntity { } // Marker interface

public interface IRepository<TEntity> where TEntity : IEntity 
{
    TEntity Get(object primaryKey);

    void Save(TEntity entity); // should handle both new and updating entities

    void Delete(TEntity entity);

}

public class ProductRepository : IRepository<Product> 
{
    public Product Get(object primaryKey) 
    {
        // Database method for getting Product
    }

    public void Save(Product entity) 
    {
        // Database method for saving Product
    }

    public void Delete(Product entity) 
    {
        // Database method for deleting Product
    }
}

You should never return your Product entity to your UI, but use a view model, e.g:

public class ProductViewModel {
    public int ID { get; set; }
    public string Name { get; set; }
    public double Price { get; set; }
    public DateTime Whatever { get; set; }
}

public class GetProductById : IQuery<ProductViewModel>
{
    public int Id { get; private set; }

    public GetProductById(int id)
    {
        Id = id;
    }
}

public class HandleGetProductById : IHandleQuery<GetProductById, ProductViewModel>
{
    private readonly IRepository<Product> _productRepository;

    public HandleGetProductById(IRepository<Product> productRepository) 
    {
        _productRepository = productRepository;
    }

    public ProductViewModel Handle(GetProductById query)
    {
        var product = _productRepository.Get(query.Id);
        return product.Select(x => new ProductViewModel {
            Name = x.Name,
            Price = x.Price;
        });
    }
}

Please note this is written in notepad, might not compile 100%, but you should get the idea on how the separation of the various components should be in order to follow SOLID. :-)