DataAdapter Cascading Deletes

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Hi,

I'm using OleDbDataAdapter objects to perform inserts,
updates, and deletes. Unfortunately, the class (or
perhaps the JET provider) won't let me include multiple
SQL delete statements in the adapter delete command.

I need a statement like "DELETE FROM table1 WHERE id = ?;
DELETE FROM table2 WHERE id = ?" etc.

Am I going to have to do this with some goofy loop using
individual command objects and ExecuteNonQuery methods?
This is going to be a major pain, especially since I need
to wrap it all into a transaction.

Randy
 
Are you needing to cascade the delete to child tables? If so, just enable
cascade delete on the Foreign Keys.
The DataAdapter can only have one command per action (Insert, Update,
Delete, Select).
Generally, you will/should have an adapter for each table that will be
updated, etc., so you can hook your other delete statements as the Delete
Command there.
 
Hey Morgan,

I haven't had the need to specify foreign keys constraints
in my data schema. I prefer to keep things simple.
Furthermore, in an effort to minimize network traffic, I
seldom carry around all related data in a single dataset.

Cascade deletes are easy with SQL Server stored procedures
because multiple commands can be evoked in one procedure.

It seems I'm about to be bitten.

Wait! What's this? OleDbDataAdapter.RowUpdating event?
Oh please, please...

Randy
 
Touché! Cascading deletes were available in Access long before they were in
SQL Server, much to the enjoyment of SQL Server developers when they were
introduced, BTW. Check out the Relationships icon from the toolbar (has 2
boxes with a line pointing to a single box). You can define actions for
update and delete.

As far as keeping things "simple", enjoy the bliss of a
single/multi-function table design while you can.When someone asks for a
report, good luck. How are you managing (if needed) parent-child
relationships and look-up tables? I'm not one to "knock" a design, but I've
worked with a similar schema before, and it wasn't pretty when it came to
reporting.

Hopefully the OleDbDataAdapter.RowUpdating event will provide the
functionality you need, but, and I could be mistaken here, you'll need to do
a .Refresh on the table &/or dataset to actually reflect the changes,
regardless of your decision. VB(which I assume you're using) doesn't do
databinding easily. the Net framework is just as bad, if not worse.

Truth be told, databinding sucks for winforms. I welcome any challenge (with
solutions and that don't require massive workarounds!!!) to prove otherwise.
Defaulting a databound textbox with no text to String.Empty isn't
acceptable. Having to implment the .Parse event for each DataAdapter is
mindless. I'll never store an empty string and consider it null. They're not
equal.
 
Morgan,

I confess I am generally recalcitrant about "smart"
database structures. I have wasted many hours over the
years trying to get features that are supposed to
be "automatic" and "easy" to actually work in real world
scenarios. My database structure is fully relational, it
just doesn't know it. I build datasets as the context
requires from a library of hand coded data tables objects.

I suppose I could add foreign key constraints, but up
until now I haven't had any reason to wrestle with
juggling "smart" relations because my application hasn't
warranted it. I can build a relational structure into a
dataset with a Select statement. I just don't try to
maintain relational pointers.

You're right though. If and when I write an app with
complex reporting requirements, I'll have to figure out
how to get an automatic relational engine running. I
haven't even "opened the box" on Crystal Reports yet.

While I haven't had a chance to fiddle yet, my hope is
that I can write an event handler that will perform my
cascade deletes directly on the database via a Sql
statement. I really have no reason to populate datatables
just so that I can delete them. The RowUpdating event
gives me a handle on each row, which will make it much
easier. I'm not sure how the transaction processing will
work...

Maybe I'm just being silly. We'll soon find out.

As far as Access cascade deletes, I'll try this if I have
to. To be frank, I don't know much about Access. My app
is basically done using SQL Server. I added Access as an
option for single user installations with the idea of
keeping the database file as simple as possible. Too bad
Access can't handle stored procedures.

Randy
 
Oh,

And databinding? What's that? Another black box...

I will never again trust Windows databinding.
Unfortunately, sometimes it's unavoidable.

Randy
 
Swoosh! All NET!

Seems to work, including rollbacks.

Randy

Public Sub MyUpdate(tbl As DataTable)
Dim da As OleDbDataAdapter = Me.GetMyAdapter()
Dim txn As OleDbTransaction
Try
Me.cnConnection.Open()
txn = Me.cnConnection.BeginTransaction()
da.InsertCommand.Transaction = txn
da.UpdateCommand.Transaction = txn
da.DeleteCommand.Transaction = txn
AddHandler da.RowUpdating, AddressOf Me.MyCascade
da.Update(tbl)
tbl.AcceptChanges()
txn.Commit()
Catch e As Exception
txn.Rollback()
Finally
Me.cnConnection.Close()
EndSub

Private Sub MyCascade(sender as Object, e As
OleDbRowUpdatingEventArgs)
If e.StatementType = StatemetType.Delete Then
Dim sMyId As String = e.Command.Parameters
(0).Value.ToString()
Dim cmd As New OleDbCommand
cmd.Transaction = e.Command.Transaction
cmd.Connection = Me.cnConnection
Try
cmd.CommandText = "DELETE FROM Table1 WHERE MyId
= " & sMyId
cmd.ExecuteNonQuery()
cmd.CommandText = "DELETE FROM Table2 WHERE MyId
= " & sMyId
cmd.ExecuteNonQuery()
Catch e As Exception
Throw New Exception(e.Message)
End Try
End If
End Sub
 
Back
Top