Datatable without knowing its primary key

Question

I am trying to query data to set the primary key [identity column] by querying each property of the auto-increment columns. However its always false (for a column that is Idenity / PK).

Querying the primary collection of tables shows that the data type does not consider that it has a PC.

  Dim dc As DataColumn() = dt.PrimaryKey
  Debug.WriteLine(dc.Count)  'Result is 0

Datatable populated .......

Using cn As SqlConnection = MyApp.GetConnection
  Using cmd As New SqlCommand(strSQL, cn)
    Using da As New SqlDataAdapter(cmd)
      Dim ds As New DataSet
      Try
        da.Fill(ds)

        Return ds

      Catch ex As Exception
        MyAppClass.LogWarning(ex, EventLogEntryType.Error)
        Throw
      End Try
    End Using 
  End Using 
End Using

The primary key of the table is: ([myTableId] [int] IDENTIFICATION (1,1) NOT NULL). and its pk: CONSTRAINT [PK_myTablesPK] PRIMARY KEY CLUSTERED ([myTableId] ASC)

Here someone has the same problem (maybe it is clearer than I wrote): http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/c6abdeef-0cb0-42f5-a5f1-10dc4d81df4a /

I suppose that something simple is missing me, does anyone want to enlighten me?

Answer 1

fillschema ;

da.FillSchema(ds, SchemaType.Mapped, table.tableName)
da.Fill(ds, table.tableName)

DataAdapter . Fill , DataSet. , DataSet, DataSet DataAdapater:

• FillSchema DataAdapter.
• AddWithKey MissingSchemaAction DataAdapter.

, , DataSet DataAdapter.

REF: http://support.microsoft.com/kb/310128

Answer 2

? ?

Private Sub GetPrimaryKeys ( myTable As DataTable )
   ' create the array for the columns.
   Dim colKeys ( ) As DataColumn = myTable.PrimaryKey
   ' get the number of elements in the array.
   Response.Write ( "Column Count: " & colKeys.Length.ToString ( ) )
   Dim i As Integer
   For i = 0 To colKeys.GetUpperBound ( 0 )
      Response.Write ( colKeys ( i ).ColumnName & _
         colKeys ( i ).DataType.ToString ( ) )
   Next i
End Sub

Answer 3

There is a library there, Kailua - Forgotten Methods in the ADO.NET API. which provides this and additional metadata for the top 5 providers. This information is vendor specific.