Deleting a data set row with relationships (and just one DataTable!)

J

Jorge

Hello all!,

I have a dataset with just one DataTable : "titles" (using pubs
database). I want to delete a specific DataRow, the problem is that
"titles" is related with the table "sales" on the field "title_id",
and when I try to delete the record, ADO.NET throws me an exception
....

Dim table As DataTable = dataset.Tables("titles")
table.Rows(0).Delete()
dataadapter.Update(dataset, "titles") 'error!


I know I can to create DataRelations between DataTables, but in this
case I just have one DataTable. How can I delete the related records
too? Do I need to use a Command object before the Delete method??

I'm confused! ...

Any suggestion will be appreciated ...

Robert
 
I

Ian Phillips

You will need to do one of two things:

1. Cascade deletes in your database so that,when you update the database
with the dataset modifications, the child rows are deleted as well.

2. Add in the other, related tables into the dataset and add a cascade
delete rules for the dataset relations.

In the second case, you will need to make sure that the child tables are
updated to the database before you update the parent.

HTH,
ian
 
J

Jorge

Kathleen, thank you for your help. You misunderstood me (or my
explanation was not the best). I want delete a parent AND delete its
child records related too.

The "titles" table is the parent and "sales" is the child. I filled a
dataset with the content of the first one, my dataset just have ONE
DataTable.

When I use the Delete method, it launches an exception obviously
because first at all I need to delete its related records (on "sales"
table).

My question is: how can I delete the related records on the "sales"
table? Do I need to use a Command?

Regards!
 
K

Kathleen Dollard

Jorge,

I'd do it with a stored procedure, but if that's not a good solution for
you...

You can use the RowUpdatign event to create a new command with a SQL
statement that delete the children. Or you could cycle through the DataSet
prior to the update, and again, create a command iwth a SQL statement to
delete each set of children. Short of cascading deletes on your server, and
I'd be hesitant to do that, I think those are about yoru only options.
 
I

Ian Phillips

If you are unwilling to load the tables into the dataset, you will have to
add the logic into the database itself. You can do that with a cascading
delete rule as discussed in option 1 of my initial post.

If you want all your cascading to be done in the app and not the RDBMS, then
you will have to at least load the rows for the records that will have to be
deleted. you can put a select clause into the sql for the selectCommand so
only the rows you are going to delete will be loaded. You need to update
the sales table before you update the titles table in this process.

HTH,

ian
 
I

Ian Phillips

BTW...

You will also have to cascade the delete into the titleAuthor and roysched
tables.

ian
 
K

Kathleen Dollard

Ian,

Those are certainly two valid options, but they are not the only options.
You can issue explicit deletes via stored proc or TSQL for the chlidren
before each parent record is deleted.
 
I

Ian Phillips

That is true....

But you can also set up explicit triggers for OnDelete events or you can
.....

I was just giving the two easiest methods. Now, don't get me wrong, I think
that using stored procedures is important whenever interacting with a
database management system to pull data. It is faster than sql delete
statements and it removes the risk of a malicious user imbedding sql into
your query. But, even when using a stored procedure to delete records from
a parent table, I have found that having the relation set up to
automatically delete child rows is faster and is, IMHO, better design. When
you have strict composition relationships, it only makes sense to cascade
updates and deletes.

ian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top