Can I vary the signature in any way when using the func<> delegate?

Question

I have a data access layer namespace that has a lot of repeated code (mostly setting up command objects and executing stored procedures to return data sets that get added to Lists). I'm trying to write a generic factory method that returns a List<T>. I'm relatively new to both generics and delegates. My thought was to have a Func parameter that creates the objects that get added to the List<T>. It would be called within the usual while dataReader.Read() block. My problem is, the data sets I'll be working with have varying numbers of columns.

Here is what I have so far:

private static List<T> ListFromDatabase<T,U>(String CommandName, SqlConnection SqlConn, Func<T,U> CreateListObj, 
    List<SqlParameter> ParamList = null)
{
    List<T> returnList = new List<T>();

    using (SqlCommand cmd = new SqlCommand(CommandName,SqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter sp in ParamList)
        {
            if (sp.Value == null)
                sp.Value = DBNull.Value;
            cmd.Parameters.Add(sp);
        }

        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            returnList.Add(CreateListObj);
        }
    }

    return returnList;
}

At my current level of understanding, this may not be possible since I would likely have to vary the Func signature for varying numbers of parameters. Is this do- able, or close? If not I'm open to a different approach, if anyone can help. Many thanks in advance..

Update: This is exactly what I was looking for:

Convert rows from a data reader into typed results

I would do something similar to what you have proposed. I'm left wondering, what aren't you happy with in your implementation? — Brian Dishaw, Aug 14 '12 at 19:34
@BrianDishaw I don't know how to "flesh out"/ implement the func delegate, especially since the #of parameters would vary.. — StatsViaCsh, Aug 14 '12 at 19:37
What is the `U` type parameter for? This won't compile since you're trying to add a `Func` to a `List`. It sounds like you want a `Func` and to pass the reader to the func to create and hydrate an instance of `T`? — Lee, Aug 14 '12 at 19:41
@Lee Yup, won't compile, that's where i'm stuck.. I know the U is unaccounted for at the moment.. you're close on your suggestion, but then I'd have to iterate within the function passed for the delegate, I was hoping to call the delegate within the iteration happening in the factory. — StatsViaCsh, Aug 14 '12 at 20:03
@StatsViaCsh - The iteration would happen in the `ListFromDatabase` method: `while(sdr.Read()) { returnList.Add(func(sdr)); }` — Lee, Aug 14 '12 at 20:13

score 1 · Answer 1 · answered Aug 14 '12 at 19:39

The most elegant way to do what you want is to specify T as a Tuple<T1,T2,T3...> by passing a function that accepts a Tuple with a particular number and type of items, and then using Tuple.Create to generate that Tuple using the contents of a row from sdr. The problem is that when getting fields out of a DataReader, they come out as objects of undetermined type, and the safest way to pass them on when you don't know their real type is as strings, meaning the CreateListObj function must expect strings and be able to parse them correctly (thus expecting a specific column order in the Tuple).

score 1 · Accepted Answer · answered Aug 14 '12 at 19:42

1

Why not pass your SqlDataReader to your delegate as an IDataRecord? The delegate can get what it needs from the data record; you maintain control over iterating over the reader.

answered Aug 14 '12 at 19:42

Nicholas Carey

71,308
16
93
135

I wasn't familiar with IDataRecord. Looking into that now, thx. – StatsViaCsh Aug 14 '12 at 20:06

score 1 · Answer 3 · answered Aug 14 '12 at 19:47

What I've done in the past is to define "builder" methods that take an IDataReader and return an object of the type they're defined in terms of (I've had them as methods of generic classes rather than delegates but Func<IDataReader, T> would do the same thing.

Generally, I've used CommandBehavior.CloseConnection on the ExecuteReader call, and then returned a class that held onto the reader, implemented IEnumerable<T> and then closed the reader (and hence the underlying connection) when it was disposed rather than creating an entire list first (you do have to be careful it gets cleaned up if the enumerator isn't actually enumerated at all).

The advantage is that I don't have to worry about differences in column number and type at the point of definition, only at the point of implementation (where I can't help but worry about it).

Interesting approach, I don't know if I can implement it exactly as you describe but I'll take a whack at it. — StatsViaCsh, Aug 14 '12 at 20:11

Can I vary the signature in any way when using the func<> delegate?

3 Answers3