Matt,
I have an n-Tier Intranet application. One of the pages in the
site needs to do multiple updates, deletes, inserts, all in one big
transaction. If any of the updates fails, then everything must be
rolled back. To complicate the issue, these records contain a
master/detail relationship. So, if I create the master record, say
"Order", I have to also create the detail record, call it
"OrderDetail", which uses the OrderID as it's foreign key to Order.
Also, the field "OrderID" in the master table is an identity field
in sql 2000.
Yes, all of what you mention is possible in ADO.NET. You can submit
pending parent and child rows within the scope of a transaction, and
cascade newly retrieved parent identity values down to pending child rows
prior to their submission. However, there is no magic black box
property/method to perform this work. You'll have to write a little bit of
code.
Your scenario covers a number of factors, which I'll address
separately. As a result, this is a pretty long reply. Information
covering each portion of this scenario can be found in "Microsoft ADO.NET",
available from Microsoft Press. Forgive the formatting and word-wrap
problems.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
WRAPPING UPDATES IN A TRANSACTION
You can create a new Transaction object in ADO.NET simply by calling
Connection.BeginTransaction. To execute queries on that transaction,
either use the Transaction in the Command object's constructor or set the
Command object's Transaction property to your Transaction object.
Submitting updates with a DataAdapter through a transaction requires a
touch more code. You'll need to set the Transaction property on the
InsertCommand, UpdateCommand, and DeleteCommand to your new Transaction
object. If you need to execute the query in your SelectCommand (to call
DataAdapter.Fill, DataAdapter.FillSchema or to generate updating logic via
a CommandBuilder), you'll also need to set SelectCommand.Transaction to
your new Transaction.
Based on the information you've provided about your scenario, you're
probably using multiple DataAdapters to submit your changes. Make sure
your DataAdapters use the same Connection object. The DataAdapter offers
constructors that take either a connection string or a Connection object.
If you only pass in a string, the DataAdapter will create a new Connection
for the SelectCommand. It sounds like you want to submit changes for both
tables in the same transaction. So, you'll need to submit those changes on
the same Connection object. Your best bet is to create a Connection object
and then use that object in the constructors for each DataAdapter you
create. Your code will look something like this:
Dim ds As New DataSet()
Dim strConn As String = ...
Dim cn As New SqlConnection(strConn)
Dim strSQL As String
Dim daOrders, daDetails As SqlDataAdapter
strSQL = "SELECT ... FROM Orders WHERE ..."
daOrders = New SqlDataAdpater(strSQL, cn)
strSQL = "SELECT ... FROM [Order Details] WHERE ..."
daDetails = New SqlDataAdapter(strSQL, cn)
...
cn.Open()
Dim txn As SqlTransaction = cn.BeginTransaction()
With daOrders
.InsertCommand.Transaction = txn
.UpdateCommand.Transaction = txn
.DeleteCommand.Transaction = txn
End With
With daDetails
.InsertCommand.Transaction = txn
.UpdateCommand.Transaction = txn
.DeleteCommand.Transaction = txn
End With
...
If blnAllIsWell Then
txn.Commit()
Else
txn.Rollback()
End If
SUBMITTING HIERARCHICAL CHANGES
When you submit pending inserts, you need to submit new parent rows
before submitting new child rows. With pending deletions, you need to
submit pending child deletions before submitting pending parent deletions
(unless you're cascading deletions on your foreign keys).
Thankfully, ADO.NET lets you control this process through the
overloaded DataAdapter.Update method.
You can pass an array of DataRows to the Update method. It just so
happens that the DataTable's Select method, which lets you select a series
of rows based on a filter (State = 'MA') or row version
(DataViewRowState.Added), returns an array of DataRows
So, to submit just the pending inserts for a table, you can call
da.Update(tbl.Select("", "", DataViewRowState.Added))
Passing DataTable.GetChanges(DataRowState.Added) to the Update method
sort of does the same thing, but you'll want to avoid that because of your
scenario. More on that later.
Your updating code block could look something like:
'Submit pending inserts and updates against the parent table
daOrders.Update(tblOrders.Select("", "", DataViewRowState.Added Or _
DataViewRowState.ModifiedCurrent))
'Submit changes against the child table
daDetails.Update(tblDetails.Select("", "", DataViewRowState.Added))
daDetails.Update(tblDetails.Select("", "",
DataViewRowState.ModifiedCurrent))
daDetails.Update(tblDetails.Select("", "", DataViewRowState.Deleted))
'Submit pending deletes against the parent table
daOrders.Update(tblOrders.Select("", "", DataViewRowState.Deleted))
If you're working with a third table, you'd need to further refine the
code. But, if you're only working with the two tables, you could simplify
this code a little to:
'Submit pending inserts and updates against the parent table
daOrders.Update(tblOrders.Select("", "", DataViewRowState.Added Or _
DataViewRowState.ModifiedCurrent))
'Submit changes against the child table
daDetails.Update(tblDetails)
'Submit pending deletes against the parent table
daOrders.Update(tblOrders.Select("", "", DataViewRowState.Deleted))
AUTO-INCREMENT VALUES AND HIERARCHICAL UPDATES
(from a canned response)
This is a fairly common scenario that ADO.NET handles much
better than any of its predecessors. It may seem complex at
first, but once you've handled the scenario once, it will
hopefully feel more intuitive.
1.) How do I keep pending parent and children in synch?
Set the ADO.NET DataColumn's AutoIncrement property to True
and ADO.NET will generate placeholder values for new rows. The
new values depend on the AutoIncrementStep, AutoIncrementSeed,
and the last value used in the DataTable. I recommend setting
AutoIncrementSeed and AutoIncrementStep to -1. These settings
will generate placeholder values of -1, -2, -3, ... There are two
benefits to this approach. The values won't conflict with any
that actually exist in the database. The user will not
misinterpret the placeholder value as an actual value from the
database.
As you add the parent rows and ADO.NET generates placeholder
values, use those placeholder values for your pending child rows.
The DataRelation object will make it easy to go from parent to
child and back, either in code or in bound controls.
2.) How do I fetch the new key values for the parent rows as I
submit them?
If you're using SQL Server, this process is actually very
simple. If you were writing your own queries, you would execute
an "INSERT INTO..." query to insert the new row and then execute
a "SELECT SCOPE_IDENTITY()" query to retrieve the last identity
value generated on that connection.
The DataAdapter submits changes via its InsertCommand
property. You can append ";SELECT @@IDENTITY AS MyIDColumn" to
the end of the "INSERT INTO..." query. (SQL 2000 users should
use "SELECT SCOPE_IDENTITY()..." instead of "SELECT @@IDENTITY".
See SQL Server Books OnLine for more information on why.) If
you're building your DataAdapters via Visual Studio .NET's
DataAdapter Configuration Wizard, the wizard will do this for you
automatically.
If you're writing your code by hand, make sure the
InsertCommand's UpdatedRowSource property is set to Both (the
default) or FirstReturnedRecord. This property controls whether
the DataAdapter will fetch the row returned by the query and
apply that data to the DataRow object.
This functionality is possible because SQL Server allows you
to execute a batch of queries that returns rows. However, not
all databases support this feature.
If you're working with an Access database, you'll need to go
a slightly different route. Trap for the DataAdapter's
RowUpdated event and use code to check for a successful insert.
Execute the "SELECT @@IDENTITY" query using a Command object and
assign the value returned by the query to the appropriate column
and call the DataRow object's AcceptChanges method. Your code
will look something like this:
Visual Basic .NET:
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim cn As OleDbConnection = da.SelectCommand.Connection
Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf HandleRowUpdated
Dim tbl As DataTable = CreateMyDataTable()
da.Fill(tbl)
...
da.Update(tbl)
Private Sub HandleRowUpdated(ByVal sender As Object, _
ByVal e As
OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
e.Row("OrderID") =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())
e.Row.AcceptChanges()
End If
End Sub
You can use similar techniques to retrieve server-generated
values from other databases as well. MySQL developers can use
the "LAST_INSERT_ID()" instead of "@@IDENTITY" to retrieve the
last auto-increment value generated. Oracle developers can use
"SELECT SequenceName.CURRVAL FROM DUAL" to retrieve the last
value generated for a sequence on the connection.
3.) How do I cascade the new key values to the child rows before
I submit them?
This is the simplest part of the process. When you create a
DataRelation object, ADO.NET will add a ForeignKeyConstraint
object to make sure that child rows match up to a parent row.
The ForeignKeyConstraint object exposes a UpdateRule property.
If this property is set to Cascade (the default), ADO.NET will
automatically cascade changes made to the parent down to the
associated child rows.
So, if you have a DataRelation set up between the DataTables
based on the auto-increment column, and you've set the parent
DataAdapter's InsertCommand to fetch the new auto-increment
values from the database, ADO.NET will cascade the new values
down to the associated child rows automatically.
UPDATES IN N-TIERED APPLICATIONS
If the client application does not have direct access to the database,
submitting updates becomes a little more complex. If you have a DataSet
that contains a thousand rows and you've only modified a small handful of
those rows, passing the entire DataSet between tiers would be a waste of
bandwidth. You'd get better performance by only passing the modified rows.
The DataSet's GetChanges method was designed with this scenario in mind.
It returns a DataSet with the same schema, but it only (mostly) contains
modified rows from the original DataSet. I say mostly, because some
unmodified rows may need to be included in order to satisfy constraints.
GetChanges creates a new DataSet that contains a separate copy of your
(mostly) changed data. The actions you perform on your new DataSet do not
affect the original DataSet. If you submit a pending update from your new
DataSet, the corresponding row in the original DataSet is still marked as a
pending update. If you fetch a new auto-increment value for a pending
insert into your new DataSet, the corresponding row in the original DataSet
is still marked as a pending insert with a placeholder value for that
column.
Say your client (a Windows Forms-based application) sends a DataSet
that contains (almost) only changes to a WebService, and it's the
WebService that submits the pending changes to the database. The client
application may still want to use its DataSet, and allow the user to
add/modify/delete more rows and submit those changes to the database. So,
after the WebService successfully submits a batch of changes, you'll likely
need to tell the client's DataSet that the accept the pending changes and
set them in a clean "unmodified" state. To do this, call the AcceptChanges
method on the DataSet (or DataTable or DataRow).
If your database generates values (timestamps, defaults,
auto-increment values), you'll probably need to push this data back from
the Web Service to the client application. The DataSet has a Merge method
that's designed to handle this generic scenario. The Merge method will add
or combine rows and tables based on schema. When the schemas of rows
match, the Merge method combines rows that have the same values for the
primary key columns.
You may have already guessed the challenging scenario here. If the
server returns new key values, the Merge method doesn't know how to match
up a pending insert with a key value of -1 with a successful insert with a
key value of, say, 42. So, if you merge in a DataSet returned by the
WebService that contains successful inserts with new server-generated key
values, the original pending inserts with placeholder key values will
remain. There are a number of possible solutions for this scenario, most
of which involve some ugly hacks like changing schema to use a psuedo key
or calling RowUpdatedEventArgs.Status = SkipCurrentRow in the DataAdapter's
RowUpdated event to prevent the implicit call to DataRow.AcceptChanges.
Fortunately, there's a solution that's fairly painless and only mildly
inelegant. If all updates succeeded (since you're wrapping your updates in
a transaction you're in good shape), purge the pending inserts from the
original client DataSet before merging the DataSet returned by submitting
the pending changes to the database. Your code will look something like
this:
Dim dsOriginal, dsJustChanges, dsReturnedByWebService As DataSet
...
dsJustChanges = dsOriginal.GetChanges()
dsReturnedByWebService = MyService.SubmitChanges(dsJustChanges)
'For each DataTable in original DataSet with an auto-increment key:
For Each row In tbl.Select("", "", DataViewRowState.Added)
tbl.Rows.Remove(row)
dsOriginal.Merge(dsReturnedByWebService)
