GetSchemaTable is returning too many columns for Views

  • Thread starter Thread starter Jason Kendall
  • Start date Start date
J

Jason Kendall

I've got a program that needs to work with the schema of tables
mostly. I recently decided to extend it to be able to work with views
as well. I discovered that the DataReader's GetSchemaTable() method
returns more columns than are actually defined for the view.

The problem appears to be the KeyInfo commandbehavior. When I pass
one of the other behaviors, I don't get the extra columns, all of
which are the primary keys for tables joined into the view for which I
chose not to return their keys. When I use any of the other
CommandBehavior values I don't get details about the schema such as
whether a column is a primary key.

My app heavily relies on the format of the Schema DataTable so what I
would like, in a perfect world, is either:
1) Help on how to use the existing code to get the details of the
schema for only the columns I explicitly defined in my views.
2) SQL that pulls directly from the system tables which returns
columns containing the same information as that found in the DataTable
returned by GetSchemaTable.

---- Code Snippet ----------------
lcCmd = New SqlCommand(SQLString, lcConn)
lcDR = lcCmd.ExecuteReader(CommandBehavior.KeyInfo)
Return lcDR.GetSchemaTable()
-------------------------------------------

Any help would be greatly appreciated. If there's an online resource
that goes into details on this, you can just point me to that.

The biggest problem I've had building this SQL myself is telling
whether a given column is part of the Primary Key. I saw how to use
the syscolumns.status to tell if the column is an Identity, but not
all of my keys are Identity columns.

Thanks!
 
Jeez... I looked and looked for the answer before making a post.
20 minutes after making the post, I think I ran across the solution.

The DataTable returned by GetSchemaTable has a column 'IsHidden'. I
don't know what other value it serves, but for Views this column will
be True for these PK columns that are not part of the normal view
results.

If someone has a better solution or wants to elaborate, feel free.
 
OK, here's how I solved this problem with minimal code in my
project...

The original code snippet is now elaborated as shown below. Basically,
I loop through the DataTable and remove any rows marked as hidden.

Dim lcRows As New ArrayList

lcCmd = New SqlCommand(SQLString, lcConn)
lcDR = lcCmd.ExecuteReader(CommandBehavior.KeyInfo)
lcDT = lcDR.GetSchemaTable()

For LCV As Integer = lcDT.Rows.Count - 1 To 0 Step -1
If CBool(lcDT.Rows(LCV)("IsHidden")) Then lcDT.Rows.RemoveAt(LCV)
Next

Return lcDT
 
Back
Top