Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert rows from a data reader into typed results

I'm using a third party library which returns a data reader. I would like a simple way and as generic as possible to convert it into a List of objects.
For example, say I have a class 'Employee' with 2 properties EmployeeId and Name, I would like the data reader (which contains a list of employees) to be converted into List< Employee>.
I guess I have no choice but to iterate though the rows of the data reader and for each of them convert them into an Employee object that I will add to the List. Any better solution? I'm using C# 3.5 and ideally I would like it to be as generic as possible so that it works with any classes (the field names in the DataReader match the property names of the various objects).

like image 536
Anthony Avatar asked Jul 29 '09 20:07

Anthony


People also ask

What is difference between SqlDataReader and SQlDataAdapter?

A SqlDataAdapter is typically used to fill a DataSet or DataTable and so you will have access to the data after your connection has been closed (disconnected access). The SqlDataReader is a fast forward-only and connected cursor which tends to be generally quicker than filling a DataSet/DataTable.

What is the difference between DataReader and DataSet?

DataReader provides faster performance, but has read-only and forward-only access. DataSet, on the other hand, is high resource-consuming, but offers more control and a disconnected nature.

What is the use of SqlDataReader in C#?

The SqlDataReader is used to read a row of record at a time which is got using SqlCommand. It is read only, which means we can only read the record; it can not be edited. And also it is forward only, which means you can not go back to a previous row (record).


2 Answers

Do you really need a list, or would IEnumerable be good enough?

I know you want it to be generic, but a much more common pattern is to have a static Factory method on the target object type that accepts a datarow (or IDataRecord). That would look something like this:

public class Employee {     public int Id { get; set; }     public string Name { get; set; }      public static Employee Create(IDataRecord record)     {         return new Employee         {            Id = record["id"],            Name = record["name"]         };     } } 

.

public IEnumerable<Employee> GetEmployees() {     using (var reader = YourLibraryFunction())     {        while (reader.Read())        {            yield return Employee.Create(reader);        }     } } 

Then if you really need a list rather than an IEnumerable you can call .ToList() on the results. I suppose you could also use generics + a delegate to make the code for this pattern more re-usable as well.

Update: I saw this again today and felt like writing the generic code:

public IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> BuildObject) {     try     {         while (reader.Read())         {             yield return BuildObject(reader);         }     }     finally     {          reader.Dispose();     } }  //call it like this: var result = GetData(YourLibraryFunction(), Employee.Create); 
like image 68
Joel Coehoorn Avatar answered Sep 18 '22 21:09

Joel Coehoorn


You could build an extension method like:

public static List<T> ReadList<T>(this IDataReader reader,                                    Func<IDataRecord, T> generator) {      var list = new List<T>();      while (reader.Read())          list.Add(generator(reader));      return list; } 

and use it like:

var employeeList = reader.ReadList(x => new Employee {                                                Name = x.GetString(0),                                                Age = x.GetInt32(1)                                         }); 

Joel's suggestion is a good one. You can choose to return IEnumerable<T>. It's easy to transform the above code:

public static IEnumerable<T> GetEnumerator<T>(this IDataReader reader,                                                Func<IDataRecord, T> generator) {      while (reader.Read())          yield return generator(reader); } 

If you want to automatically map the columns to properties, the code idea is the same. You can just replace the generator function in the above code with a function that interrogates typeof(T) and sets the properties on the object using reflection by reading the matched column. However, I personally prefer defining a factory method (like the one mentioned in Joel's answer) and passing a delegate of it into this function:

 var list = dataReader.GetEnumerator(Employee.Create).ToList(); 
like image 33
mmx Avatar answered Sep 20 '22 21:09

mmx