Determining Primary Key columns via GetSchema

Question

Is there a way to determine whether a column is a primary key using the ADO.NET GetSchema method?

Here's what I've got so far:

public IEnumerable<DbColumnInfo> GetColumns(string providerName, string connectionString, string tableName)
{
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
    using (DbConnection connection = factory.CreateConnection())
    using (DbCommand command = factory.CreateCommand())
    {
        connection.ConnectionString = connectionString;
        connection.Open();
        command.Connection = connection;

        var columns = connection.GetSchema("Columns", tableName.Split('.'));
        foreach (DataRow row in columns.Rows)
        {
            yield return new DbColumnInfo()
            {
                Name = row.Field<string>(3),
                OrdinalPosition = row.Field<short>(4),
                DataType = this.FormatDataType(row),
                IsNullable = string.Equals(row.Field<string>(6), "yes", StringComparison.InvariantCultureIgnoreCase),
                IsPrimaryKey = // ... ?
            };
        }
    }
}

Solution 1

I'm afraid you can't determine with connection.GetSchema() ...

But as a workaround you can try the dataadapter, if it is suitable for you:

    var da = factory.CreateDataAdapter();
    command.CommandText = "select * from Employees";
    da.SelectCommand = command;
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

    var dtab = new DataTable();
    da.FillSchema(dtab, SchemaType.Source);

    foreach (DataColumn col in dtab.Columns)
    {
        string name = col.ColumnName;
        bool isNull = col.AllowDBNull;
        bool isPrimary = dtab.PrimaryKey.Contains(col);
    }

Solution 2

Since qes asked, what I'm currently using (after several versions refactoring the code away since Laszlo provided his working solution) was not as elegant as I had hoped for, but it was more efficient and satisfied my needs. Basically I provide a DbInfoProvider class which is designed to produce DbTableInfo and DbColumnInfo for a given provider. The SqlDbInfoProvider uses this to get column information for SQL Server only:

public IEnumerable<DbColumnInfo> GetColumns(string connectionString, DbTableInfo table)
{
    DbProviderFactory factory = DbProviderFactories.GetFactory(this.providerName);
    using (DbConnection connection = factory.CreateConnection())
    using (DbCommand command = factory.CreateCommand())
    {
        connection.ConnectionString = connectionString;
        connection.Open();
        command.Connection = connection;
        command.CommandText = ColumnInfoQuery;
        command.CommandType = CommandType.Text;
        var tableSchema = factory.CreateParameter();
        tableSchema.ParameterName = "@tableSchema";
        tableSchema.DbType = DbType.String;
        tableSchema.Value = table.Schema;
        command.Parameters.Add(tableSchema);
        var tableName = factory.CreateParameter();
        tableName.ParameterName = "@tableName";
        tableName.DbType = DbType.String;
        tableName.Value = table.Name;
        command.Parameters.Add(tableName);

        var dataTable = new DataTable();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return new DbColumnInfo()
                {
                    Name = reader.GetString(0),
                    OrdinalPosition = reader.GetInt32(1),
                    DataType = reader.GetString(2),
                    IsNullable = reader.GetBoolean(3),
                    IsPrimaryKey = reader.GetBoolean(4),
                    IsForeignKey = reader.GetBoolean(5),
                    IsUnique = reader.GetBoolean(6),
                };
            }
        }
    }
}

Where ColumnInfoQuery is a static string like this:

