DataGridView: Determine SQL Key Values or Relationships?

Question

Is there a way to tell when a DataGridView columns is a PRIMARY KEY or a FOREIGN KEY?

The reason I ask is because I'd like to set these DataGridViewDataColumns to be ReadOnly.

After loading a DataTable, I can see SQL-like properties like AllowDBNull, AutoIncrement, and Unique; but how do I tell which columns are keys?

private void GetData(string tableName, SqlConnection con) {
  bool wasOpen = (con.State == ConnectionState.Open);
  DataTable table = new DataTable(tableName);
  string sqlText = string.Format(SQL_SELECT_COMMAND, tableName);
  SqlCommand cmd = new SqlCommand(sqlText, con);
  if (!wasOpen) {
    con.Open();
  }
  table.Load(cmd.ExecuteReader());
  if (!wasOpen) {
    con.Close();
  }
  dataGridView1.DataSource = table.DefaultView;
  for (int i = 0; i < table.Columns.Count; i++) {
    DataColumn tblC = table.Columns[i];
    DataGridViewColumn dgvC = dataGridView1.Columns[i];
    dgvC.ReadOnly = (!tblC.AllowDBNull && tblC.AutoIncrement && tblC.Unique);
  }
}

The test in that last line above (!tblC.AllowDBNull && tblC.AutoIncrement && tblC.Unique) is a hack, and I know it only works in some cases.

I found the post How to determine a primary key for a table in SQL Server? showing how to write an SQL Query to determine this, but can I tell somehow using the DataTable that I supply to the DataGridView?

I also saw the post C# Linq-to-SQL: Refectoring this Generic GetByID method using Linq-to-SQL, but (though I try) I just don't understand that Linq jibberish.

If determining the key is not supported, what would be the best approach? Two queries, one for schema and the other for the data, then go through the data using the schema collected?

EDIT: I noticed that when looking at the data table's schema using:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1'

I am returned a COLUMN_FLAGS value. The table I am looking at shows a value of 18 for my Primary Key on this table. Is that a good test?

Answer 1

The DataTable has a property PrimaryKey which is a collection of DataColumns.

If you set the CommandBehavior parameter when you call ExecuteReader to KeyInfo then you can retrieve the primary key column information from the primary key property.

table.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo));

Using the ColumnName property of each column in the collection you can then set the Readonly property of your DataGridView columns.

foreach (DataColumn d in dt.PrimaryKey)
{
    if (dataGridView1.Columns[d.ColumnName] != null)
    {
        dataGridView1.Columns[d.ColumnName].ReadOnly = true;
    }
}

---

There are also other useful values for CommandBehaviour, such as SchemaOnly, that you can combine as shown:

table.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehaviour.SchemaOnly));