Clever Geek Handbook

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?

+3


source to share


4 answers


You can always explicitly select columns and specify 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"]

.

+10


source


The only reliable way is to use AS

in a SQL query to make sure the column names / aliases are unique ... otherwise you could access the fields by index (0/1/2, etc.) instead of by name.



+1


source 


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"];
+1


source


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.

+1


source






More articles:


All Articles