Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Get more than one table

Author  Topic 

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-24 : 14:12:38
Hi,

I have various related tables:
[A] > Aid (PK), Title, Name, ...
[B] > Bid (PK), Aid (FK), B
...

I need to get the records from various tables given an @Aid and populate a class that has properties as follows:
Title;
Name;
Generic.List(Of B);
...

For what I have been reading there are two options:
1. Use one select, with joins, getting only one table in my dataset;
2. Use one select for each table getting various tables in my dataset.

Which is the best option?

How can I do this in my C# code?

Thanks,
Miguel

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-24 : 23:38:03
Depends on columns you need and table schema, looks like you need join based on what you said.
Go to Top of Page

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-25 : 07:15:58
Yes, I know.

My problem is not the SQL is how to access the values in C#.

Let me be specific and explain what I did:

I have an SQL Stored Procedure that uses Inner Join to return the records of various related tables:

SELECT *
FROM dbo.by27_Blogger_Posts p
LEFT JOIN (
SELECT *
FROM dbo.by27_Blogger_Comments c
LEFT JOIN dbo.by27_Membership_Users u
ON c.AuthorId = u.UserId) AS cu
ON p.PostId = cu.PostId
...
WHERE p.PostId = @PostId
ORDER BY p.PostUpdatedDate

Now I get a dataset with all this data.

How can I, for example, loop through each PostId? And for each PostId loop through each CommentId associated to it? And of course access the columns?

The SQL code section I post applies to the 2 tables:

[Posts] > PostId, Title, ...

[Comments] > CommentId, PostId, Body, ...

Or should I use various Selects?
This was what I was talking about!

Thanks,

Miguel
Go to Top of Page
   

- Advertisement -