Avoid code duplication when reading/serializing from DbDataReader?

Question

Consider the FetchData method below. It is designed to avoid duplicating the database query code every time you want to fetch some data from the database.

List<MyData> myData = new List<MyData();
FetchData((IDataReader reader) =>
   {
       myData.Add(new MyData(reader.GetString(0), reader.GetInt32(1)));
   }, "usp_get_my_data");

It works, but it would be nice if this object creation could somehow live inside the FetchData method.

Is there a better approach?

Perhaps FetchData can be modified to return a list of some type X directly?

protected void FetchData(Action<IDataReader> processor, String query)
{
    using (var connection = CreateConnection())
    {
        connection.ConnectionString = ConnectionString;
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = query;

            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.read())
                {
                    processor(reader);
                }
            }
        }
    }
}

The problem would be that FetchData would need to now things about the object that it will be creating to achieve that. Why not make the object creation a part of the actual object (in this case MyData) using an Interface of some sorts and pass that to FetchData. — Adriaan Stander, Sep 14 '12 at 12:12
You mean like a factory? And a template to allow for different data types? — l33t, Sep 14 '12 at 12:14
The processor Action that was passed to FetchData, should then live inside MyData onbject. — Adriaan Stander, Sep 14 '12 at 12:17

score 2 · Accepted Answer · answered Sep 14 '12 at 12:14

Using generics maybe?

protected IEnumerable<T> FetchData<T>(Func<IDataReader, T> processor, String query)
{
    using (var connection = CreateConnection())
    {
        connection.ConnectionString = ConnectionString;
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = query;

            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.read())
                {
                    yield return processor(reader);
                }
            }
        }
    }
}

Then you can use the enumeration however you want:

var myData = FetchData<MyData>(reader => new MyData(reader.GetString(0), reader.GetInt32(1)), "usp_get_my_data").ToList();

score 1 · Answer 2 · answered Sep 14 '12 at 12:28

1

You might take a look at Dapper.Net which is single file ORM with three different helpers:

Execute a query and map the results to a strongly typed List
Execute a query and map it to a list of dynamic objects
Execute a Command that returns no results

Design patterns used to build this miro-ORM are so helpful. Dapper.Net is currently being used in StackOverflow.

answered Sep 14 '12 at 12:28

Alireza Maddah

5,718
2
21
25

Interesting! Will look into this in future implementations! – l33t Sep 14 '12 at 12:36

Avoid code duplication when reading/serializing from DbDataReader?

2 Answers2