How to access Ambiguous Column in SQL Query with SELECT *

When I execute Stored Procedure

SELECT * FROM Users 
INNER JOIN BloodBankUse开发者_如何学Gor 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

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"));
        }
    }
}

Rename the FIELD in the output (Select FIELDNAME as NEWNAME)

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 

Avoid the use of * in SELECT queries. Select only the columns you need and name them explicitly to avoid ambiguity,

Insead of SELECT *... specify the columns you want explicitly, and alias those that may duplicate

SELECT Users.Address as UsersAddress