How can I determine in C# whether a SQL Server database column is autoincrement?

Question

I need to be able to determine from the DataTable returned by DbConnection.GetSchema() whether a particular column in a SQL Server table is identity/auto-increment or not. I cannot resort to querying the system tables directly.

Oddly, if I connect to SQL Server via ODBC, the returned datatype for such a column is returned as "int identity" (or "bigint identity", etc.) but if I use the native SQL Server driver, there appears to be no distinction between an "int" column and an "int identity" column. Is there some other way I can deduce that information?

Answer 1

DataTable has Columns property and DataColumn has a property indicating auto-increment:

bool isAutoIncrement = dataTable.Columns[iCol].AutoIncrement

Answer 2

See This StackOverflow thread

The GetSchema() function will not return the info that you want. Nor will examining the DataTable schema properties. You'll have to go to a lower level and that will depend on the DBMS and likely its version.

The member below retrieves all tables with identity columns and then looks to match a specific table passed as an argument. The code can be modified to either return all the tables or the query optimized to look only for the table of interest.

// see: https://stackoverflow.com/questions/87747/how-do-you-determine-what-sql-tables-have-an-identity-column-programatically
private static string GetIdentityColumnName(SqlConnection sqlConnection, Tuple<string, string> table)
{
    string columnName = string.Empty;

    const string commandString =
         "select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS "
       + "where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 "
       + "order by TABLE_NAME";

    DataSet dataSet = new DataSet();
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = new SqlCommand(commandString, sqlConnection);
    sqlDataAdapter.Fill(dataSet);

    if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
    {
        foreach (DataRow row in dataSet.Tables[0].Rows)
        {
            // compare name first
            if (string.Compare(table.Item2, row[1] as string, true) == 0)
            {
                // if the schema as specified, we need to match it, too
                if (string.IsNullOrWhiteSpace(table.Item1) || string.Compare(table.Item1, row[0] as string) == 0)
                {
                    columnName = row[2] as string;
                    break;
                }
            }
        }
    }
    return columnName;
}