Get values from joined tables using SqlDataReader

Question

Imagine we have two tables in the database, user (FK id_role) and role (PK role). We need to read information about user and his privileges.

I use the following SQL statement to perform query:

SELECT * 
FROM [user] 
INNER JOIN role ON [user].id_role = role.id 
WHERE login = @login

After executing, I try to read values in reader using string indexer: reader[string name].

The problem which I need to resolve is repeating names: both user and role contain, e.g., the field id, which I am able to read for user (using reader["id"]), but not able to read for role (using reader["role.id"]).

The property FieldCount returns 12, which means that all required fields were read (user contains 6 fields, so does role).

Do I have a possibility to read columns by name in this case? Or using two queries or SQL 'as' operator in the only way?

You need to define column aliases in your SELECT - or read by ordinal position (Reader[index]) - but that of course is prone to other problems (like User table gets one more property and then your orders are all off by 1) - and those could be solved by not using SELECT * - but explicitly listing all the columns you really need and want to select from those two tables... — marc_s
– marc_s, Commented Apr 8, 2012 at 18:46
Of course reading by position is uncomfortable a bit... Probably aliases is the answer. But I'm confused why datareader doesn't add table name before columns. — Eadel
– Eadel, Commented Apr 8, 2012 at 18:51

marc_s · Accepted Answer · 2012-04-08 18:48:25Z

12

You could always select the columns explicitly and alias them:

SELECT user.id AS user_id, user.*, role.id AS role_id, role.* 
FROM [user] 
INNER JOIN role ON [user].id_role = role.id 
WHERE login = @login

Then select them with reader["user_id"] and reader["role_id"].

edited Apr 8, 2012 at 18:48

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 8, 2012 at 18:47

McGarnagle

103k31 gold badges235 silver badges264 bronze badges

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

3 Comments

sarwar026 Over a year ago

Just a curiosity to know: does it show user_id column two times? and same for the role_id column?? Thanks.

McGarnagle Over a year ago

@sarwar026, yeah, in what I wrote there both ID columns would be selected twice.

Eadel Over a year ago

It's a nice way and probably the easiest one, thanks! I guess the best way to avoid such problems is to provide tables' columns with unique names.

Yahia · Accepted Answer · 2012-04-08 18:48:24Z

1

The only robust way is to use AS in the SQL-query to make sure your column names/aliases are unique... otherwise you would to access the fields by index (0 / 1 / 2 etc.) instead of by name.

answered Apr 8, 2012 at 18:48

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Comments

Joe · Accepted Answer · 2012-04-08 18:48:37Z

1

SELECT role.id as RoleId, user.id as UserId
FROM [user] 
INNER JOIN role ON [user].id_role = role.id 
WHERE login = @login

Using AS

reader["RoleId"];

answered Apr 8, 2012 at 18:48

Joe

82.8k18 gold badges130 silver badges147 bronze badges

1 Comment

zwcloud Over a year ago

Is there a solution not using AS?

sarwar026 · Accepted Answer · 2012-04-08 19:00:25Z

1

Of course, you should select columns by name, but you need to do it like:

SELECT u.id as uid, r.id as rid, ... , FROM [user] u INNER JOIN role r ON [user].id_role = role.id WHERE login = @login

With , ... , I want to select the remaining column of both the tables.

Finally, use reader["uid"], reader["rid"] etc.

edited Apr 8, 2012 at 19:00

answered Apr 8, 2012 at 18:49

sarwar026

3,8413 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Get values from joined tables using SqlDataReader

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related