如何使用 SELECT * 访问 SQL 查询中的不明确列

发布于 2024-10-02 17:22:05 字数 451 浏览 0 评论 0原文

当我执行存储过程时，

SELECT * FROM Users 
INNER JOIN BloodBankUser ON Users.UserID = BloodBankUser.UserID

它给出的结果很好。

但现在在 .net 方面，

dt.Rows[0]["Address"].ToString();

这给了我表 BloodBankUser 的地址，

dt.Rows[0]["Users.Address"].ToString();

当我调试此语句时，它执行错误

列“Users.Address”不属于表。

如何获取 Users.Address 的值

原文

When I execute Stored Procedure

SELECT * FROM Users 
INNER JOIN BloodBankUser ON Users.UserID = BloodBankUser.UserID

It gives me result fine.

but now on .net side

dt.Rows[0]["Address"].ToString();

this gives me Address of table BloodBankUser

dt.Rows[0]["Users.Address"].ToString();

when I debug this statement it execute error

Column 'Users.Address' does not
belong to table.

How can I get Value of Users.Address

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼趣 2024-10-09 17:22:05

虽然第一个答案是更改 SQL 查询，为每个字段指定不同的名称，但仍然可以检索与您的字段关联的表名称。

在此示例中，我没有使用 DataAdapter 填充 DataTable，而是使用 SqlDataReader。

请注意，如果您因任何原因无法检索数据库架构，这可能会失败

在 SqlCommand 上调用 ExecuteReader 时，会出现过载它允许您指定CommandBehavior。在我们的例子中，我们想要的行为是CommandBehavior.KeyInfo。

var reader = command.ExecuteReader(CommandBehavior.KeyInfo);

现在，在阅读器上，您可以调用 GetSchemaTable 方法。它返回一个包含查询结构的DataTable。

var schema = reader.GetSchemaTable();

您可以在 MSDN 上了解该表。

我们现在的目标是将字段和表与其在列列表中的顺序位置进行匹配。架构表中的三个字段与您的兴趣相关：

ColumnName
BaseTableName
ColumnOrdinal

然后您可以创建一个扩展方法来执行该读取：

public static T Field<T>(this SqlDataReader reader, DataTable schema, string table, string field)
{
    // Search for the ordinal that match the table and field name
    var row = schema.AsEnumerable().FirstOrDefault(r => r.Field<string>("BaseTableName") == table && r.Field<string>("ColumnName") == field);
    var ordinal = row.Field<int>("ColumnOrdinal");

    return (T)reader.GetValue(ordinal);
}

然后您可以调用该扩展方法

using (SqlConnection connection = new SqlConnection("your connection string"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("SELECT * FROM Users INNER JOIN BloodBankUser ON Users.UserID = BloodBankUser.UserID;", connection))
    using (var reader = command.ExecuteReader(CommandBehavior.KeyInfo))
    {
        var schema = reader.GetSchemaTable();

        while (reader.Read())
        {
            Console.WriteLine(reader.Field<string>(schema, "Users", "Address"));
        }
    }
}

While the first answer would be to change your SQL Query to specify a distinct name for each of your field, it is still possible to retrieve the table name associated with your field.

In this example, I am not filling a DataTable using a DataAdapter, but rather I am using the SqlDataReader.

Be aware that this may fail if you are unable to retrieve the database schema for any reason

When calling ExecuteReader on a SqlCommand, there is an overload that allows you to specify a CommandBehavior. In our case, the behavior that we want is CommandBehavior.KeyInfo.

var reader = command.ExecuteReader(CommandBehavior.KeyInfo);

Now, on the reader, you can invoke the GetSchemaTable method. It returns a DataTable that contains the structure of your query.

var schema = reader.GetSchemaTable();

You can read about that table on MSDN.

Our goal now is to match the field and table against its ordinal position in the column list. Three fields from the schema table are relevant to your interest:

ColumnName
BaseTableName
ColumnOrdinal

You can then create an extension method to do that reading:

public static T Field<T>(this SqlDataReader reader, DataTable schema, string table, string field)
{
    // Search for the ordinal that match the table and field name
    var row = schema.AsEnumerable().FirstOrDefault(r => r.Field<string>("BaseTableName") == table && r.Field<string>("ColumnName") == field);
    var ordinal = row.Field<int>("ColumnOrdinal");

    return (T)reader.GetValue(ordinal);
}

You can then call that extension method

using (SqlConnection connection = new SqlConnection("your connection string"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("SELECT * FROM Users INNER JOIN BloodBankUser ON Users.UserID = BloodBankUser.UserID;", connection))
    using (var reader = command.ExecuteReader(CommandBehavior.KeyInfo))
    {
        var schema = reader.GetSchemaTable();

        while (reader.Read())
        {
            Console.WriteLine(reader.Field<string>(schema, "Users", "Address"));
        }
    }
}

回复收藏 0 原文

恰似旧人归 2024-10-09 17:22:05

重命名输出中的字段（选择FIELDNAME为NEWNAME）

回复收藏 0 原文

沉睡月亮 2024-10-09 17:22:05

您指定列名而不是使用 SELECT * FROM 然后您将能够执行以下操作

Select User.Username,
       User.Address as 'UserAddress',
       BloodBankUser.Address as 'BloodbankAddress'

FROM Users 
INNER JOIN BloodBankUser ON Users.UserID = BloodBankUser.UserID

You specify the column names rather than use SELECT * FROM You will then be able to do the following

Select User.Username,
       User.Address as 'UserAddress',
       BloodBankUser.Address as 'BloodbankAddress'

FROM Users 
INNER JOIN BloodBankUser ON Users.UserID = BloodBankUser.UserID

回复收藏 0 原文