C#: how can I overload a method to accept several types but duplicate as little code as possible?

Question

Instances of SqlDataReader can be indexed by both integers and strings (see below: reader[col]). So, I want the wrapping function to accept both List<string> and List<int> as an argument. I really don't want type-checking at runtime, though, and, as C# doesn't have something like type unions, I assume overloading is the best way to allow a function to accept different types without involving generics and at-runtime type-checking.

So... I can easily just copy the method with List<int> instead of List<string> and overload it that way, but I don't want (and shouldn't) duplicate code like this. There must be a better way.

public List<List<object>> Query(string query, List<string> relevantColumns)
    {
        var rows = new List<List<object>>();

        using (SqlConnection connection = 
            new SqlConnection(this.ConnectionString))
        {
            var command = new SqlCommand(query, connection);
            command.Connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    List<object> row = (from col
                                        in relevantColumns
                                        select reader[col]).ToList();
                    rows.Add(row);
                }
            }
        }
        return rows;
    }

Answer 1

Instead of asking for columns in a List<object>, we can take this a step further and create a collection of the specific target type. For example, let's say you are querying an Employee table, and you need to fill an Employee object with columns for EmployeeNumber, FirstName, and LastName. The class looks like this:

public class Employee
{
    public int EmployeeNumber {get; set;}
    public string FirstName {get;set;}
    public string LastName {get;set;}
}

And you have a query like this:

SELECT * 
FROM Employee 
WHERE HireDate<=current_timestamp and COALESCE(TermDate,'29991231') > current_timestamp

We can make a basic method like this:

private IEnumerable<T> GetData<T>(string SQL, Func<IDataRecord, T> translate, Action<SqlParameterCollection> addParams)
{
    using var con = new SqlConnection(this.ConnectionString);
    using var cmd = new SqlCommand(SQL, con);

    if(addParams is object) addParams(cmd.Parameters);

    con.Open();
    using var rdr = cmd.ExecuteReader();
    while(rdr.Read())
    {
        yield return translate(rdr);
    }    
    rdr.Close();
}

Notice the method is private. Now we also add a public method like this:

public IEnumerable<Employee> GetEmployees()
{
    string SQL = @"
SELECT EmployeeNumber, FirstName, LastName 
FROM Employee 
WHERE HireDate<=current_timestamp and COALESCE(TermDate,'29991231') > current_timestamp";

    return GetData(SQL, row => new Employee {
            EmployeeNumber = row["EmployeeNumber"],
            FirstName = row["FirstName"],
            LastName = row["LastName"]        
        }, null);

}

Now we have pushed the column mapping into the translate() lambda, and we get back typed results, instead of just an Object. Notice I also specified the column list in the SQL. You should be doing this with every query.

Let's look at one more example that uses the parameter lambda:

public Employee GetEmployeeByNumber(int EmployeeNumber)
{
    string SQL = @"
SELECT EmployeeNumber, FirstName, LastName 
FROM Employee 
WHERE EmployeeNumber = @EmployeeNumber";

    return GetData(SQL, row => new Employee {
            EmployeeNumber = row["EmployeeNumber"],
            FirstName = row["FirstName"],
            LastName = row["LastName"]        
        }, p => {
            p.Add("@EmployeeNumber", SqlDbType.Int).Value = EmployeeNumber;
        }).FirstOrDefault();
}

We see how this also lets us safely include parameter data for the query. The original method would have practically forced you to write unsafe code.

Answer 2

You could have a third overload which takes a Func<SqlDataReader, List<object>>. Then call the other two overloads in terms of that overload. Something like...

public static class SqlUtilities
{
    public static List<List<object>> Query(string query, List<string> relevantColumns)
    {
        return Query(query, reader => (from col in relevantColumns select reader[col]).ToList());
    }

    public static List<List<object>> Query(string query, List<int> relevantColumns)
    {
        return Query(query, reader => (from col in relevantColumns select reader[col]).ToList());
    }

    private static List<List<object>> Query(
        string query, 
        Func<SqlDataReader, List<object>> selector)
    {
        var rows = new List<List<object>>();

        using (SqlConnection connection = 
            new SqlConnection(this.ConnectionString))
        {
            var command = new SqlCommand(query, connection);
            command.Connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    List<object> row = selector(reader);
                    rows.Add(row);
                }
            }
        }
        return rows;
    }
}

In this specific case, however, my opinion is that a better way to solve it would be to rewrite query if that is an option. relevantColumns as a list of names or a list of ordinals is unnecessary if query is only selecting the relevant columns.