How to determine if a table has a column that is autoincrement(Identity)?

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hi All,

I am trying to find out how to determine if a table that I am
working with contains a column that is an identity column (ie,
has autoincrement set to true). I am working in C# and I'm
already using the GetOleDbSchemaTable method to get the schema
for the table I am interested in. However, this DataTable that
I get back does give me the names of the columns but it does
not tell me if a column is an identity column, or at the very
least I don't know where to look for it in the Datatable. This
is the call I am using:

DataTable dtCols = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Columns,
new Object[]{null, null, sTableName, null});

Now I've seen posts and visted websites that say you can get
the autoincrement/identity flag by accessing the Columns data
member as such:

dtCols.Columns.AutoIncrement

Now, although this flag is there, it seems to be associated
with some meta-data column. In fact, all of the Columns that
are in the DataTable(dtCols, the table that I get from the
call above) seem to be associated with the table's meta-data
(like, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME). Its the Rows
data member that contains information on the columns of the
table but I can't find any reference to the autoincrement or
identity flag. I was hoping it would be in this DataTable
since I could make one function call and get all I need.

Can someone please tell me how I can programatically get this
flag or tell me where to find it? Thanks in advance.

Sincerely,
Joe
 
Hi Miha,

Thank you for your reply.

I using an Access 2000 database. If you know how to get the info
through an MSDE (SQL Server) 2000 database, I'd be interested in
knowing how to do it using that DB as well.

-- Joe


Miha Markic said:
Hi Joe,

What database are we talking about?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Joe said:
Hi All,

I am trying to find out how to determine if a table that I am
working with contains a column that is an identity column (ie,
has autoincrement set to true). I am working in C# and I'm
already using the GetOleDbSchemaTable method to get the schema
for the table I am interested in. However, this DataTable that
I get back does give me the names of the columns but it does
not tell me if a column is an identity column, or at the very
least I don't know where to look for it in the Datatable. This
is the call I am using:

DataTable dtCols = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Columns,
new Object[]{null, null, sTableName, null});

Now I've seen posts and visted websites that say you can get
the autoincrement/identity flag by accessing the Columns data
member as such:

dtCols.Columns.AutoIncrement

Now, although this flag is there, it seems to be associated
with some meta-data column. In fact, all of the Columns that
are in the DataTable(dtCols, the table that I get from the
call above) seem to be associated with the table's meta-data
(like, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME). Its the Rows
data member that contains information on the columns of the
table but I can't find any reference to the autoincrement or
identity flag. I was hoping it would be in this DataTable
since I could make one function call and get all I need.

Can someone please tell me how I can programatically get this
flag or tell me where to find it? Thanks in advance.

Sincerely,
Joe
Click to expand...
Click to expand...
 
On 2 Jun 2004 20:22:11 -0700, (e-mail address removed) (Joe) wrote:

¤ Hi All,
¤
¤ I am trying to find out how to determine if a table that I am
¤ working with contains a column that is an identity column (ie,
¤ has autoincrement set to true). I am working in C# and I'm
¤ already using the GetOleDbSchemaTable method to get the schema
¤ for the table I am interested in. However, this DataTable that
¤ I get back does give me the names of the columns but it does
¤ not tell me if a column is an identity column, or at the very
¤ least I don't know where to look for it in the Datatable. This
¤ is the call I am using:
¤
¤ DataTable dtCols = conn.GetOleDbSchemaTable(
¤ OleDbSchemaGuid.Columns,
¤ new Object[]{null, null, sTableName, null});
¤
¤ Now I've seen posts and visted websites that say you can get
¤ the autoincrement/identity flag by accessing the Columns data
¤ member as such:
¤
¤ dtCols.Columns.AutoIncrement
¤
¤ Now, although this flag is there, it seems to be associated
¤ with some meta-data column. In fact, all of the Columns that
¤ are in the DataTable(dtCols, the table that I get from the
¤ call above) seem to be associated with the table's meta-data
¤ (like, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME). Its the Rows
¤ data member that contains information on the columns of the
¤ table but I can't find any reference to the autoincrement or
¤ identity flag. I was hoping it would be in this DataTable
¤ since I could make one function call and get all I need.
¤
¤ Can someone please tell me how I can programatically get this
¤ flag or tell me where to find it? Thanks in advance.


An auto increment column is implemented differently depending upon the type of database you are
working with. It isn't exposed via GetOleDbSchema.

As Miha mentioned, we would have to know what the database product is that you are using.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
On 3 Jun 2004 07:00:04 -0700, (e-mail address removed) (Joe) wrote:

¤ Hi Miha,
¤
¤ Thank you for your reply.
¤
¤ I using an Access 2000 database. If you know how to get the info
¤ through an MSDE (SQL Server) 2000 database, I'd be interested in
¤ knowing how to do it using that DB as well.
¤

You will probably need to use DAO or ADOX (Microsoft ADO 2.x for DDL and Security). Below is an
example that uses ADOX:

Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

Dim col As ADOX.Column
For Each col In ADOXCatalog.Tables("Table1").Columns
If col.Properties("AutoIncrement").Value = True Then
Console.WriteLine(col.Name)
End If
Next

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top