advanced ADO.NET and Oracle Database
-
hi all just wonder if anyone have done any relatively complex project in oracle database. it seems hard to make an app for oracle database using ADO.NET dataset. in my app, I bind all the fields to all kind of controls and there are parent/child tables. so I create datarelations and all that for the datatable in a same dataset. Q1. when inserting new parent/child records, oracle database doesn't support scop_indentity(), how does one get the real id from the database and update the child records before they get inserted into the database? Q2. when adding new child records for existing parent entry, does the datarelation automatically add the parent foreign key into the newly added child record? it seems that's not the case, since it doesn't work for me when I try to add new child record. I posted a similar thread in the ms newsgroup, one of the ms guy showed me some of the sample how things are done in sql server 2000 and access, but not much with oracle. he also mentioned the use of sequence. get the some-sequence.currval to get the real id, but that's not connection oriented, because multiple users might insert record at the same time, how can one guarantee it is the correct id for the record. anyone have any idea? thx:) kevin
-
hi all just wonder if anyone have done any relatively complex project in oracle database. it seems hard to make an app for oracle database using ADO.NET dataset. in my app, I bind all the fields to all kind of controls and there are parent/child tables. so I create datarelations and all that for the datatable in a same dataset. Q1. when inserting new parent/child records, oracle database doesn't support scop_indentity(), how does one get the real id from the database and update the child records before they get inserted into the database? Q2. when adding new child records for existing parent entry, does the datarelation automatically add the parent foreign key into the newly added child record? it seems that's not the case, since it doesn't work for me when I try to add new child record. I posted a similar thread in the ms newsgroup, one of the ms guy showed me some of the sample how things are done in sql server 2000 and access, but not much with oracle. he also mentioned the use of sequence. get the some-sequence.currval to get the real id, but that's not connection oriented, because multiple users might insert record at the same time, how can one guarantee it is the correct id for the record. anyone have any idea? thx:) kevin
Oracle doesn't have autonumeric fields. You can achive a similar behavior with sequences. Sequences guarantee that the number generated is unique. Add a trigger to your table that inserts the sequence value on the table. Free your mind...
-
Oracle doesn't have autonumeric fields. You can achive a similar behavior with sequences. Sequences guarantee that the number generated is unique. Add a trigger to your table that inserts the sequence value on the table. Free your mind...
-
sorry, Gillermo I am way pass that level, in fact select max(someColumn) + 1 will do. it seems like you misunderstand my question. kevin;)
I hope you're doing some locking, otherwise you'll get bitten by concurrency bugs. Example: Say the current maximum is n. Client A's connection performs SELECT MAX() + 1, then, before client A's connection performs an INSERT, client B's connection also does SELECT MAX() + 1. Both clients now think that their insert should be numbered n + 1. The second client to INSERT will either cause a unique constraint violation if you've defined that someColumn should be unique, or you'll get two values. SELECT MAX() also causes more work for the database. Instead of looking up and updating a single value, it has to seek through the index every time. (Speaking as someone who's used this technique in SQL Server.)