Get values from joined tables using SqlDataReader

.net, ado.net, c#, sql-server, sqldatareader

Solution

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"]`.

Problem

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?

Original source