How to read Result of SELECT * from Joined Tables With Duplicate Column Names in .NET

Question

I am a PHP/MYSQL developer, slowly venturing into the realm of C#/MSSQL and I am having a problem in C# when it comes to reading an MSSQL query that joins two tables.

Given the two tables:

TableA:

int:id
VARCHAR(50):name
int:b_id

TableB:

int:id
VARCHAR(50):name

And given the query

SELECT * FROM TableA,TableB WHERE TableA.b_id = TableB.id;

Now in C# I normally read query data in the following fashion:

SqlDataReader data_reader= sql_command.ExecuteReader();
data_reader["Field"];

Except in this case I need to differentiate from TableA's name column, and TableB's name column.

In PHP I would simply ask for the field "TableA.name" or "TableB.name" accordingly but when I try something like

data_reader["TableB.name"];

in c#, my code errors out. Now I know this can probably be solved with a Google search or a search on this site, but I am having the hardest time coming up with the appropriate search term to find an answer.

So could someone please point me in the write direction, or give me a suggestion as to how to read a query on multiple tables in C#?

Thanks.

Answer 1

The result set only sees the returned data/column names, not the underlying table. Change your query to something like

SELECT TableA.Name as Name_TA, TableB.Name as Name_TB from ...

Then you can refer to the fields like this:

data_reader["Name_TA"];

Answer 2

You could try reading the values by index (a number) rather than by key.

name = data_reader[4];

You will have to experiment to see how the numbers correspond.

Answer 3

Welcome to the real world. In the real world, we don't use "SELECT *". Specify which columns you want, from which tables, and with which alias, if required.

Answer 4

I got an answer from one of my friends as well and I am posting it here because it is the approach that I will take, however the above answer's do best answer my question.

"Ideally, you should never have duplicate column names, across a database schema. So if you can rename you schema to not have conflicting names. That rule is for this very situation, once you've done your join, it is just a new recordset, and generally the table names do go with it."