Attention: We have retired the ASP.NET Community Blogs. Learn more >

DataAdapter.Update...how to handle inserts into multiple tables

When users add a new record in our app, we need to do an insert into 5 sql server tables. This insert needs to run in a transaction, and, if one insert fails, we need to do a Rollback on all the Inserts.  The first 4 inserts return keys that are used as part of the last insert.  Meaning, prior to doing the last insert, we need the key values from the first 4 inserts. 

Our initial select returns 5 resultsets, which are stored in 5 DataTables on the client.  DataRelations are built between these 5 DataTables. 

Now, on an AddNew on the client, 5 Added DataRows are created, one in each of the 5 Datatables.  If we use DataAdapter.Update, we have to send each DataTable seperately, therefore we cannot run the Insert as a whole, under one transaction.

Is therer a feature of DataAdapter.Update that I am not aware of that will allow us to somehow send all 5 DataTables at one time?  The insert sprocs are currently written as 1:1 to each sql server table.  We can easily create one sproc that executes all 5 insert sprocs, however getting the values into this main sproc is not possible unless we have one DataTable.  That is if we want to use the DataAdapter.Update model. 

One solution is to add all of the Added DataRows to one new custom DataTable just prior to the call to update, however this feels like a hack and I am not even sure this will work.

So, I have concluded this simply cannot be done with DataAdapter.  Can anyone prove me worng?  

  

