I'm going to have a couple of posts about working with data that's derived from joining tables in a database. The first issue is (I think) the more straightforward. This just happened to come up. I got curious about the behavior and investigated just a little.
The scenario is that you run a query something like this:SELECT
Products.ProductID,
Products.ProductName,
Products.CategoryID,
Categories.CategoryName
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
You are using an ADO.NET data reader to fetch each record. How do you get the value of, say, Categories.CategoryName? This doesn't work:String categoryName = sqlDataReader["Categories.CategoryName"];
As in, ADO.NET will throw an IndexOutOfRange exception. However, this does work:String categoryName = sqlDataReader["CategoryName"];
That is, you can refer to the column name without its table qualifier, even though the original query did include the table qualifiers.
Ok, now let's create a query that would require disambguation:SELECT
Products.ProductID,
Products.ProductName,
Products.CategoryID,
Categories.CategoryID,
Categories.CategoryName
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
(Never mind that this is a dumb query.) This time, you must do this:String categoryName = sqlDataReader["Categories.CategoryName"];
If you leave off Categories. when referencing the column, you'll get another IndexOutOfRange exception.
I'm not going to posit anything definitive about what's going on here. What appears to be the case is that if the column name is unambiguous, ADO.NET (or someone -- the provider?) drops the table qualification. Thus Categories.CategoryID becomes just CategoryID. However, if there is ambiguity, it keeps the table qualifier. Or let's just say that this model worked for me in these circumstances.
(Incidentally, a way around all of this is to just alias the column names in the original query and use the alias instead.)
I deduced this through trial and error (mostly error, of course) and at the suggestion of ADO.NET guru Dave Sceppa, poked into the results returned by dataReader.GetSchemaTable(). This exposed to me the exact column names that I could use. In case you're curious about what I did, here's the code:OleDbConnection OleDbConnection =
new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\SsampleWeb\App_Data\Northwind.mdb");
// Try various queries.
//OleDbCommand OleDbCommand = new OleDbCommand("SELECT Products.ProductID,
// Products.ProductName, Products.CategoryID, Categories.CategoryName
// FROM (Products INNER JOIN Categories ON Products.CategoryID =
// Categories.CategoryID)", OleDbConnection);
OleDbCommand OleDbCommand = new OleDbCommand("SELECT Products.ProductID,
Products.ProductName, Products.CategoryID, Categories.CategoryID,
Categories.CategoryName FROM (Products INNER JOIN Categories ON
Products.CategoryID = Categories.CategoryID)", OleDbConnection);
OleDbDataReader dataReader;
DataTable schemaTable;
OleDbConnection.Open();
dataReader = OleDbCommand.ExecuteReader();
String s;
dataReader.Read();
{
schemaTable = dataReader.GetSchemaTable();
foreach(DataRow row in schemaTable.Rows)
{
for(int i = 0; i < schemaTable.Columns.Count; i++)
{
s = schemaTable.Columns[i].ColumnName + "=" + row[i].ToString();
Response.Write(s + "<br/>");
}
}
}
// Try various ways to refer to the CategoryID column.
Response.Write("<br/>CategoryID = " + dataReader["CategoryID"]);
//Response.Write("<br/>CategoryID = " + dataReader["Categories.CategoryID"]);
//Response.Write("<br/>CategoryID = " + dataReader["Products.CategoryID"]);
OleDbConnection.Close();
|
