C# database access, Dapper, SQL and POCOs – programming design

Let’s say we have a table in SQL represented in C# like this:

public class Product
{
   public int ID { get; set; }
   public string Name { get; set; }
   public string Picture { get; set; } // filename of the picture, e.g. apple.jpg
   public int CategoryID { get; set; }
}

Now we would query the database and retrieve the object, let’s say with values like this:

ID = 1
Name = Yellow apple
Picture = apple.jpg
CategoryID = 25

All perfectly normal. The thing I’m meditating about at the moment is this: if I want to show a product, I need some additional info that wasn’t queried from the database, like exact file path to the image, all we have is

apple.jpg

, but we need maybe something like

~/images/apple.jpg

---

So, I was thinking of 3 possibilities:

1.) add a new property to the class Product

public string PictureUrl
{
    get
    {
        return "~/images/apple.jpg";
    }
}

2.) specify the full url during performing of the presentation logic, let’s say:

public void ShowProductDetails()
{
    Product p = ProductRepo.GetProduct(id);

    txtName.Text = p.Name;
    imgPicture.ImageUrl = "~/images/" + p.Picture;
}

3.) use Decorator pattern

First approach seems wrong to me (even though I have been using it for quite a long time), because I’m trying to have a layered web application. I’m not sure hard-coding this is a good way to go.

Second approach is better, but worse in the sense it can’t be easily reused. If I have multiple places where I’m doing the same thing and something changes, … Maybe it would work if I specify some static constants holding the paths…

Third possibility seems quite complicated in terms of maintainability. The number of my classes would probably have to double. If I have 30 classes now, it would suddenly become 60 :/

What is the best/recommended way of doing things like this? If I add properties to my POCOs that aren’t included in the db schema, I’m unable to use Dapper.Contrib or Rainbow and similar libraries, because even though “selects” work fine, I can’t “insert” nor “delete”. I have to hard-code the sql strings for every command which becomes really tedious after some time, when you’re doing all the time the same stuff.

EDIT:

The solution from Govind KamalaPrakash Malviya is great, but can’t be used every time. I need a way to solve this for any type of properties, even those more complex ones – for instance the number of photos of some album. It’s a good idea to query the count of photos along with albums, but assign it to what? Create a decorated class using a Decorator pattern?

How do YOU solve this kind of architecture problems?

Answer

I normally solve this by leaving the entity object as it is and creating an extra data container, which will either hold a reference to the corresponding entity or implement the corresponding properties from the entity object itself. In the latter case I use a mapping library (AutoMapper) to copy data from an entity to a the enhanced container.

The logic for filling the extra properties normally lies in a factory (or factory method). It’s up to you, where you want to place this in your architecture. In a current project we are including them in our data access facade on client side, because we don’t want to clutter the data access layer with too many DTO’s. This of course means, that the data access layer still needs to support retrieving the extra properties. In your case an operation like int GetNumberOfPhotosForAlbum(Album album).

We found that the benefits outweigh the risk of an ever-growing contract of the data access layer, which of course might need to support many different calls like the example above instead of just EnhancedAlbum GetEnhancedAlbumWithAllKindsOfExtraProperties(long albumId). This might also become a performance problem in some scenarios, because of the overhead of an increased frequency of service calls. In the end you need to decide, what’s best for your project.

I like this approach, because my entities (Album) stay untouched and I retain a clear separation of concerns between persistence, client logic and mapping.

Example:

class Album
{
    string Name { get; set; }
}

class EnhancedAlbum
{
    Album Album { get; set; }
    int NumberOfPhotos { get; set; }
}

class EnhancedAlbumFactory
{
    private MyDataService _dataService;

    //include some means of constructing or (better) injecting the data service

    EnhancedAlbum GetEnhancedAlbum(Album album)
    {
        return new EnhancedAlbum
               {
                   Album = Album,
                   NumberOfPhotos = _dataService.GetNumberOfPhotosForAlbum(album);
               };
    }
}