.NET DataReader and SQL joins

Question

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

Answer 1

Given that you know the field names ahead of time, why not express them in the select statement?

string query = "SELECT table2.field as field2, ... FROM table1, table2 " + 
               "WHERE table1.Id = table2.fId";

Then you can just refer to dataReader["field2"].

Answer 2

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

Answer 3

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.

Answer 4

Using * instead of a fieldlist is the number 1 sql anti-pattern as determined by stack overflow voters.

http://stackoverflow.com/questions/346659/what-are-the-most-common-sql-anti-patterns/346679#346679

Answer 5

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