ADO.NET build complex objects using DataReader

Question

I'm trying to create a way to make an unique search into the database and build the right object for my needs. I mean, I use a SQL query that returns me a lot of rows and then I build the collections based on that database rows. E.g.:

We have a table called People and another table called Phones.

Let's suppose that this is my SQL query and will return the following below:

SELECT 
   P.[Id], P.[Name], PH.[PhoneNumber]
FROM
   [dbo].[People] P
INNER JOIN 
   [dbo].[Phones] PH ON PH.[Person] = P.[Id]

And that's the results returned:

1   NICOLAS    (123)123-1234
1   NICOLAS    (235)235-2356

So, my class will be:

public interface IModel {
    void CastFromReader(IDataReader reader);
}

public class PhoneModel : IModel {
    public string PhoneNumber { get; set; }

    public PhoneModel() {  }
    public PhoneModel(IDataReader reader) : this() {
         CastFromReader(reader);
    }

    public void CastFromReader(IDataReader reader) {
        PhoneNumber = (string) reader["PhoneNumber"];
    }
}

public class PersonModel : IModel {
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<PhoneModel> Phones { get; set; }

    public PersonModel() {  
        Phones = new List<PhoneModel>();
    }

    public PersonModel(IDataReader reader) : this() {
         CastFromReader(reader);
    }

    public void CastFromReader(IDataReader reader) {
        Id = Convert.ToInt32(reader["Id"]);
        Name = (string) reader["Name"];

        var phone = new PhoneModel();
        phone.CastFromReader(reader);
        Phones.Add(phone);

        // or
        Phones.Add(new PhoneModel {
            PhoneNumber = (string) reader["PhomeNumber"]
        });
    }
}

This code will generate a PersonModel object with two phone numbers. That's good so far.

However, I'm struggling to make some good way to deal when I want to manage more tables with this process.

Let's suppose, then, I have a new table called Appointments. It stores the user's appointments to the schedule.

So, adding this table to the query, the result will be:

1   NICOLAS   (123)123-1234    17/09/2014
1   NICOLAS   (123)123-1234    19/09/2014
1   NICOLAS   (123)123-1234    27/09/2014
1   NICOLAS   (235)235-2356    17/09/2014
1   NICOLAS   (235)235-2356    19/09/2014
1   NICOLAS   (235)235-2356    17/09/2014

As you guys can see, the problem is to manage the phones and the appointments this way. Do you can think in anything that could solve this issue?

Thank you all for the opinions!

Answer 1

If you want to manually write a data type for each combination of columns resulting from your queries, then you have a lot of work to do, and you will end up with lots of very similar, but slightly different classes that are hard to name. Note also that these data types should not be treated as something more than what they are: Data Transfer Objects (DTOs). They are not real domain objects with domain-specific behaviour; they should just contain and transport data, nothing else.

What follows are two suggestions, or ideas. I will only scratch at the surface here and not go into too many details; since you haven't asked a very specific question, I won't provide a very specific answer.

1. A better approach might be to determine what domain entity types you've got (e.g. Person, Appointment) and what domain value types you have (e.g. Phone Number), and then build an object model from that:

struct PhoneNumber { … }

partial interface Person
{
    int Id { get; }
    string Name { get; }
    PhoneNumber PhoneNumber { get; }
}

partial interface Appointment
{
    DateTime Date { get; }
    Person[] Participants { get; }
}

and then have your database code map to these. If, for example, some query returns a Person Id, Person Name, Phone Number, and an Appointment Date, then each attribute will have to be put into the correct entity type, and they will have to be linked together (e.g. via Participants) correctly. Quite a bit of work. Look into LINQ to SQL, Entity Framework, NHibernate or any other ORM if you don't want to do this manually. If your database model and your domain model are too different, even these tools might not be able to make the translation.

2. If you want to hand-code your data query layer that transforms data into a domain model, you might want to set up your queries in such a way that if they return one attribute A of entity X, and entity X has other attributes B, C, and D, then the query should also return these, such that you can always build a complete domain object from the query result. For example, if a query returned a Person Id and a Person Phone Number, but not the Person Name, you could not build Person objects (as defined above) from the query because the name is missing.

This second suggestion will at least partially save you from having to define lots of very similar DTO types (one per attribute combination). This way, you can have a DTO for a Person record, another for a Phone Number record, another for an Appointment record, perhaps (if needed) another for a combination of Person and Phone Number; but you won't need to distinguish between types such as PersonWithAllAttributes, PersonWithIdButWithoutNameOrPhoneNumber, PersonWithoutIdButWithPhoneNumber, etc. You'll just have Person containing all attributes.

Answer 2

You cannot transfer your query result to strongly typed objects without first defining these objects' types. If you want to keep query data in memory, I recommend that you transfer it into objects of a previously defined type at some point.

What follows is therefore not something that I would actually recommend doing. But I want to demonstrate to you a possibility. Judge for yourself.

As I suggested in a previous comment, you can mimick strongly typed DTOs using the Dynamic Language Runtime (DLR), which has become available with .NET 4.

Here is an example for a custom DynamicObject type that provides a seemingly strongly-typed façade for a IDataReader.

using System.Data;
using System.Dynamic; // needs assembly references to System.Core & Microsoft.CSharp
using System.Linq;

public static class DataReaderExtensions
{
    public static dynamic AsDynamic(this IDataReader reader)
    {
        return new DynamicDataReader(reader);
    }

    private sealed class DynamicDataReader : DynamicObject
    {
        public DynamicDataReader(IDataReader reader)
        {
            this.reader = reader;
        }

        private readonly IDataReader reader;

        // this method gets called for late-bound member (e.g. property) access   
        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            int index = reader.GetOrdinal(binder.Name);
            result = index >= 0 ? reader.GetValue(index) : null;
            return index >= 0;
        }
    }
}

Then you can use it like this:

using (IDataReader reader = someSqlCommand.ExecuteReader(…))
{
    dynamic current = reader.AsDynamic(); // façade representing the current record
    while (reader.Read())
    {
        // the magic will happen in the following two lines:
        int id = current.Id; // = reader.GetInt32(reader.GetOrdinal("Id"))
        string name = current.Name; // = reader.GetString(reader.GetOrdinal("Name"))
        …
    }
}

But beware, with this implementation, all you get is a façade for the current record. If you want to keep data of several records in memory, this implementation won't help a lot. For that purpose, you could look into several further possibilities:

• Use anonymous objects: cachedRecords.Add(new { current.Id, current.Name });. This is only any good if you access the cachedRecords in the same method where you build it, because the anonymous type used will not be usable outside of the method.

• Cache current's data in an ExpandoObject.