Datasets and SQLServer keys

[Tekito]

I'm new to datasets, and trying to update a SQL Server database with one. I'm having trouble understanding how to correctly write the dataset info to the server db and preserve the relationship keys.

When I write to the main table in the server database, it will assign the new entry a new ID for the primary key. But as I am writing to several child tables as well (that have a field that references the main ID), how do I make sure I record the correct ID for these tables? After all, I do not know the ID until the server gives it to me.

[Ramy Mahrous]

Look when you treat with dataset you treat with database (I don't know if you've to set Enforce relationship property to true or it's set by default) so you can update it then reflect these changes to the physical database.

[Tekito]

I'm not sure I understand the response, but I don't think I explained my problem very clearly, so I'll start over:

I am trying to add new entries to the SQL Server database from a dataset. The database has a main table with a primary key as the ID, then several child tables referenced to the main table via this ID.

When I start to add new entries to the database, the first thing I do is add a new entry to the main table. The database will then assign this entry a new ID. To finish the job I have to add some entries to the child tables, but I need to make sure to give these tables the new ID so everything is tied together properly. How do I read this new ID, or otherwise ensure integrity? Are you saying somehow the dataset and database figure this out automatically?

[Ramy Mahrous]

My first answer is concept :) not for special case, once you load the database tables into your dataset, forget the physical database, and when you add entry to the primary table, you add it into dataset, so get the last entry key from the main table by anyway from dataset, then use it to add entries in child tables, and when you finish your work on the dataset, reflect that changes.

[Tekito]

I think I understand.

One thing is that I was never actually loading the database into the dataset, only vice versa. This is because my program only adds to the database, and never needs to read anything from it. So I didn't bother loading from the database, I just created the dataset independently. I suppose if it is necessary to somehow tie the dataset and database together then I could do so.

The next question is what if someone else is also adding to the database at the same time? Are their changes going to be reflected to my dataset? Could this cause confusion in the process?

[Ramy Mahrous]

As there are many users updating database, you need reflect dataset changes to the database once occurred. So you need to reflect\load and it's not good in performance perspective, so use Connected Mode, it's better in such cases.

[Tekito]

Okay thanks for the advice... sounds like I need to learn a little bit more about datasets.

[Ramy Mahrous]

Yes, preferable, and we're here to help you about anything you face, really I appreciate this word from you

sounds like I need to learn

, please mark it solved.