CODEWITHSUNDEEP
.netdatabasevb.netms-accessoledb
Admin
Admin

Reputation: 193

conn.GetSchema find primary key of table - MsAccess

I have a MsAccess database (.mdb) located in D drive as (D:\project.mdb). Database have more than 120 Tables. There is a table Records which have primary key and multiple fields. I want to get the Columns,ColumnType and PrimaryKey.

I am getting fields and its type using :

Dim TableNm_ As String = "Records"      
Dim restrictions2() As String = {Nothing, Nothing, TableNm_, Nothing} 
Dim DataTable2 As System.Data.DataTable = conn.GetSchema("Columns", restrictions2)

But it did not have PrimayKey Column.

I have gone through several SO posts and others like GetSchema and PrimaryKey column. But i do not want to create a command and Reader to read the key.

Is there any way to get PrimayKey Column of table Records only, using conn.GetSchema ?

Upvotes: 0

Views: 1096

Answers (2)

Mino
Mino

Reputation: 335

You can use DatabaseSchema from Kros.Utils.MsAccess

using(var cn = new OleDbConnection("MS Access Connection String"))
{  
  DatabaseSchema schema = DatabaseSchemaLoader.Default.LoadSchema(cn);
  Assert.IsFalse(schema.Tables["Person"].Columns["Id"].AllowNull);
}

Contains infromation about Tables, Columns, Indexes, ...

Upvotes: 0

Er Mayank
Er Mayank

Reputation: 1073

You can use Connection.GetOleDbSchemaTable and pass PrimaryKeys SchemaGuid. Apply restrictions on TableName as :

DataTable2 = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New String() {Nothing, Nothing, TableNm_}) 
For Each TableRow As DataRow In DataTable2.Rows
    If TableRow.Item("PK_NAME").ToString.ToLower = "PrimaryKey".ToLower Then
        Dim  PrimaryKey = TableRow.Item("COLUMN_NAME")
        Dim Ordinal = CShort(TableRow.Item("ORDINAL"))
    End If
Next

Upvotes: 2

Related Questions