CODEWITHSUNDEEP
c#-4.0genericsdapper
Tomcat14
Tomcat14

Reputation: 43

Generalize Dapper query within a delegate, Func or Action C#

I have several kind of a Dapper queries like this below, with several different types as result. This is one specific from these, which produce e.g. List<ClassA> :

string anSql = GetSqlQueryText("query_name");
SqlConnection connection = GetSqlConnection();

List<ClassA> result = null;
try
{
    connection.Open();
    result = connection.Query<ClassA>(anSql, new    //want to move this part from here
    {
        param1 = "value1",
        param2 = "value2"
    }).ToList();                                    //to here out, to an outer call
}
catch //more error handling and retry logic omitted and this is a simplified version
{
    result = new List<ClassA>();       //this good to be filled up by the generic type
}
finally
{
    connection.Close();
}

I want to join this kind of queries into a GenDapperQuery<T> generic method, which can be called with the help of a delegate (or Func/Action or anything else) something like this (T will be ClassA or ClassB, etc. in the final code):

List<T> result = GenDapperQuery<T>(() =>
{
    result = connection.Query<T>(anSql, new
    {
        param1 = "value1",
        param2 = "value2"
    }).ToList();
}
//and I want to use the result then as a specific type e.g. ClassA
//either immediately or after a cast
result[0].Id = 3; //or
(result as List<ClassA>)[0].Id = 3;

So my purpose is to use the connection, my error handling/retry logic, and of course the Dapper query generally, multiple times (because I don't want to write them down as many as query and type I have), but I want to say to this (wanted) generic method somehow, what to query down with dapper and what type of (generic) list create and fill-up.

(This (wanted) generic method will be in the same class where I can create connection once. The error handling part will be more complicated, but always the same at every type, that's why I don't want to write them down multiple times. The parameters can vary freely just as sql string as an inputs.)

My problem now, that I cannot write a generic Dapper query surrounding my own code, but with a specific injected function from outside of this (wanted) method.

Is this possible in C#? Any suggestion would be highly appreciated.

Upvotes: 2

Views: 1848

Answers (2)

Tomcat14
Tomcat14

Reputation: 43

I'm posting my improvement -based on Ryan's good answer- also to hold these solutions together. This wrapper function is requesting not just the SQL connection but the name of the query also. So this solution is shorter with one line on the caller side and maybe a bit more elegant.
The modified wrapper function:

public TResult ExecuteWrapper<TResult>(SqlConnection connection, string queryName, Func<SqlConnection, string, TResult> func)
{
   string anSql = GetSqlText(queryName);
   TResult result;
   try
   {
      connection.Open();
      result = func(connection, anSql);
   }
   catch
   {
      result = System.Activator.CreateInstance<TResult>(); //this is working in .NET 4.5 environment
   }
   finally
   {
      connection.Close();
   }
   return result;
}

And the caller side of this:

List<ClassA> result = ExecuteWrapper(connection, "query_name", (conn, sql) =>
    {
        return conn.Query<ClassA>(sql, new
            {
                param1 = "value1",
                param2 = "value2"
            }).ToList();        
    });

Thanks again to Ryan for his answer.

Upvotes: 2

Ryan
Ryan

Reputation: 8005

There are many ways to accomplish this. One mechanism is to create a method for the Execute/ErrorHandler:

public TResult ExecuteWrapper<TResult>(SqlConnection connection, Func<TResult, SqlConnection> func)
{
    TResult result;
    try
    {
        connection.Open();
        // Query will be wrapped in a function or lambda
        result = func(connection);
    }
    catch //more error handling and retry logic omitted and this is a simplified version
    {
        // Specifying a new TResult may be more difficult. You could either:
        // 1. Pass a default value in the method parameter
        //    result = defaultValue; // defaultValue is method parameter
        // 2. Use System.Activator to create a default instance
        //    result = (TResult)System.Activator(typeof(TResult));

        // Original: result = new List<ClassA>(); // this good to be filled up by the generic type
    }
    finally
    {
        connection.Close();
    }
    return result;
}

Then you would use it like this:

List<ClassA> result = ExecuteWrapper(connection, (cn) =>
    {
        string anSql = GetSqlQueryText("query_name");
        return cn.Query<ClassA>(anSql, new
            {
                param1 = "value1",
                param2 = "value2"
            }).ToList();        
    });

Upvotes: 3

Related Questions