Reputation: 15571
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.
Upvotes: 1
Views: 2291
Reputation: 15571
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;
}
Upvotes: 3