none
Can I determine the primary key and index columns of a table using SqlConnection.GetSchema? RRS feed

  • Question

  • My app can open connections using OdbcConnection, OleDbConnection and SqlConnection. For OdbcConnection and OleDbConnection I have figured out how to determine what the indexes are, including the primary key for a given table by using Connection.GetSchema("Indexes",SchemaRestrictionsIndexes). There are differences in the way to determine what the primary key is between providers but I have been able to deal with that.

    The schemas that SQLConnection return are quite different when it comes to Indexes from their OleDb/Odbc counterparts.

    Is there are way to use GetSchema to determine which columns of a table are indexes and which column(s) make up the primary key?

    Thanks.



    Thursday, July 28, 2011 9:45 PM

Answers

  • Hello,

    Thank you for posting.

    I suggest you can check the following code snippets. Hope this helps.

    using (SqlConnection conn = new SqlConnection(strSqlConn))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM tCity", conn))
        {
           SqlDataReader myDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
           DataTable dt = myDataReader.GetSchemaTable();
        }
    }

    Please check here for more information. http://support.microsoft.com/kb/310107

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Ou8 Monday, August 1, 2011 6:09 PM
    Monday, August 1, 2011 2:45 PM

All replies

  • Hello,

    Thank you for posting.

    I suggest you can check the following code snippets. Hope this helps.

    using (SqlConnection conn = new SqlConnection(strSqlConn))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM tCity", conn))
        {
           SqlDataReader myDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
           DataTable dt = myDataReader.GetSchemaTable();
        }
    }

    Please check here for more information. http://support.microsoft.com/kb/310107

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Ou8 Monday, August 1, 2011 6:09 PM
    Monday, August 1, 2011 2:45 PM
  • Thanks. That's what I needed. I have to wonder why you can't do it directly with GetSchema on the connection object though.
    Monday, August 1, 2011 6:20 PM