none
I am looking for the .Net equivalent of SQLPrimaryKeys in ODBC.

    Question

  •  

    I am trying to use an OdbcConnection to get a list of primary keys from a generic ODBC database.

     

    In C++ I used the SQLPrimaryKeys function.  I did not see this option with the GetSchema methods of OdbcConnection.

     

    Any suggestions?

     

    Thanks in advance!

    Thursday, January 10, 2008 7:13 PM

Answers

  • I finally had to create my own class based on code from others.

    The class is OdbcPKConnection.

    If anyone is interested in the source please let me know. 

     

    Here is sample code:

    OdbcPKConnection myKeys = new OdbcPKConnection("DSN=DSNName;UID=uid;PWD=pwd");

    myKeys.Open();

    string[] restrictions = new string[4];

    restrictions[0] = "";

    restrictions[1] = "";

    restrictions[2] = "";

    restrictions[3] = "";

     

    DataTable dtPKeys = myKeys.GetSchema("PRIMARY_KEYS", restrictions);

    foreach (DataRow row in dtPKeys.Rows)

    {

    foreach (DataColumn col in dtPKeys.Columns)

    {

    string sVal = col.ColumnName + " - " + row[col];

    richTextBox1.AppendText(sVal);

    }

    }

    myKeys.Close();

     

     

    Sample Output:

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctCategories

    COLUMN_NAME - CategoryID

    KEY_SEQ - 1

    PK_NAME - AcctCategories

    TABLE_CAT -

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctCodeDesc

    COLUMN_NAME - SalesAcct

    KEY_SEQ - 1

    PK_NAME - AcctCodeDesc

    TABLE_CAT -

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctFootNotes

    COLUMN_NAME - NoteID

    KEY_SEQ - 1

    PK_NAME - AcctFootNotes

    TABLE_CAT -

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctGeneralCategories

    COLUMN_NAME - GeneralCategoryID

    KEY_SEQ - 1

    PK_NAME - AcctGeneralCategories

    Thursday, January 17, 2008 3:33 PM

All replies

  • GetSchema("IndexColumns" ... will give you a list of primary keys columns

     

    Bear in mind that if you call GetSchema from inside a transaction it will raise an expection.

     

    Unbelievable I know, but hilariously true

    Friday, January 11, 2008 4:40 PM
  • Apparently GetSchema("IndexColumns"  is not available with the OdbcConnection only with SqlClient.

     

    Thanks!

     

    Friday, January 11, 2008 6:14 PM
  • I finally had to create my own class based on code from others.

    The class is OdbcPKConnection.

    If anyone is interested in the source please let me know. 

     

    Here is sample code:

    OdbcPKConnection myKeys = new OdbcPKConnection("DSN=DSNName;UID=uid;PWD=pwd");

    myKeys.Open();

    string[] restrictions = new string[4];

    restrictions[0] = "";

    restrictions[1] = "";

    restrictions[2] = "";

    restrictions[3] = "";

     

    DataTable dtPKeys = myKeys.GetSchema("PRIMARY_KEYS", restrictions);

    foreach (DataRow row in dtPKeys.Rows)

    {

    foreach (DataColumn col in dtPKeys.Columns)

    {

    string sVal = col.ColumnName + " - " + row[col];

    richTextBox1.AppendText(sVal);

    }

    }

    myKeys.Close();

     

     

    Sample Output:

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctCategories

    COLUMN_NAME - CategoryID

    KEY_SEQ - 1

    PK_NAME - AcctCategories

    TABLE_CAT -

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctCodeDesc

    COLUMN_NAME - SalesAcct

    KEY_SEQ - 1

    PK_NAME - AcctCodeDesc

    TABLE_CAT -

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctFootNotes

    COLUMN_NAME - NoteID

    KEY_SEQ - 1

    PK_NAME - AcctFootNotes

    TABLE_CAT -

    TABLE_SCHEM - DBA

    TABLE_NAME - AcctGeneralCategories

    COLUMN_NAME - GeneralCategoryID

    KEY_SEQ - 1

    PK_NAME - AcctGeneralCategories

    Thursday, January 17, 2008 3:33 PM