How to create an object from datareader from two tables (sql statemens)

Question

I have two classes Car and Drivers:

public class Car
{
    public string Status { get; set; }
    public IList<Driver> Event { get; set; }
}

public class Driver
{
    public string Name { get; set; }
    ....
}

I get the values from TWO SQL-Statemens (e.g. Select * from car; select * from driver). I can fill the first one with:

while (rdr.Read())
{
    _results.Add(new TaskModel(rdr));
    //new TaskModel(rdr); 
}

where _results is:

private ObservableCollection<Car> _results = new ObservableCollection<Car>();

Constructor for Car looks like:

public Car(IDataRecord record)
{
    this.CarId = Convert.ToInt32(record["CARID"]);
    this.Color = (string)record["COLOR"];
}

I'm asking myself how to fill the datareader with two statemens and refer in the constructor to get the whole object (Car with Driver)

Please adjust the sample code, it's really hard to tell what's going on with all the "Enter Code here" statements. — Brian Mains
– Brian Mains, Commented Jul 20, 2011 at 11:09

BennyM · Accepted Answer · 2011-07-20 11:10:54Z

4

How about retrieving all the data in one go, as in

SELECT * FROM Car INNER JOIN Driver ON Car.Id = Driver.CarId

And then create your object graph from there on?

Also I wouldn't use the datareader in the constructor, use a third object to construct the object from a database.

JOIN Fundamentels.

answered Jul 20, 2011 at 11:10

BennyM

2,8561 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nearshore Over a year ago

BennyM - many thanks for your answer. Could you give me a hint what you mean by saying "use a third object to construct"?

BennyM Over a year ago

Google either for data access object or repository if you want to do it more domain driven devolopment style. If you're just starting out a dao will probably be easier.

Eben Roux · Accepted Answer · 2011-07-20 11:27:45Z

0

You could have a look at the data mapper pattern. You are creating a tight coupling between your domain and data access. You don't want to do that :)

answered Jul 20, 2011 at 11:27

Eben Roux

13.4k3 gold badges31 silver badges51 bronze badges

Comments

Andreas Ågren · Accepted Answer · 2011-07-20 11:33:43Z

0

If you can't change the query to an inner join as @BennyM suggests then you can join the objects using LiNQ instead. First create one List<Car> Cars and one List<Driver> Drivers and fill them using datareader(s), then join them like this:

from c in Cars
join d in Drivers on d.CommonKey = c.CommonKey //Your code samples doesn't mention any key connecting the two objects, so please adjust this to whatever it should be.
select new { c, d }

This is of course a less good solution than fixing the actual SQL-query...

answered Jul 20, 2011 at 11:33

Andreas Ågren

3,94926 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to create an object from datareader from two tables (sql statemens)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related