28 Comments

  • We do this very thing all day long at work.

    We just use a typed DataSet and do a DataAdapter.Update(ds, "TableName")



    And go through all the tables.

    We use cascading on the DataTables in the DataSet (not the SQL database) and have the stored proces return the identity.



    So for example. It inserts an Order and returns the OrderID, which would be cascaded to the OrderItems table into its foreign key back to Orders. So then OrderItems is inserted with the foreign key.



    Make any sense?



    matthew.watson@prlnet.com

  • Or if transactions are the problem, using the Data Access Application Block you have the option to enroll a dataset update in a given transaction. Just create a transaction and use it for all 5 updates.

  • Yea, just don't forget to check your identity values and child relations in the case of a rollback on inserted records - you can get out of sync keys!!

  • Using typed datasets doesn't solve the problem that (I think) Greg is getting at, which is:



    Because DataAdapter.Update is table-specific, the are *multiple round-trips* to the db for a single (logical) update. Using a typed ds still means multiple trips.



    Transactions are not relevant. Using the DAAB isn't necessary to run the update(s) in a transaction.



    Greg, I don't think that what you want to do is possible. I'm not sure DataAdapter.Update was designed with batching in mind. I'm hoping that ADO.NET 2.0 has this functionality. It's top of my wish-list.

  • Darrell, this is exactly waht i was trying to do. Got it worked out this morning. Seems I was calling Commit outside the Try\Catch\Block and you cannot do this. Calling Commit inside the Try\Catch\Block works.

  • Johnny, you are right on target. Wrapping all 5 Inserts in a sqltransaction correctly, so far seems to be doing what we need. The need is for all Inserts to run under one transaction. I would typically batch this up in a sproc, but, since like you state the DataAdapter Update architecture works with one dataTable this is not possible when you data is stored in more than one datatable.



  • I don't know if you've found a suitable answer yet, but I think you can use sp_xml_preparedocument in order to send multiple tables/rows for update. You may not want to go that route, but I think it should allow you to do what you would like.



    -Christopher

  • Why not use a SQL Stored procedure that can add the records to all tables affected. You can begin a transaction in SQL, have SQL return the identity values you need (@@Identity) and populate the tables in sequence. If any errors occur you simply rollback the transaction and return a value to your code stating that an error occurred. This is the standard way I have been taught to handle transactions affecting multiple tables or multiple rows on given tables. Hope this helps.

  • hello,



    I have created command and using dataset and dataadapter with in the transaction.



    My problem arise only when the data adapeter excutes the line myadap.update(ds,"TableName")



    I have wasted my lot of time. but still no solution.



    Please any one can give me good solution for this.



    My emailid : deeps_2000@rediffmail.com

  • I am having a similar prob at the moment. Mine relates to the status of the dataset. I have read that you need separate dataadapters, but I am finding that the first call sets the status for the entire dataset to "unchanged". As a result, the subsequent dataadapters' updates do nothing because they are seeing no changes. BTW, I am using the DAAB and adding some of my own stuff to facilitate transactions, etc. Also finding it n.b. to map sproc params to columns (sourcecolumn and sourceversion properties). Dunno if this will help, I'm still stuck with it :-)

  • You do not need seperate DataAdapters. As you state, calling Update on DataAdapter, passing in DataSet, will cause all Modified, Deleted and Added DataRows in all DataTables to update. If you want more control, do what we now do and that is pass a DataTable to the Update method. I think you can also pass DataRow to the Update method now.




  • I'm not concerned with transactions, but does specifying which datatable during the update method, leave the rowstate `changed` for other datatables you've changed???

  • Yes, this is how we do our update, as we need more control over the update.



  • How do you do cascade update with datadapter please.

  • I got the same issue as Greg

  • Who can help me to use TableAdapter?
    Thanks.

  • I response to the initial question you could try using the uniqueidentifier type for the primary key and leave the IsRowGUID flag unchecked.

    When creating the new order record you have control over the new primary key value and therefore already have it to pass to the orderitems.

    I could be wrong though.... I'm not *that* up to speed with typed datasets and have already found some "wierdnesses" with them :)

  • I have a similar problem.
    Ive taken 2 tables and used a join to get the information from both.
    created a dataadapter and filled the dataset with the information and bound controls on my form to the datatable.
    now for the update i get error Dynamic SQL GENERATion not supported for base tables.

    i used a currency manager to navigate through the dataset.

    and i used this to update. :

    sqlConnection.Open()
    sqlManager.EndCurrentEdit()
    Dim sqlUpdate As New SqlCommandBuilder(sqlAdapter)
    sqlAdapter.Update(sqlDataTable)
    sqlConnection.Close()

    can anyone help me >

  • just concentrate on ur requirement again.

  • Hello Guys,
    so what is the conclusion of the original thread......IS THIS POSSIBLE to do multiple transaction with dataset n dataadapter.update in one single transaction begin n commit with rollback option if needed.

  • check this url

  • I have also this problem, my problem is that when I run the application I add the data in the form and click on the insert button, a dialog box is open and say that data is inserted, when I close the application the see the data in the sql database it don't show the data that is save in the form.... i use dataAdapter.Insert(), and dataAdapter.Update().

    If any body help me plz contact with me at my hotmail add i.e,. new_friend88@hotmail.om, Its urgent............

  • Is there a way to know what dataadapter.update did? (insert,update,delete which row)

    I have a datagridview, changes in the gridview is updated at the database. But I have two databases so according to changes made in the datagridview the second table should be recalculated.

    So I want to know what dataadapter.update did during the execution Did it insert,update or delete and which rows.

  • I've been thinking of the same thing.
    I believe you can just use more than one insert/update/delete statements in a batch.
    A batch is a set of statements separated by a semicolon. (Sql server supports batch statements, dont know about the others)

    For eg: adapter.InsertCommand=new xxxCommand("insert into Table1 values (x,y,z) ; Insert into Table2 values ...")

    Make sure you use & associate a transaction object to the command (so that u can rollback on error)

    This has to work but not sure. I'm about to try it myself

  • Добавлю в закладки, интеретсно читать

  • Hello,

    I think I've been trying to figure out something similar for this afternoon and found nothing that exactly hit on what I was trying to do. But if this is what anyone's looking for, here's how I solved it:

    (This is a test Access DB)
    tables: namesb, namesc.

    namesb has id, firstname, lastname, address.
    namesc has id, city, state.

    So with ONE DataAdapter/DataSet/DataGrid, I displayed all of the data, linked up on id, and wrote the changes back to the database with one updatecommand:



    Dim updatecmd As New OleDb.OleDbCommand("update namesb inner join namesc on namesb.id = namesc.id set namesb.id=@id, namesc.id=@id, namesb.firstname=@firstname, namesb.lastname=@lastname, namesb.address=@address, namesc.city=@city, namesc.state=@state where namesb.id=@id", db)

    With updatecmd.Parameters
    .Add(New OleDb.OleDbParameter("@id", OleDb.OleDbType.Numeric, 4, "id"))
    .Add(New OleDb.OleDbParameter("@firstname", OleDb.OleDbType.VarChar, 50, "firstname"))
    .Add(New OleDb.OleDbParameter("@lastname", OleDb.OleDbType.VarChar, 50, "lastname"))
    .Add(New OleDb.OleDbParameter("@address", OleDb.OleDbType.VarChar, 50, "address"))
    .Add(New OleDb.OleDbParameter("@city", OleDb.OleDbType.VarChar, 50, "city"))
    .Add(New OleDb.OleDbParameter("@state", OleDb.OleDbType.VarChar, 50, "state"))
    End With

    ds = New DataSet

    DataGrid1.DataSource = Nothing
    DataGrid1.DataMember = Nothing

    da = New OleDb.OleDbDataAdapter("select namesb.id, namesb.firstname, namesb.lastname, namesb.address, namesc.city, namesc.state from namesb, namesc where namesb.id = namesc.id", db)

    da.UpdateCommand = updatecmd

    da.Fill(ds, "namesb")

    DataGrid1.DataSource = ds
    DataGrid1.DataMember = "namesb"


    Then in my "Commit" button, I've got:

    da.Update(ds, "namesb")


    So this is updating multiple tables with one DataAdapter/DataSet.

  • Hello,
    I have a similiar problem, I have 3 tables, the first on returns a key that I need put on 2 other tables and make insert or update and all in a transaction. The problem is when my internet link down the rollbak dosent work because the connection is close, fine at this point is normal behavior, but the real problem is the rowstate is this table are changed to modify insted insert. I dont know how solve this issue.

  • I am so lost in how to handle inserts into multiple tables. Is there anywhere on the web I can go that explains it "barney the dinosaur style" with examples? I have to capture the id_key from each table to complete the next one and to the user it has to all be done on one form/page/view.

Comments have been disabled for this content.