Get primary field name

[bungpeng]

Is there anyway to get to know the table primary field name?

 

The information I have is table name and record id of this table, but to form a SQL statement to select those records, I need to know the table primary field. Is there anyway I can get it?

[Nerseus]

Do you mean find out the Primary field in SQL Server itself, or in a DataTable based on a SELECT from a SQL Server table?

 

-Nerseus

[bungpeng]

not DataTable, I haven't select any records yet. and not only SQL Server, because my application is database independent.

 

Just want to know, is it .NET provide any feature to let us find out the primary key field name of a table?

 

P/S: My purpose is to use this key field to form a SQL statement.

 

TQ

[Nerseus]

For SQL Server you can use SQLDMO (a COM object) or you can query the system tables if the user has admin priveledges. I'm not that familiar with the SQLDMO anymore, but I remember it being quite easy (connect to the server, find a database and table, then get the primary keys).

 

-Nerseus

[bungpeng]

I can't use that, because as what I said, my application is database independent, I need to cater for other database as well.

 

TQ anyway for your reply! Is there any other way?

[quwiltw]

Perhaps you could create a temporary dataset then use the DataAdapter's FillSchema() method then traverse the schema to determine what the primary key is which FillSchema should have been able to determine.

 

To keep it independent, I assume you already have some sort of factory class that return instances and you are only dealing with interfaces? If so just change the interface to DbDataAdapter (which provides FillSchema) which all of the big adapters inherit and should keep you neutral.

[bungpeng]

even to create a temporary dataset, I need to read the whole table in dataset right? I just feel that it is not quite efficient....

[liquidspaces]

I'm not very experienced in VB so there might be a simple answer, but I have to ask this question anyway:

 

How are you going to write a program that interacts with a database when you have no idea how to reference the data?

[bungpeng]

Because I am developing a module to control 'record' level of security in my application. Since it is not program hard coded, and it is flexible to maintain which record level user want to control, so I need to write a function to accept a SQL statement and regenerate a new SQL statement. That why I need way to find out my primary key in table.

 

Is it more confusing? but at least you know there is a reason why I want to get this answer right? TQ :)

[quwiltw]

FillSchema doesn't "read" all the data in the table it just gets the structure.