使用 SqlDataReader 从连接表中获取值 [英] Get values from joined tables using SqlDataReader

问题描述

假设我们在数据库中有两个表，user(FK id_role)和role(PK 角色).我们需要阅读有关用户及其权限的信息.

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.

我使用以下 SQL 语句来执行查询:

I use the following SQL statement to perform query:

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

执行后，我尝试使用字符串索引器读取 reader 中的值:reader[string name].

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

我需要解决的问题是重复名称:user 和 role 都包含，例如，字段 id，我是能够读取用户(使用 reader["id"])，但不能读取角色(使用 reader["role.id"]).

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

属性FieldCount 返回12，这意味着读取了所有必需的字段(user 包含6 个字段，role 也包含6 个字段).

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

在这种情况下，我是否可以按名称读取列?还是只使用两个查询或 SQL 'as' 运算符?

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 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

然后使用 reader["user_id"] 和 reader["role_id"] 选择它们.

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

这篇关于使用 SqlDataReader 从连接表中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！