Reading from multiple tables in C# - how?

Question

5.00/5 (3 votes)

See more:

Hi.

I guess this is a very simple question, but I really can't find the answer that suits my needs!

What I have is 2 tables in a MySQL database:

Families
- FamilyId
- City
- Phone

Members
- FamilyId
- Birtdate

The family id is of course the primary key, that binds members to a family.
So what I want is to get the right family row, and all member rows that belongs to this family like this:

string Query="SELECT * FROM `Families`, `Members` WHERE `Families`.`FamilyId`='123' AND `Members`.`FamilyId`='123';";

But how do I iterate over the result, so that I can create the corresponding family object, and the corresponding Member objects that I have in my C# application?

MysqlConnection.Open();
MySqlCommand MysqlCommand=new MySqlCommand(Query,MysqlConnection);
MysqlReader=MysqlCommand.ExecuteReader();

while (MysqlReader.Read()) {
  //what should I do here?
}

Thanks a lot!

Posted 21-May-11 10:32am

lvq684

Updated 22-May-11 0:28am

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 22-May-11 6:29am

Good Question, Minor edi for grammar.

2 solutions

Solution 1

Well, as you iterate through, read the 5 values, and call the two constructors taking 3 values each, and put them in a list, perhaps checking first that they are not there already.

Posted 21-May-11 10:41am

Christian Graus

Comments

lvq684 21-May-11 17:03pm

Thanks for your answer.
I´m not that much into database programming, and tried to fiddle with the Nextresult(), which always returns false. And that is wierd because if I execute the same query in the SQL console, I get several row results.

I dont wanna sound rude or lazy, but can you please provide a code example?

Thanks alot! :)

Christian Graus 21-May-11 17:59pm

I was too late. I will add, the code you were given will not work unless your constructor takes 3 objects ( which would be hideous ). You need to cast them to the desired type.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kim Togo · Accepted Answer · 2011-05-21T11:39:00

As you execute the query

SQL

"SELECT * FROM `Families`, `Members` WHERE `Families`.`FamilyId`='123' AND `Members`.`FamilyId`='123'"

You join the to tables Families and Members into 1 result set.

The output result will be

FamilyId,City,Phone,FamilyId,Birtdate

Ending up with 5 column on each row returned.

In your while loop you can then do:

C#

while (reader.Read())
{
  FamiliesObject f = new FamiliesObject((int)reader[0], (string)reader[1], (string)reader[2]); // Column FamilyId, City, Phone
  MembersObject m = new MembersObject((int)reader[3], (DateTime)reader[4]); // Column FamilyId and Birtdate
}

You have to create FamiliesObject and MembersObject class. And FamiliesObject could look like this.

C#

public class FamiliesObject
{
  public int FamilyId;
  public string City;
  public string Phone;

  public FamiliesObject(int familyId, string city, string phone)
  {
    this.FamilyId = familyId;
    this.City = city;
    this.Phone = phone;
  }
}

Hope you get the idea :-)

Reading from multiple tables in C# - how?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Reading from multiple tables in C# - how?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us