How to get the primary key column name

[Ellis Yu]

Dear all,

I want to design a data class model that it can return the record
according to the table name and the primary column parameters value.
Assuming that there's only one primary column, is it possible to do so??
Thanks

Best Rdgs
Ellis

 

[J L]

Not sure I do it the best way but here is what I do

myReader is a data reader
cmd is a command
dt is a data table
dRow is a data row
primaryKey is a string that ends up with the name of the primary key
column

cmd.CommandText = "SELECT * FROM " & theTableName
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
dt = myReader.GetSchemaTable()
for each dRow in dt.Rows
if dRow("IsKey") then
primaryKey = dRow("ColumnName")
end if
next

HTH,
John

PS If anyone has a better way, please let me and Ellis know. Thanks.

 

[Jim Brandley]

Do you want to do this at run-time, or in a code generator?

 

[Cor Ligthert]

Ellis,

I don't see the problem.

\\\
dim a as new dataset
dim b as new datataadapter("Select * from C where PrimKey = 1",conn)
b.fill(a)
///

It sounds for me strange that you know a table name and not the primary key,
however than take the answer from John.

However, I think that when you want to make something generic with that,
your solution is worthless.

A kind of this select.
"Select * from IdoNotKnowWhich Where IdoNotKnowWhat = IdoNotKnowWhat"

However just my thought,

Cor

 

[J L]

Hi Cor,
About knowing the table name and not the primary key...what I do is
discover all details of the data base. I start by getting the table
names and then get the keys and details of all the fields. Now if I
change something in the database, my DAL has the info automatically.

For example, in DAO we had the Seek function. I do the same with an
SQL statement I build on the fly but use only the primary keys that I
have discovered whtn the DAL was initialized. So the using code passes
me a data table and instructions to update the underlying table. My
first effort is to be sure the record does not already exist. If so I
do an update. If not I do an insert. And the way I tell it exist is
using the primary keys I read.

John

 

[Cor Ligthert]

John,

That I saw already in your code.



Cor

 

[Ellis Yu]

Thanks to you all, esp. John. The solution is exactly the one I want. Thank
you very much !!