Architecture SQL,Datasets and relationships

[MB]

Hello!

I have a major question.

I have several tables in my SQL-DB with several relationships (without update/delete cascade) like for example:

- Customers
|
'- Contacts

If I in my vb project datagrid deletes a contact and the parent customer, the ds.update has to be made in the order daContacts.update(ds, "Contacts") and then daCustomers.update(ds, "Customers"), because you can't delete a parent row having a sub value in the child table.

If I in my vb project datagrid inserts a Customer and a Contact, the ds.update has to be made in the order daCustomers.update(ds, "Customers") and then daContacts.update(ds, "Contacts"), because you can't insert a contact that not belongs to a parent Customer.

Do I really have to take care of the order in which I call the update methods?

I also tried adding relationsships to my dataset in vb, but the problem almost reamains the same.

How should I do. Should I use update/delete cascade. I have learned to always only work with one table in each dataadapter. Is this correct? I think I've missed something.

Regards Magnus

 

[Kevin Yu [MSFT]]

Hi MB,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know if it is required to
update the data source with the order of first child table second parent
table. If there is any misunderstanding, please feel free to let me know.

Whether we have to update the data source in order, depends on if you have
set the relationship in the data source. When we add relationship in
DataSet, it only make constraints for data in the in-memory DataSet level.
However, the relationship in SQL Server add constraints to the data source
level, so that the data in the database are restricted by the foreign key
constraint. It offers more integrity for the database.

So if we have set a foreign key relationship in SQL Server data source
table, we have to update data source in order. If we haven't, just don't
care about the order of update.

To add a relationship in the SQL server table, in the design view of a
table, right click a column and select relationship from the popup menu.
For more information about adding the relationship, please check the
following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html
/dvrefrelationshippropertypage.asp

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

You're right Kevin, but consider this scenario
Customers and orders tables, with foreign key constraints both in sql server and datase

You have an id in Custormers table with autoincrement set to true, so you're forced to update customers table first, in order to get the real id value, then update this id in the orders table and finally you can call the update method for the Orders DataAdapte

Now, if you are inserting a new customer it works fine (and it's the only way to do the work), but if you are deleting a customer you need to invert the order of the update method calls

I had similar problem about a week ago and I had to disable the foreign key constraints in SQL Server

Is there a better way to handle it?

 

[Magnus Blomberg]

Hello both!

I think this has several solutions, but not any working in 100%. I think the best way of doing this is to not use any SQL releationships. Unfortunately this means not 100% of consistency, though updates from other applications or by SQL Query, not are using the releationships (who doesn't exists in the database).

The Concept

The best would be if MS develop their SQL-client components to automatically load (by option) the relations from the database into a dataset, and then had a function to automatically update all dataadapters used to load data into the dataset, using commands in the orders accepted by the database. The delete command before the inserts in the subtables, the insert command before the deletes in the parenttable and so on.

(Code below will not work. It's an idea of the future)

Fill
daCustomers.fill(ds,"Customers",true) 'true would build the relationships from the databas to the dataset
daContacts.fill(ds,"Customers",true) 'true would build the relationships from the databas to the dataset
or
ds.fill(new dataadapter(){daCustomers,daContacts}) ' this could be used if you're not working with aliases

Update
'Instead of updating all dataadapters, you update from the dataset to all dataadapters it has been loaded by.
ds.update(new dataadapter(){daCustomers,daContacts}) ' this have to go through all dataadapters and do the separate commands is specific orders regarding to the releationships.

Please comment this idea of how to develop the idea of making the dataset concept better (although I think it's good).

Regards Magnus


You're right Kevin, but consider this scenario:
Customers and orders tables, with foreign key constraints both in sql server and dataset

You have an id in Custormers table with autoincrement set to true, so you're forced to update customers table first, in order to get the real id value, then update this id in the orders table and finally you can call the update method for the Orders DataAdapter

Now, if you are inserting a new customer it works fine (and it's the only way to do the work), but if you are deleting a customer you need to invert the order of the update method calls.

I had similar problem about a week ago and I had to disable the foreign key constraints in SQL Server.

Is there a better way to handle it?

 

[Kevin Yu [MSFT]]

Hi Magnus,

Thanks for you suggestions. If you need to delete the rows in the parent
table and need to delete all the rows that references this row in child
table automatically, please try to use Cascade Delete Related Fields in the
relationships property page. Here is a link for more information about this
option.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html
/dvrefrelationshippropertypage.asp

I think your suggestions are quite good. You can send mail directly to
(e-mail address removed). Your advice will be highly appreciated. I will also
send your feedback through a corresponding channel. Thanks again!

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Magnus,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."