0

I am trying to get List of tables and its corresponding columns with 3 constraints :

  1. IsNullable (I am getting this successfully with the help of GetSchema method)

  2. IsPrimarykey (not getting this information)

  3. IsAutoincrement (not getting this information)

Code :

public class Tables
{
   public string Name { get; set; }
   public List<Columns> Columns { get; set; }
}

public class Columns
{
   public string Name { get; set; }
   public string IsNullable { get; set; }
   public bool IsPrimarykey { get; set; }
   public bool IsAutoincrement { get; set; }
}

var list = (from table in connection.GetSchema("Tables").Select()
            let name = (string)table["TABLE_NAME"]
            let catalog = (string)table["TABLE_CATALOG"]
            let schema = (string)table["TABLE_SCHEMA"]
            select new Tables
                 {
                     Name = schema + "." + name,
                     Columns =
                     connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, new[] { catalog, schema, name }).AsEnumerable()
                     .Select
                     (
                          c => new Columns
                          {
                             Name =col[3].ToString(),
                             IsNullable = col.Field<string>("is_nullable"),
                             IsPrimarykey = ??not getting whether column is primary key or not
                             IsAutoincrement = ??not getting whether column is autoincrement or not
                          }
                     ).ToList();
               ).ToList();

It seems like this is not possible with the GetSchema method.

How to get primary key and autoincrement information of columns for each of the tables?

halfer
  • 19,824
  • 17
  • 99
  • 186
I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216
  • 2
    Possible duplicate of [How can I determine in C# whether a SQL Server database column is autoincrement?](https://stackoverflow.com/questions/4834592/how-can-i-determine-in-c-sharp-whether-a-sql-server-database-column-is-autoincre) – Alen Genzić Aug 02 '17 at 11:37
  • For getting the Primary keys, you can run an SQL as per this [SO post](https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table) and read the result and populate your objects. This is assuming you are using SQL Server. The query is likely to be different for different databases. If you search for "sql for getting primary keys", you are likely to find the relevant SQL for your required database. – Subbu Aug 02 '17 at 12:22

0 Answers0