Obtaining a MySql tables PrimaryKey in C# using OdbcConntion (System.Data.Odbc)

Question

I am trying to retrieve the Primary Key of a table in a MySQL database using C-Sharp (C#) and running into problem.

I looked at the various MetaData collections offered and the corresponding columns, however none of them offer a primary key. The "Tables" and "Indexes" collection seem the most promising. INTERESTINGLY, OdbcConnection.GetSchema() has a PrimaryKey property/method, however there is no case where the PrimaryKey property yields something other than a null.

Indexes and Tables really did seem like the obvious choice. Yes, the tables in the database have a primary key and the database works.

Here is some code, although for this question none seem really necessary. I chose "Tables" for the purpose of this sample, but one can simply change to "Indexes" (or anything else). Obviously, COLUMN_NAME exists for Tables. I just have that there for whatever, playing.

public String GetPrimaryKey(String strTable)
{  
try  
{  
    String strPrimaryKey = null;  
    String[] strRestricted = new String[4] { null, null, strTable, null };  
    DataTable oSchema = null;  


    // Make sure that there is a connection.
    if (ConnectionState.Open != this.m_oConnection.State)
        this.m_oConnection.Open();

    // DATABASE: Get the schema
    oSchema = this.m_oConnection.GetSchema("Tables", strRestricted);

    // Extract the information related to the primary column, in the format "{System.Data.DataColumn[0]}"
    DataColumn[] oPrimaryKeys = oSchema.PrimaryKey;

    // Extract: Column Names
    foreach (DataRow oRow in oSchema.Rows)
    {
        // Get the column name.
        String strColumnName = oRow["COLUMN_NAME"].ToString();
    }

    return strPrimaryKey;
}

catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

return null;
}

In doing my research, I found it interesting that I could not find any posts from anyone using the GetSchema().PrimaryKey property.

So how can I identify the primary key?

Thanks in advance.

Answer 1

Your comment was the magic key. I did not know that the old interface was deprecated. Finding the right code was a bit of a challenge, as there is sadly no "COLUMN_NAME" on the Indexes collection or "PRIMRY" on the Columns collections, so I have to go through twice, but still, the new version is far better.

public String GetPrimaryKey(String strTable)
{
    try
    {
        Boolean bIsPrimary = false;
        String strIndexName = null;
        String strColumnName = null;
        String[] strRestricted = new String[4] { null, null, strTable, null      };
        DataTable oSchemaIndexes = null;
        DataTable oSchemaIndexColumns = null;


        // Make sure that there is a connection.
        if (ConnectionState.Open != this.m_oConnection.State)
            this.m_oConnection.Open();

        // DATABASE: Get the schemas needed.
        oSchemaIndexes = this.m_oConnection.GetSchema("Indexes", strRestricted);
        oSchemaIndexColumns = this.m_oConnection.GetSchema("IndexColumns", strRestricted);

        // Get the index name for the primary key.
        foreach (DataRow oRow in oSchemaIndexes.Rows)
        {
            // If we have a primary key, then we found what we want.
            strIndexName = oRow["INDEX_NAME"].ToString();
            bIsPrimary = (Boolean)oRow["PRIMARY"];
            if (true == bIsPrimary)
                break;
        }

        // If no primary index, bail.
        if (false == bIsPrimary)
            return null;

        // Get the corresponding column name.
        foreach (DataRow oRow in oSchemaIndexColumns.Rows)
        {
            // Get the column name.
            if (strIndexName == (String)oRow["INDEX_NAME"])
            {
                strColumnName = (String)oRow["COLUMN_NAME"];
                break;
            }
        }

        return strColumnName;
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return null;
}