SqlDataReader.GetSchemaTable returns too many columns

  • Thread starter Thread starter John
  • Start date Start date
J

John

I've run into a problem I can't seem to explain or work around. I'm calling
SqlDataReader.GetSchemaTable() on a database view (SQL Server 2005). It is
returning rows not only for the columns in the view, but also for another
column that is not ouput in the view. For example, I execute the following
code on the Northwind database view named [Alphabetical list of products]:

command.CommandText = "SELECT * FROM [Alphabetical list of products]";
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = reader.GetSchemaTable();

This view selects all 10 columns from Products and also CategoryName from
Categories (11 columns total). The tables are joined on CategoryID. When I
run the above code, it returns a schema table that contains 12 rows--all 10
from Products, Categories.CategoryName and also Categories.CategoryID. It
is this last one that is causing problems for me. CategoryID is appearing
twice in my list (once for each table) even though only one of them is in
the SELECT.

So my questions are:

1. Why is Categories.CategoryID getting into the schema table even though it
is not selected?
2. Is there any way to prevent it from getting in there and having the
schema table only show the selected columns?

For those who do not have the Northwind database handy, here's the view's
sql:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.SupplierID, dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,
dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)

Thanks,
John
 
When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with the
necessary additional columns needed to uniquely identify the rows returned.

For example, if you had this table:

CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50))

If you called ExecuteReader/GetSchemaTable on this query:

SELECT MyValue FROM FOO

Without KeyInfo, you'd only get MyValue when you called GetSchemaTable().
However, if you specify KeyInfo, you'll get ID as well because ID is the key
for the given select clause and you specifically asked for key info.

Robert
 
Thank you, Robert. That makes sense. My problem is (was) that I need the
key info that is provided in the table, but only for the selected columns.
Through the magic of the IntelliSense Visualizers I just found the IsHidden
column in the table. This appears to tell me whether or not the column was
included in the select. That is what I needed. I can now ignore the hidden
columns, which is what I need to do. Funny, IsHidden is not in my VS
documentation, but it is on the MSDN site.

Thanks for your reply, as it got me thinking and looking in the right
direction.

John

Robert Simpson said:
When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with
the necessary additional columns needed to uniquely identify the rows
returned.

For example, if you had this table:

CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50))

If you called ExecuteReader/GetSchemaTable on this query:

SELECT MyValue FROM FOO

Without KeyInfo, you'd only get MyValue when you called GetSchemaTable().
However, if you specify KeyInfo, you'll get ID as well because ID is the
key for the given select clause and you specifically asked for key info.

Robert

John said:
I've run into a problem I can't seem to explain or work around. I'm
calling SqlDataReader.GetSchemaTable() on a database view (SQL Server
2005). It is returning rows not only for the columns in the view, but
also for another column that is not ouput in the view. For example, I
execute the following code on the Northwind database view named
[Alphabetical list of products]:

command.CommandText = "SELECT * FROM [Alphabetical list of products]";
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = reader.GetSchemaTable();

This view selects all 10 columns from Products and also CategoryName from
Categories (11 columns total). The tables are joined on CategoryID.
When I run the above code, it returns a schema table that contains 12
rows--all 10 from Products, Categories.CategoryName and also
Categories.CategoryID. It is this last one that is causing problems for
me. CategoryID is appearing twice in my list (once for each table) even
though only one of them is in the SELECT.

So my questions are:

1. Why is Categories.CategoryID getting into the schema table even though
it is not selected?
2. Is there any way to prevent it from getting in there and having the
schema table only show the selected columns?

For those who do not have the Northwind database handy, here's the view's
sql:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.SupplierID, dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,
dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)

Thanks,
John
Click to expand...
Click to expand...
 
I guess I could've mentioned the IsHidden property and saved you some
additional time while I was at it ... oh well! :P

Robert

John said:
Thank you, Robert. That makes sense. My problem is (was) that I need the
key info that is provided in the table, but only for the selected columns.
Through the magic of the IntelliSense Visualizers I just found the
IsHidden column in the table. This appears to tell me whether or not the
column was included in the select. That is what I needed. I can now
ignore the hidden columns, which is what I need to do. Funny, IsHidden is
not in my VS documentation, but it is on the MSDN site.

Thanks for your reply, as it got me thinking and looking in the right
direction.

John

