how to query with Dapper with a type variable

Question

this is my setup

first, the base classes

public class TableBase
{
}

public class TableRepositoryBase
{
    private BindingSource _bindingSourceOnForm;

    public TableRepositoryBase(BindingSource bindingSource, string connectionString)
    {
        BindingSourceOnForm = bindingSource;
        ConnectionString = connectionString;
        SetupParameters();
    }

    protected string ConnectionString { get; set; }

    protected Type TableType { get; set; }

    protected BindingSource BindingSourceOnForm
    {
        get { return _bindingSourceOnForm; }
        set { _bindingSourceOnForm = value;}

    protected string SQLSelect { get; set; }

    protected virtual void SetupParameters()
    { }

    public virtual void FetchAll()
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            BindingSourceOnForm.DataSource = connection.Query<TableType>(SQLSelect).ToList();
        }
    }
}

now my inherited classes

public class TableUser : TableBase
{
    public int UserID { get; set; }
    public string UserFirst { get; set; }
    public string UserLastname { get; set; }
}

public class TableRepositoryUser : TableRepositoryBase
{
    public TableRepositoryUser(BindingSource bindingSource, string connectionString) : base(bindingSource, connectionString)
    { }

    protected override void SetupParameters()
    {
        base.SetupParameters();

        TableType = typeof(TableUser);
        SQLSelect = "select UserID, UserFirst, UserLastname from tblUser";
    }
}

Now the problem
In the base class TableRepositoryBase the following line of code in the method FetchAll() will not compile:

BindingSourceOnForm.DataSource = connection.Query<TableType>(SQLSelect).ToList();

The error is

Error CS0246 The type or namespace name 'TableType' could not be found (are you missing a using directive or an assembly reference?)

This will be because the query<> is looking for a type, and although TableType is declared as a type, it will probably see it as a variable here.

What I want to achieve is that the method FetchAll can be done entire by the baseclass.
But, I would like that the list that is put in BindingSourceOnForm.DataSource is not of type DapperRow but in this case of type TableUser.

I could override this method in TableRepositoryUser like this

public override void FetchAll()
{
    //base.FetchAll();
    
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        BindingSourceOnForm.DataSource = connection.Query<TableUser>(SQLSelect).ToList();
    }
}

That will work, now the list assigned to BindingSourceOnForm.DataSource if of type TableUser
But I would like to do this code in the base class, not in the inherited class.
How could I achieve this ?

EDIT
I am trying to adapt the answers from the link I got in the comments from @Flydog57 but with no success.
I have this so far

MethodInfo method = typeof(SqlConnection).GetMethod("Query");
MethodInfo generic = method.MakeGenericMethod(TableType);

BindingSourceOnForm.DataSource = generic.(SQLSelect).ToList();

I get a compile error Identifier expected
I guess this code makes no sense at all, but I dont understand how to use MakeGenericMethod to solve my problem, if it is even possible.

So some help here would be appreciated

EDIT 2

Since I can't find a solution, I am now using this inefficient method

public virtual void FetchAll()
{
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        if (TableType == typeof(TableUser))
            BindingSourceOnForm.DataSource = connection.Query<TableUser>(SQLSelect).ToList();
        else if (TableType == typeof(TableTask))
            BindingSourceOnForm.DataSource = connection.Query<TableTask>(SQLSelect).ToList();
    }
}

Still hoping for a better way because this means I have to come back here and alter the code everytime a new repository class is inherited from RepositoryBase

Answer 1

I'm assuming what you are trying to do is to translate this:

public virtual void FetchAll()
{
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        BindingSourceOnForm.DataSource = connection.Query<TableType>(SQLSelect).ToList();
    }
}

into functioning code, that calls connection.Query using the type represented by TableType as the generic parameter to the Query function.

Rather than using your abstract code, I'm using the SQL Pubs database sample (I had it on my laptop) and an Authors class I threw together. So the code I'm going to translate looks like this:

public string ConnectionString { get; set; } = @"Data Source=[MyLaptopName];Trusted_Connection=true";
public string SelectString { get; set; } = @"SELECT TOP (1000) [au_id], [au_lname], [au_fname],[phone],[address],[city],[state],[zip],[contract]FROM [pubs].[dbo].[authors]";

With an Authors class that looks like:

public class Authors
{
    public string au_id { get; set; }
    public string au_lname { get; set; }
    public string au_fname { get; set; }
    public string phone { get; set; }
    public string address { get; set; }
    public string city { get; set; }
    public string state { get; set; }
    public string zip { get; set; }
    public string contract { get; set; }
}

I can read the contents of that table with this minimalist code:

using (var conn = new SqlConnection(ConnectionString))
{
    var result = conn.Query<Authors>(SelectString);
}

So, starting with How do I use reflection to call a generic method? as a guide, the first roadblock is how to get a reference to the MethodInfo that represents the call to Query.

That's an extension method on SqlConnection that is implemented as a static method on the Dapper.SqlMapper class. It's generic and has a single type parameter. The particular override we are going to call has seven parameters (there are 11 Query methods defined in Dapper.SqlMapper - we need to pick the right one).

I'm getting the method this (rather brute-force) way:

private MethodInfo GetDapperQueryMethod()
{
    var dapperMapperType = typeof(Dapper.SqlMapper);
    //get the first method named "Query" that has a single generic type parameter and seven regular parameters
    var queryMethods = dapperMapperType.GetMethods(BindingFlags.Public | BindingFlags.Static)
        .Where(m => m.Name == "Query" && m.IsGenericMethod && m.GetGenericArguments().Length == 1 && m.GetParameters().Length == 7);
    return queryMethods.FirstOrDefault();
}

You might want to look at this for other ways of doing this: Reflection to Identify Extension Methods

Once we have the MethodInfo that represents the correct override of Query, the rest is reasonably simple. First, you need to fabricate the generic method, combining the generic method definition with the generic parameter type (TableType in your case, Authors in mine). You create that MethodInfo using MakeGenericMethod.

You need to call that method - but as a simple static method (reflection mostly doesn't care about extension methods). So, the first parameter is the connection. The final parameters are the default values that each of the parameters have (after the connection and the query string). So:

var method = GetDapperQueryMethod();
var authorsType = typeof(Authors);
var genericQuery = method.MakeGenericMethod(authorsType);
using (var conn = new SqlConnection(ConnectionString))
{
    var result = genericQuery.Invoke(null, new object[] {conn, SelectString, null, null, true, null, null});
}