I'm using ADO.NET with a strongly typed dataset in C# (.NET 3.5). I want to insert a new row to two tables which are related in an 1:n relation.
The table Attachments holds the primary key part of the relation and the table LicenseAttachments holds the foreign key part.
AttachmentsDataSet.InvoiceRow invoice; // Set to a valid row, also referenced in InvoiceAttachments
AttachmentsDataSet.AttachmentsRow attachment;
attachment = attachmentsDataSet.Attachments.AddAttachmentsRow("Name", "Description");
attachmentsDataSet.InvoiceAttachments.AddInvoiceAttachmentsRow(invoice, attachment);
Of course when I first update the InvoicesAttachments table, I'll get a foreign key violation from the SQL server, so I tried updating the Attachments table first, which will create the rows, but will remove the attachment association in the InvoiceAttachments table. Why?
How do I solve this problem?
On the relation between the tables, ensure that the "Both Relation and Foreign Key Constraint" is selected and "Update Rule" is set to "Cascade". Combined with the "Refresh the data table" option on the adapter, after you insert your parent row, the updated ID will "Cascade" down the relationships, preventing foreign key violations in your dataset. Your child tables will then be ready to properly insert into the database.