How to use Dapper with derived types

Question

I have about 20 classes that are derived from ModelBase (which contains common things like CreationDate, Id etc).

Using Dapper, it is easy to save and delete these derived classes (all the work is done by the second-last line)

public static bool ModelDelete(ModelBase model, SqlConnection? conn = null, SqlTransaction? trans = null)
{
    bool connectionIsNew = (conn is null);
    bool transactionIsNew = (trans is null);

    try
    {
        if (connectionIsNew) 
            conn = new SqlConnection(Globals.connString);

        if (conn is null)
        {
            throw new Exception("Unable to establish a database connection in Data.ModelDelete");
            return false;
        }
        else
        {
             if (conn.State != System.Data.ConnectionState.Open) 
                 conn.Open();
        }

       if (transactionIsNew) 
           trans = conn.BeginTransaction();

        DynamicParameters p = new DynamicParameters();
        p.Add("Id", model.Id, DbType.Int32);
        p.Add("Error", null, DbType.String, ParameterDirection.Output, 8000);
        string spName = $"sp{model.GetType().Name}_Delete";
        int recordsAffected = conn.Execute(spName, p, trans);
    }
    catch (Exception exc)
    { }
}

But I don't seem to be able to do the same thing with a Get/Fetch command and end up with

public static ModelBase ModelGet(Type t, int id, SqlConnection? conn = null, SqlTransaction? trans = null, bool withDependents = false)
{
    bool connectionIsNew = (conn is null);
    bool transactionIsNew = (trans is null);
    ModelBase model = default!;

    try
    {
        if (connectionIsNew) 
           conn = new SqlConnection(Globals.connString);

        if (conn is null)
        {
             throw new Exception("Unable to establish a database connection in Data.ModelGet");
        }
        else
        {
            if (conn.State != System.Data.ConnectionState.Open) 
                conn.Open();

            if (transactionIsNew) 
                trans = conn.BeginTransaction();

            var p = new { Id = id };

            string spName = $"sp{t.Name}_Get";

            switch (t.Name)
            {
                case nameof(Account):
                    model = conn.QuerySingle<Account>(spName, p, trans);
                    break;

                case nameof(AccountGroup):
                    model = conn.QuerySingle<AccountGroup>(spName, p, trans);
                    break;

                case nameof(Address):
                    model = conn.QuerySingle<Address>(spName, p, trans);
                    break;

                case nameof(Agency):
                    model = conn.QuerySingle<Agency>(spName, p, trans);
                    break;

and so on with all the different derived model classes. This works but is inelegant. Is there a better way than having a separate conn.QuerySingle<> for each of my derived classes?

Answer 1

since you're depending on Type, you could just convert it into a generic method something like this :

public static T ModelGet<T>(int id, SqlConnection? conn = null, SqlTransaction? trans = null, bool withDependents = false)
where T : ModelBase, class
{
    try
    {
        if (conn is null) 
           conn = new SqlConnection(Globals.connString);

        if (conn.State != System.Data.ConnectionState.Open) 
            conn.Open();
        
        if (trans is null) 
            trans = conn.BeginTransaction();

        return conn.QuerySingle<T>($"sp{typeof(T).Name}_Get", new { Id = id }, trans);
    
    }
    catch(Exception)
    {
        
    }
    
    return null;
}

you can do the same on the Delete method as well.