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
精彩评论