Inserting Rows in Relationship using a Strongly Typed DataSet

Question

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?

Lignite

Just a note... when i used strongly typed datasets, I got a HUGE performance hit everytime I instantiated it. – Gigot

Yes, I'm aware of that. What do you use instead? Simply typed DataSets? – Lignite

@Manuel: Are your strongly typed data sets the same as what I would find in my asp.net project datasets as in do you have auto-generated table adpaters or are you creating them manually? – Nakia

@Breadtruck: Yes, I use auto generated DataSets and TableAdapters. – Lignite

@Manuel: If your table has many records (more than a couple of hundred) just make sure that you never let your table adapter GetData pull the whole table, throw in a @column = @columnvalue so you only return the rows you want to work with and not the whole table. – Nakia

Where are you at with this problem, did you find what it was? – Nakia

Since this is still a maintenance task, I simplly updated the whole DataSet after each add of Attachment. I'll use LINQ for the project itself, instead of strongly typed DataSets. – Lignite

Answer 1

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.

Eidson

Answer 2

Some things to try:

When you configure the tableadapter, did you click on advanced options, and check on "refresh data table" so that it will retrieve the identity column value?

For me sometimes I either forgot to check it, or it didn't save the configuration correctly because I didn't have my table identity increment/seed set for whatever reason. Are you using identity increment on the table?

You might also consider just re-creating the adapters for those two tables.

Usually when I go back over everything I find it was something stupid on my part.

Lastly, you might consider calling update on the Primary table, then manually grab the primary key value and manually set the value when you insert the child record. If that doesn't make sense let me know and I will post code.

Nakia

Answer 3

You need to tell your parent table's table-adapter to refresh the data-table after update operation. This is how you can do that.

1. Open the properties of ProgramUserGroupTableAdapter -> Default Select Query -> Advnaced options. and Check the option of Refresh the data table. Save the adapter now. Now when you call update on table-adapter, the data-table will be updated [refreshed] after the update operation and will reflect the latest values from database table. if the primary-key or any coloumn is set to auto-increment, the data-table will have those latest value post recent update.

2. Now you can Call the update as pug.Update(dsUserGroup.ProgramUserGroup);

3. Read latest values from the ProgramUserGroup coloumns and assign respective values into the child table before update. This will work exactly the way you want.

alt text http://ruchitsurati.net/files/tds1.png

Rendon