Robert Simpson said:
When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with
the necessary additional columns needed to uniquely identify the rows
returned.

For example, if you had this table:

CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50))

If you called ExecuteReader/GetSchemaTable on this query:

SELECT MyValue FROM FOO

Without KeyInfo, you'd only get MyValue when you called GetSchemaTable().
However, if you specify KeyInfo, you'll get ID as well because ID is the
key for the given select clause and you specifically asked for key info.

Robert

John said:
I've run into a problem I can't seem to explain or work around. I'm
calling SqlDataReader.GetSchemaTable() on a database view (SQL Server
2005). It is returning rows not only for the columns in the view, but
also for another column that is not ouput in the view. For example, I
execute the following code on the Northwind database view named
[Alphabetical list of products]:

command.CommandText = "SELECT * FROM [Alphabetical list of products]";
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = reader.GetSchemaTable();

This view selects all 10 columns from Products and also CategoryName
from Categories (11 columns total). The tables are joined on
CategoryID. When I run the above code, it returns a schema table that
contains 12 rows--all 10 from Products, Categories.CategoryName and also
Categories.CategoryID. It is this last one that is causing problems for
me. CategoryID is appearing twice in my list (once for each table) even
though only one of them is in the SELECT.

So my questions are:

1. Why is Categories.CategoryID getting into the schema table even
though it is not selected?
2. Is there any way to prevent it from getting in there and having the
schema table only show the selected columns?

For those who do not have the Northwind database handy, here's the
view's sql:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.SupplierID, dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,
dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)

Thanks,
John
Click to expand...
Click to expand...
Click to expand...
 
It's always better when I find things out on my own... It makes them stick
better in this weary old brain! :)

Robert Simpson said:
I guess I could've mentioned the IsHidden property and saved you some
additional time while I was at it ... oh well! :P

Robert

John said:
Thank you, Robert. That makes sense. My problem is (was) that I need
the key info that is provided in the table, but only for the selected
columns. Through the magic of the IntelliSense Visualizers I just found
the IsHidden column in the table. This appears to tell me whether or not
the column was included in the select. That is what I needed. I can now
ignore the hidden columns, which is what I need to do. Funny, IsHidden
is not in my VS documentation, but it is on the MSDN site.

Thanks for your reply, as it got me thinking and looking in the right
direction.

John

Robert Simpson said:
When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with
the necessary additional columns needed to uniquely identify the rows
returned.

For example, if you had this table:

CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50))

If you called ExecuteReader/GetSchemaTable on this query:

SELECT MyValue FROM FOO

Without KeyInfo, you'd only get MyValue when you called
GetSchemaTable(). However, if you specify KeyInfo, you'll get ID as well
because ID is the key for the given select clause and you specifically
asked for key info.

Robert

I've run into a problem I can't seem to explain or work around. I'm
calling SqlDataReader.GetSchemaTable() on a database view (SQL Server
2005). It is returning rows not only for the columns in the view, but
also for another column that is not ouput in the view. For example, I
execute the following code on the Northwind database view named
[Alphabetical list of products]:

command.CommandText = "SELECT * FROM [Alphabetical list of products]";
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = reader.GetSchemaTable();

This view selects all 10 columns from Products and also CategoryName
from Categories (11 columns total). The tables are joined on
CategoryID. When I run the above code, it returns a schema table that
contains 12 rows--all 10 from Products, Categories.CategoryName and
also Categories.CategoryID. It is this last one that is causing
problems for me. CategoryID is appearing twice in my list (once for
each table) even though only one of them is in the SELECT.

So my questions are:

1. Why is Categories.CategoryID getting into the schema table even
though it is not selected?
2. Is there any way to prevent it from getting in there and having the
schema table only show the selected columns?

For those who do not have the Northwind database handy, here's the
view's sql:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.SupplierID, dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,
dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)

Thanks,
John
Click to expand...
Click to expand...
Click to expand...
 
If you modify the view in the following form:

ALTER VIEW TheView
WITH VIEW_METADATA
AS
SELECT ....

Then GetSchemaTable will return only the fields in the select list. No
hidden fields will be returned.

Regards:

Jesús López




