Reading DataReader Results into Object with List property Help!

[beantownace]

Hello all,

I am trying to see what is the best way to handle this in a proficient manner. I have a SQLDataReader that is returning a flattened result set where I am trying to group the data and add to a List property a few of the fields in the result set. I found a forum post using IDataReader with SelectRows with Group but I am not seeing SelectRows as an option.

The result set I get to avoid multiple query calls is a flattened reader return of data example query return:
Customer1, Maryland, Active, 123, 55.55
Customer1, Maryland, Active, 456, 77.89
Customer1, Maryland, Inactive, 789, 99.43
Customer2, Seattle, Active, 321, 77.77

Here is what I have below for the classes and I am trying to group the results by name, location, status then add to the list of invoices for that customer using the GetString etc.

public class Customer
{
    public string name { get; set; }
    
    public string location { get; set; }
    
    public string status { get; set; }
    
    public List<Invoice> { get; set; }
}

public class Invoice
{
    public string invoiceNumber { get; set; }
    
    public decimal? amount { get; set; }
}

Thanks all for any help if you have an example how I can do this.

 

[jmcilhinney]

Based on the data you have shown, you would have two Customer objects with a name of "Customer1" and location of "Maryland" because you have two different status values. Is that correct?

 

[jmcilhinney]

By the way, the code you posted would not compile because your list property has no name. Presumably it should be Invoices. You really ought to be copying working code from VS and posting it here if you possibly can. Also, while you don't have to, you really should start property names with an upper case letter.

 

[jmcilhinney]

Here's how I might do it. Firstly, my data classes would look like this:

public class Customer
{
    public string Name { get; set; }

    public string Location { get; set; }

    public string Status { get; set; }

    public List<Invoice> Invoices { get; } = new List<Invoice>();
}

public class Invoice
{
    public string InvoiceNumber { get; set; }

    public decimal? Amount { get; set; }
}

I would then read the data into those classes like so:

var customers = new List<Customer>();
Customer customer = null;

using (var reader = command.ExecuteReader())
{
    var name = reader.GetString(reader.GetOrdinal("Name"));
    var location = reader.GetString(reader.GetOrdinal("Location"));
    var status = reader.GetString(reader.GetOrdinal("Status"));

    // The check for Name includes a null check.
    if (customer?.Name != name ||
        customer.Location != location ||
        customer.Status != status)
    {
        // Create a new Customer when a new set of key values are detected.
        customer = new Customer
                   {
                       Name = name,
                       Location = location,
                       Status = status
                   };
        customers.Add(customer);
    }

    customer.Invoices.Add(new Invoice
                          {
                              InvoiceNumber = reader.GetString(reader.GetOrdinal("InvoiceNumber")),
                              Amount = reader.IsDBNull(reader.GetOrdinal("Amount"))
                                            ? (decimal?)null
                                            : reader.GetDecimal(reader.GetOrdinal("Amount"))
                          });

This assumes that the query results are sorted by the three key columns.