.NET DataReader 和 SQL 连接
string query =
"SELECT * FROM table1, table2 WHERE table1.Id = table2.fId";
...
using(IDataReader dataReader =
db.ExecuteReader(CommandType.Text, query))
..
string value = dataReader["table2.field"]; //dies
我目前正在编写一些 .NET 代码，其中涉及执行联接查询，然后使用 DataReader 访问返回的数据。 我想知道是否可以使用某种前缀表示法（参见上面的示例）访问返回行中的字段，而不必使用序数位置来访问行中的值（在两个表都包含重叠字段的情况下）名字）？
string query =
"SELECT * FROM table1, table2 WHERE table1.Id = table2.fId";
...
using(IDataReader dataReader =
db.ExecuteReader(CommandType.Text, query))
..
string value = dataReader["table2.field"]; //dies
I'm currently writing some .NET code which involves executing a join query and then accessing the returned data using a DataReader. I am wondering if it is possible to access fields from the returned rows using some sort of prefix notation (see example above) instead of having to use the ordinal position to access a value in the row (in the case where both tables contain overlapping field names)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您不应该向生产服务器发送 select * 查询，因为它会浪费资源。 当您进行连接时，至少有一个字段会重复，并且完全不需要同时发送这两个字段。 如果您实际上并不需要每一列，则不应返回所有列。 此外，当数据库结构发生变化时，您将使用 select * 来破坏代码。 这确实是一个坏主意。
您还应该真正学习使用 ANSII 92 连接语法。 您使用的语法仅过时了 18 年，并且可能会导致很多问题，因为当人们忘记 where 子句中的连接条件之一时，它经常会导致交叉连接（使用 ANSII 92 连接语法，这不会通过语法检查）。 当在 SQL Server 中使用旧式左联接语法 (*=) 时，它也并不总是给出正确的答案，因为它有时但并不总是将其解释为交叉联接（并且该样式左联接已被弃用，并且将不允许使用）在下一个版本中）。 我不知道你正在使用什么数据库，但如果你有 SQL Server 后端，你需要注意这一点。
You should not ever send a select * query to a production server as it wastes resources. When you have a join, at least one field is repeated and that is completely unnecessary to send send both. And if you don't actually need every column, you shouldn't return all of them. Additionally, you are going to break code using select * as database structures change. This is truly a bad idea.
You also should really learn to use ANSII 92 join syntax. The syntax you use is only 18 years out of date and can cause lots of isses as it all too frequently results in a cross join when people forget one of the join conditions in the where clause (using ANSII 92 join syntax this wouldn't pass the syntax check). It also does not always give correct answers when using the old style left join syntax (*=) in SQL Server as it sometimes but not consistently will interpret that as a cross join (and that style left join has been deprecated and will not be allowed in the next version). I don't know what database you are using, but if you have a SQL Server backend, you need to be aware of that.
使用 * 代替字段列表是由堆栈溢出投票者确定的第 1 sql 反模式。
最常见的 SQL 反模式是什么？
Using * instead of a fieldlist is the number 1 sql anti-pattern as determined by stack overflow voters.
What are the most common SQL anti-patterns?
无论列的来源如何，选择的结果都是单个表。 您可以看到，当表有共同的列时，它往往会抱怨。 :) 如果上面列出的表具有唯一的列，则只需使用 [“field”]。
The result of a select is a single table regardless of the source of the columns. You can see that when the tables have a column in common it tends to complain. :) If the tables you have listed above have unique columns, you would simply have ["field"].
存在合法的情况，也许您拥有一个数据读取器或存储过程，但您不一定控制源 - 例如，在工具或实用程序库中。
虽然一般情况下我建议不要使用 *，而是使用别名
您可以通过使用 DataReader.GetSchemaTable 并查看 BaseTableName。
您还需要使用 ExecuteReader(System.Data.CommandBehavior.KeyInfo) 调用 ExecuteReader。
There are legitimate cases, perhaps you are handed a data reader or a stored proc and you aren't necessarily in control of the source - for example, in a tools or utility library.
Although in general I would recommend not using *, and using aliasing instead
You can get what you want by using DataReader.GetSchemaTable and looking at the BaseTableName.
You will also need to call ExecuteReader with ExecuteReader(System.Data.CommandBehavior.KeyInfo).
既然您提前知道字段名称，为什么不在 select 语句中表达它们呢？
然后你就可以参考
dataReader["field2"]。Given that you know the field names ahead of time, why not express them in the select statement?
Then you can just refer to
dataReader["field2"].