SELECT c.[column_name]
     , CAST(c.[ordinal_position] AS int) [ordinal_position]
     , CASE WHEN c.[data_type] IN ( 'bit', 'date', 'datetime', 'smalldatetime', 'int', 'bigint', 'smallint', 'tinyint', 'real', 'money', 'smallmoney', 'image', 'text', 'ntext' )  THEN c.[data_type]
            WHEN c.[character_maximum_length] IS NOT NULL
            THEN c.[data_type] + '(' + CAST(c.[character_maximum_length] AS VARCHAR(30)) + ')'
            WHEN c.[datetime_precision] IS NOT NULL 
            THEN c.[data_type] + '(' + CAST(c.[datetime_precision] AS VARCHAR(30)) + ')'
            WHEN c.[numeric_scale] IS NOT NULL 
            THEN c.[data_type] + '(' + CAST(c.[numeric_precision] AS VARCHAR(30)) + ',' + CAST(c.[numeric_scale] AS VARCHAR(30)) + ')'
            WHEN c.[numeric_precision] IS NOT NULL 
            THEN c.[data_type] + '(' + CAST(c.[numeric_precision] AS VARCHAR(30)) + ')'
            ELSE c.[data_type]
       END [data_type]
     , CAST(MAX(CASE c.[is_nullable] WHEN 'YES' THEN 1 ELSE 0 END) AS bit) [is_nullable]
     , CAST(MAX(CASE WHEN pk.[constraint_type] = 'PRIMARY KEY' THEN 1 ELSE 0 END) AS bit) [is_primary_key]
     , CAST(MAX(CASE WHEN pk.[constraint_type] = 'FOREIGN KEY' THEN 1 ELSE 0 END) AS bit) [is_foreign_key]
     , CAST(MAX(CASE WHEN pk.[constraint_type] = 'FOREIGN KEY' THEN 0 ELSE 1 END) AS bit) [is_unique]
FROM information_schema.columns c
LEFT JOIN information_schema.constraint_column_usage ccu 
        ON c.[column_name] = ccu.[column_name] 
        AND c.[table_name] = ccu.[table_name] 
        AND c.[table_schema] = ccu.[table_schema]
        AND c.[table_catalog] = ccu.[table_catalog]
LEFT JOIN information_schema.table_constraints pk 
        ON pk.[constraint_name] = ccu.[constraint_name]
        AND pk.[table_name] = ccu.[table_name] 
        AND pk.[constraint_schema] = ccu.[table_schema]
        AND pk.[constraint_catalog] = ccu.[table_catalog]
        AND pk.[constraint_type] IN ( 'PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE' )
WHERE c.[table_schema] = @tableSchema
        AND c.[table_name] = @tableName
GROUP BY c.[table_schema], c.[table_name], c.[column_name], c.[ordinal_position]
       , c.[data_type], c.[character_maximum_length], c.[datetime_precision]
       , c.[numeric_precision], c.[numeric_scale], c.[is_nullable]

Solution 3

Yes, you can determine which columns are primary keys by requesting the schema for the Indexes. Then search the indexes for the specific colum / table

DataTable indexes = conn.GetSchema("Indexes");
List<string> PrimaryKeys = new List<string>();
foreach (DataRow row in indexes.Rows)
  if (Convert.ToBoolean(row["PRIMARY_KEY"]))
    PrimaryKeys.Add(row["TABLE_NAME"] + "." + row["COLUMN_NAME"]);

the PrimaryKeys will contain the list of primary keys in the database. simply check if your [table].[column] is in this list.

Comments

[p.s.w.g]

I've thought about that but I also need to reliably retrieve the underlying SQL datatype of the column (e.g. NVARCHAR(255) as apposed to System.String). AFAIK GetSchema("Columns") is the only way to do that. I'm really hoping to avoid making 2 calls to the database.

[Laszlo Boke]

I don't know that, thanks. Maybe a select from system views? But that will be specific for a given provider ...

[p.s.w.g]

I ended up going with a solution which uses system views for the SQL server provider and combination of FillSchema and GetSchema for more generic providers. It's not pretty, but it works reasonably well on the providers I need it to. Thanks for your help.

[quentin-starin]

Thank you. I ended up finding dbschemareader.codeplex.com which got me a long ways but still doesn't return everything accurately. Writing some integration tests for a migration helper lib bitbucket.org/qes/easymigrator

[Chris Hayes]

I don't think this works against every server version of Sql Server, vr 2008 doesn't return a 'PRIMARY_KEY' column

[t3chb0t]

Exactly what I was looking for. Much easier then the accepted answer and the MS SQL Server also supports this: SQL Server Schema Collections

[Arvo Bowen]

I can't get this to work on SQL server 2016 either... :/

[Arvo Bowen]

As for MSSQL 2016 the "PrimaryKeys" collection (as you show in your answer) does not exist at all. So I don't think this will work for MSSQL server.