Retrieving Values ββfrom Concatenated Tables Using SqlDataReader
Suppose we have two tables in the database, user
(FK id_role) and role
(PK role). We need to read the information about the user and his privileges.
I am using the following SQL statement to execute a query:
SELECT *
FROM [user]
INNER JOIN role ON [user].id_role = role.id
WHERE login = @login
After performing, I'm trying to read values in the reader via the indexer rows: reader[string name]
.
The problem I need to solve is repeating names: both user
and role
contain, for example, a field id
that I can read for the user (using reader["id"]
), but not able to read for the role (using reader["role.id"]
).
The property FieldCount
returns 12, which means that all required fields have been read ( user
contains 6 fields, which means role
).
Do I have the option to read columns by name in this case? Or using two queries or the SQL 'as' statement the only way?
source to share
Of course, you have to select the columns by name, but you need to do it like this:
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 tables.
Finally, use reader ["uid"], reader ["rid"], etc.
source to share