Re: SqlDataReader.GetSchemaTable returns too many columns by John
John
Fri Oct 06 21:59:42 CDT 2006
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" <sqlranger.mpv@mpvs.org> wrote in message
news:Ooq6V1W6GHA.3620@TK2MSFTNGP04.phx.gbl...
> 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" <revelation@nospam.nospam> escribió en el mensaje
> news:%23z%23gypI6GHA.4644@TK2MSFTNGP04.phx.gbl...
>> 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" <rmsimpson@nospam.nospam> wrote in message
>> news:OlZAUmE6GHA.4232@TK2MSFTNGP02.phx.gbl...
>>> 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" <revelation@nospam.nospam> wrote in message
>>> news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>