John said:
Thank you, Robert. That makes sense. My problem is (was) that I need the
key info that is provided in the table, but only for the selected columns.
Through the magic of the IntelliSense Visualizers I just found the
IsHidden column in the table. This appears to tell me whether or not the
column was included in the select. That is what I needed. I can now
ignore the hidden columns, which is what I need to do. Funny, IsHidden is
not in my VS documentation, but it is on the MSDN site.

Thanks for your reply, as it got me thinking and looking in the right
direction.

John

Robert Simpson said:
When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with
the necessary additional columns needed to uniquely identify the rows
returned.

For example, if you had this table:

CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50))

If you called ExecuteReader/GetSchemaTable on this query:

SELECT MyValue FROM FOO

Without KeyInfo, you'd only get MyValue when you called GetSchemaTable().
However, if you specify KeyInfo, you'll get ID as well because ID is the
key for the given select clause and you specifically asked for key info.

Robert

John said:
I've run into a problem I can't seem to explain or work around. I'm
calling SqlDataReader.GetSchemaTable() on a database view (SQL Server
2005). It is returning rows not only for the columns in the view, but
also for another column that is not ouput in the view. For example, I
execute the following code on the Northwind database view named
[Alphabetical list of products]:

command.CommandText = "SELECT * FROM [Alphabetical list of products]";
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = reader.GetSchemaTable();

This view selects all 10 columns from Products and also CategoryName
from Categories (11 columns total). The tables are joined on
CategoryID. When I run the above code, it returns a schema table that
contains 12 rows--all 10 from Products, Categories.CategoryName and also
Categories.CategoryID. It is this last one that is causing problems for
me. CategoryID is appearing twice in my list (once for each table) even
though only one of them is in the SELECT.

So my questions are:

1. Why is Categories.CategoryID getting into the schema table even
though it is not selected?
2. Is there any way to prevent it from getting in there and having the
schema table only show the selected columns?

For those who do not have the Northwind database handy, here's the
view's sql:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.SupplierID, dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,
dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)

Thanks,
John
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Jesús. That's good to know. I'll keep that for future reference.
In my case now, however, modifying the views is not an option.
John

Jesús López said:
If you modify the view in the following form:

ALTER VIEW TheView
WITH VIEW_METADATA
AS
SELECT ....

Then GetSchemaTable will return only the fields in the select list. No
hidden fields will be returned.

Regards:

Jesús López




John said:
Thank you, Robert. That makes sense. My problem is (was) that I need
the key info that is provided in the table, but only for the selected
columns. Through the magic of the IntelliSense Visualizers I just found
the IsHidden column in the table. This appears to tell me whether or not
the column was included in the select. That is what I needed. I can now
ignore the hidden columns, which is what I need to do. Funny, IsHidden
is not in my VS documentation, but it is on the MSDN site.

Thanks for your reply, as it got me thinking and looking in the right
direction.

John

Robert Simpson said:
When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with
the necessary additional columns needed to uniquely identify the rows
returned.

For example, if you had this table:

CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50))

If you called ExecuteReader/GetSchemaTable on this query:

SELECT MyValue FROM FOO

Without KeyInfo, you'd only get MyValue when you called
GetSchemaTable(). However, if you specify KeyInfo, you'll get ID as well
because ID is the key for the given select clause and you specifically
asked for key info.

Robert

I've run into a problem I can't seem to explain or work around. I'm
calling SqlDataReader.GetSchemaTable() on a database view (SQL Server
2005). It is returning rows not only for the columns in the view, but
also for another column that is not ouput in the view. For example, I
execute the following code on the Northwind database view named
[Alphabetical list of products]:

command.CommandText = "SELECT * FROM [Alphabetical list of products]";
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = reader.GetSchemaTable();

This view selects all 10 columns from Products and also CategoryName
from Categories (11 columns total). The tables are joined on
CategoryID. When I run the above code, it returns a schema table that
contains 12 rows--all 10 from Products, Categories.CategoryName and
also Categories.CategoryID. It is this last one that is causing
problems for me. CategoryID is appearing twice in my list (once for
each table) even though only one of them is in the SELECT.

So my questions are:

1. Why is Categories.CategoryID getting into the schema table even
though it is not selected?
2. Is there any way to prevent it from getting in there and having the
schema table only show the selected columns?

For those who do not have the Northwind database handy, here's the
view's sql:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.SupplierID, dbo.Products.CategoryID,
dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,
dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)

Thanks,
John
Click to expand...
Click to expand...
Click to expand...
 
Back
Top