deleting rows in one-to-many relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear Ng,

I have a 1(master table) : n(first child table) : m(second child table)
relationship in my sql server and strongly typed dataset. Now I locate the
corresponding row in the master table.

DataAccessControl.DocumentTemplateRow rowTemplate =
tblTemplate.FindBydocumentTemplateId(templateId);

Then i request all child rows:

DataRow[] rowsToDelete =
rowTemplate.GetChildRows("DocumentTemplateDocumentField");

Then I want to delete all child rows in the child tables:

foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete)
{
tblFields.RemoveDocumentFieldRow(row);
}
dac.updateDataAccessLayer();

The delete rules of both relations (from the master to the first child and
from the first to the second child) is set to cascade (in the database and in
the dataset). But if I execute the code, just the rows in the second child
table are deleted. What do I do wrong? If I use additionally the row.delete()
command

foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete)
{
row.Delete();
if (row.RowState == DataRowState.Deleted)
tblFields.RemoveDocumentFieldRow(row);
}
dac.updateDataAccessLayer();

I'll get a DBConcurrencyException! What is wrong? What I want to achieve is
that the master record is kept and all child records are deleted.

Thanks in advance and best regards,
Patrick Braunschweig
 
Hi Patrick,

Use only DataRow.Delete as Remove removes the row and adapter won't see it.
Delete just marks row's state to Deleted (if the previouse state is Added
then row is removed from collection as there is no need to delete it).
 
Hi Miha,

thanks I tried this before. But it just works, if I switch the updating
order of my OleDbAdapters:

this.oleDbDataAdapter1.Update(dal);
this.oleDbDataAdapter2.Update(dal);
this.oleDbDataAdapter4.Update(dal); no 4
this.oleDbDataAdapter3.Update(dal); no 3


if I do it in the regular order like
this.oleDbDataAdapter1.Update(dal);
this.oleDbDataAdapter2.Update(dal);
this.oleDbDataAdapter3.Update(dal); no 3
this.oleDbDataAdapter4.Update(dal); no 4

then I get this DbConcurrencyException. I do not understand why it is like
that??? Do you have an explanation for that?

Best regards,
Patrick


Miha Markic said:
Hi Patrick,

Use only DataRow.Delete as Remove removes the row and adapter won't see it.
Delete just marks row's state to Deleted (if the previouse state is Added
then row is removed from collection as there is no need to delete it).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Patrick said:
Dear Ng,

I have a 1(master table) : n(first child table) : m(second child table)
relationship in my sql server and strongly typed dataset. Now I locate the
corresponding row in the master table.

DataAccessControl.DocumentTemplateRow rowTemplate =
tblTemplate.FindBydocumentTemplateId(templateId);

Then i request all child rows:

DataRow[] rowsToDelete =
rowTemplate.GetChildRows("DocumentTemplateDocumentField");

Then I want to delete all child rows in the child tables:

foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete)
{
tblFields.RemoveDocumentFieldRow(row);
}
dac.updateDataAccessLayer();

The delete rules of both relations (from the master to the first child and
from the first to the second child) is set to cascade (in the database and
in
the dataset). But if I execute the code, just the rows in the second child
table are deleted. What do I do wrong? If I use additionally the
row.delete()
command

foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete)
{
row.Delete();
if (row.RowState == DataRowState.Deleted)
tblFields.RemoveDocumentFieldRow(row);
}
dac.updateDataAccessLayer();

I'll get a DBConcurrencyException! What is wrong? What I want to achieve
is
that the master record is kept and all child records are deleted.

Thanks in advance and best regards,
Patrick Braunschweig
Click to expand...
Click to expand...
 
Hi Patrick,

Of course, you need to delete rows from children to master and insert rows
from master to children.
Instead of passing dal as parameter, use dal.GetChanges(DataRowState) to
filter the rows required for the pass, for example, when deleting you will
pass DataRowState.Deleted, when inserting you'll pass DataRowState.Added,
etc.
You need at least two passes, one for Delete and other for Add (Modified
rows doesn't care of Update order).
When the transaction is complete, you'll have to Merge those rows back to
original DataSet.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Patrick said:
Hi Miha,

thanks I tried this before. But it just works, if I switch the updating
order of my OleDbAdapters:

this.oleDbDataAdapter1.Update(dal);
this.oleDbDataAdapter2.Update(dal);
this.oleDbDataAdapter4.Update(dal); no 4
this.oleDbDataAdapter3.Update(dal); no 3


if I do it in the regular order like
this.oleDbDataAdapter1.Update(dal);
this.oleDbDataAdapter2.Update(dal);
this.oleDbDataAdapter3.Update(dal); no 3
this.oleDbDataAdapter4.Update(dal); no 4

then I get this DbConcurrencyException. I do not understand why it is like
that??? Do you have an explanation for that?

Best regards,
Patrick


Miha Markic said:
Hi Patrick,

Use only DataRow.Delete as Remove removes the row and adapter won't see
it.
Delete just marks row's state to Deleted (if the previouse state is Added
then row is removed from collection as there is no need to delete it).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Patrick said:
Dear Ng,

I have a 1(master table) : n(first child table) : m(second child table)
relationship in my sql server and strongly typed dataset. Now I locate
the
corresponding row in the master table.

DataAccessControl.DocumentTemplateRow rowTemplate =
tblTemplate.FindBydocumentTemplateId(templateId);

Then i request all child rows:

DataRow[] rowsToDelete =
rowTemplate.GetChildRows("DocumentTemplateDocumentField");

Then I want to delete all child rows in the child tables:

foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete)
{
tblFields.RemoveDocumentFieldRow(row);
}
dac.updateDataAccessLayer();

The delete rules of both relations (from the master to the first child
and
from the first to the second child) is set to cascade (in the database
and
in
the dataset). But if I execute the code, just the rows in the second
child
table are deleted. What do I do wrong? If I use additionally the
row.delete()
command

foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete)
{
row.Delete();
if (row.RowState == DataRowState.Deleted)
tblFields.RemoveDocumentFieldRow(row);
}
dac.updateDataAccessLayer();

I'll get a DBConcurrencyException! What is wrong? What I want to
achieve
is
that the master record is kept and all child records are deleted.

Thanks in advance and best regards,
Patrick Braunschweig
Click to expand...
Click to expand...
Click to expand...
 
Back
Top