Each type implementing IDataItem must have associated string (an SQL select qry) but access needed before type instantiated

Question

My application has a set of types implementing IDataItem. They are all simple "DTO" types representing database rows. Instances are created using PetaPoco (a micro ORM) by running a SQL select query against the database. The SQL select query is specific to the type, and also depends on the application version number (suppose type X has a property Y, but before app version N there is no corresponding column in the DB - in that case the query returns a default value for Y).

The nature of the application is that there are a LOT of these types and the number will grow in the future. I am trying to use generics in the data access layer to (i) minimise the amount of code and (ii) force future developers to design the necessary SQL queries as they are designing the type.

So, in the data access layer what I want is a method that:

• has a type parameter T:IDataItem
• returns an IEnumerable of T
• finds the SQL select query associated with T
• runs the query against the DB using PetaPoco to create the list of T (its a little more complex than that - there's also a dynamic where clause, but leaving that out to keep it simple)

My challenge is - how to retrieve the SQL query for the type parameter T before any instances of T are created. Ideally I would add a method to IDataItem, e.g. string SqlSelectFrom(int appVersion) but then I need an instance to call it (if only an interface could require a static member!). The best I have right now is a function inside the data access layer like this (but this doesn't really fulfil my requirement to force developers of future types to implement a SQL query):

    private string GetSqlSelectFrom<T>(int appVersion) where T : IDataItem
    {
        if (typeof(T) == typeof(ProductDto))
            return "select ProductId, ProductCode from Product";

        if (typeof(T) == typeof(ColourDto))
            return "select ColourId, ColourCode from Colour";

        etc

        throw new Exception("No SQL defined!");
    }

Is there a better pattern for accomplishing this?

Answer 1

One way is using a kind of registry for DTO types:

public static class DtoRegistry
{
    private static readonly Dictionary<Tuple<Type, int>, string> _sqlSelectByType =
        new Dictionary<Tuple<Type, int>, string>();

    public static void RegisterDto<T>(int appVersion, string sqlSelect) where T : IDataItem
    {
        var key = new Tuple<Type, int>(typeof(T), appVersion);
        _sqlSelectByType[key] = sqlSelect;
    }

    public static string GetSqlSelectFrom<T>(int appVersion) where T : IDataItem
    {
        var key = new Tuple<Type, int>(typeof(T), appVersion);
        return _sqlSelectByType[key];
    }
}

and somewhere in the application startup code, all DTO types must be registered:

DtoRegistry.RegisterDto<ProductDto>(appVersion: 1, sqlSelect: "SELECT * FROM Products");
DtoRegistry.RegisterDto<ProductDto>(appVersion: 2, sqlSelect: "SELECT * FROM ProductsV2");

DtoRegistry.RegisterDto<ColourDto>(appVersion: 1, sqlSelect: "SELECT * FROM Colours");
DtoRegistry.RegisterDto<ColourDto>(appVersion: 2, sqlSelect: "SELECT * FROM ColoursV2");

One caveat with the registry is thread safety. I would make application initialization run in single thread, filling the registry, and allow no changes to the registry once initialization is completed. Then during the application execution, multiple threads can safely call GetSqlSelectFrom concurrently.