0

I need to know in C# if a column of a Table in SQL Server 2005 is autonumeric. I know that if I make a query to get a DataTable and I go through the columns, I could use something like

if (table.Columns[i].AutoIncrement) bla bla

The problem is that AutoIncrement is always false, even when the column is an Identity and autoincrement column, and I don't know how to find out this, besides this way.

I would like to know the same for an Access database, though.

Thank you very much!!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Roman
  • 1,691
  • 4
  • 18
  • 35

2 Answers2

1

what you need to do is call the GetSchema method to retrieve also the db schema / metadata and not only the data from the database, have a look here:

GetSchema and Schema Collections (ADO.NET)

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
  • Thanks, I have achieved what I wanted with this code DataTable schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { if (bool.Parse(row[schema.Columns["IsAutoIncrement"].Ordinal].ToString())) return true; } – Roman Sep 21 '11 at 09:26
  • well you have to change some objects and use Oledbconnection instead of SqlConnection, try to go like that and let us know ;-) – Davide Piras Sep 22 '11 at 08:01
  • I've done that, but the problem is that Access returns the DataType as a number, instead of the real type, and I don't know how can I translate it – Roman Sep 22 '11 at 13:30
0

You can achieve this for SQL Server 2005 by using the Microsoft.SqlServer.Management.Smo namespace - Click here for more info 

string conn = string.Format(@"Data Source={0};Initial Catalog={1};User ID={2};Password={3}",
            "DATASOURCENAME", "DB", "USERNAME", "PASSWORD");

 Microsoft.SqlServer.Management.Smo.Server s = new Microsoft.SqlServer.Management.Smo.Server(
                    new Microsoft.SqlServer.Management.Common.ServerConnection(
                        new System.Data.SqlClient.SqlConnection(
                    conn)));

 Microsoft.SqlServer.Management.Smo.Database db =
                    s.Databases["YOUR_DATA_BASE_NAME"];
                Microsoft.SqlServer.Management.Smo.Table tbl =
                    db.Tables[0];//Or you can get the table by table name

 List<Microsoft.SqlServer.Management.Smo.Column> autoIncrementClmns = 
                    new List<Microsoft.SqlServer.Management.Smo.Column>();

 foreach (Microsoft.SqlServer.Management.Smo.Column clmn in tbl.Columns)
  {
     if (clmn.IdentityIncrement > 0)//Check if this column is AutoIncrement
         autoIncrementClmns.Add(clmn);
  }

Needed assemblies for SMO are:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo

Found at YOUR_SYSTEM_DRIVE_NAME:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

Rami Alshareef
  • 7,015
  • 12
  • 47
  • 75
  • Sorry, I have seen your answer after the previous one, and that one is valid for me. But thank you for your help! – Roman Sep 21 '11 at 09:28