Reading ambiguous name columns from a SQLDataReader

[ikantspelwurdz]

Suppose a database with two tables, "Main" and "Users."

Main:

ID	User1ID	User2ID
1	1	2

Users:

UserID	Name
1	Alice
2	Bob

I've got a SQL statement that looks something like this:
select User1.Name, User2.Name from main
inner join Users as User1 on main.User1ID = User1.UserID
inner join Users as User2 on main.User2ID = User2.UserID

The SQL itself works fine. But I want to read this data using a SQLDataReader, and I want to reference by column name, not column index.

This code:

Dim reader As SqlDataReader = ExecuteReader(sql)
reader.Read()
Dim alice as String = reader.Item("User1.Name")
Dim bob as String = reader.Item("User2.Name")

...does not work. It crashes with IndexOutOfRangeException. These columns do not exist. A look at the metadata shows both columns are called "Name."

This code:

Dim reader As SqlDataReader = ExecuteReader(sql)
reader.Read()
Dim alice as String = reader.Item("Name")
Dim bob as String = reader.Item("Name")

...runs, but both strings are going to be "Alice."

How can I best solve this problem? Going by column index isn't a good solution; the indices could (and almost certainly will) change. Using multiple SQLDataReaders would work, but I'd prefer a more elegant solution if there is one.

 

[Herman]

Use aliases in your SQL query:

select User1.Name AS User1Name, User2.Name AS User2Name from main

 

[ikantspelwurdz]

That won't work in the long run, unfortunately. The real "Users" table has 160 fields in it, and they can change. I don't mind updating the VB code when the table fields change, but also having to maintain a SQL query with 320 aliased columns is too much work.

I did find some other threads on this subject on Google, and the best suggestion I've seen is to build the SQL dynamically. That would still take two trips to the DB, one to get the schema (so you can build the dynamic SQL query), and another to execute the dynamic query.