none
how to insert/update data in two tables with dataadapter RRS feed

  • Question

  • Can someone show an example on how to update/insert data into two tables at the same time using a MySqlDataAdapter?

    I have two tables: storehardware, and hardware, that i want to insert/update data. I set the selectCommand on the adapter to this:

     

    select 
    h.serialNumber, 
    h.hardwareStatus, 
    ht.hardwareTypeID, 
    ht.hardwareDescription, 
    ht.hardwareGroup 
    from storehardware sh 
    join store s ON s.storeID = sh.storeID 
    join  hardware h ON sh.hardwareID = h.hardwareID 
    join hardwaretype ht ON ht.hardwareTypeID = h.hardwareTypeID WHERE s.storeID=@storeID

    I have seen many examples where the insertCommand on the adapter updates one table.

    This is what I normally would do when not using the insertcommand on the adapter.

    call my insert statement for the hardware table first

    insert INTO `HARDWARE`(`hardwareID`, `serialNumber`, `hardwareTypeID`, `hardwareStatus`) 
    VALUES (@hardwareID, @serialNumber, @hardwareTypeID, @hardwareStatus);
    

    if this is successful i get the last insert id which would be the hardwareID and use this id in my next insert.

    select last_insert_id();
    
    insert INTO `STOREHARDWARE`(`hardwareID`, `storeID`) VALUES (@hardwareID, @storeID);
    What are my options here if i want to use the adapter for my inserts and updates now?

     

    • Moved by Helen Zhou Wednesday, March 24, 2010 1:31 AM (From:Windows Forms General)
    Tuesday, March 23, 2010 9:13 PM

Answers

  • If provider for MySql supports batches, then you can combine multiple INSERT or UPDATE statements as a single SQL statement by providing semicilon separator between them. So INSERT would look something like

    insert INTO `HARDWARE`(`hardwareID`, `serialNumber`, `hardwareTypeID`, `hardwareStatus`) VALUES (@hardwareID, @serialNumber, @hardwareTypeID, @hardwareStatus);insert INTO `MyOtherTable`(..........);
     

    Another option is to create stored procedure that would accept all the values through parameters and the implement all the INSERT or UPDATE SQL statements inside of that stored procedure. Then your code would need to call single stored procedure

     


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, March 24, 2010 10:16 AM
    Moderator
  • DataAdapters are designed to submit updates to a single table.  Submitting updates to multiple tables is possible if the provider and store support batched queries (as Val points out) but this approach can become extremely complex, especially if you want to retrieve server generated values (which in this case you do) or if there's a chance that sending the changes in a DataRow results in a successful update against one table but a failed update against another table (always a possibility in a multi-user application).

     

    The simplest solution is to have each DataTable contain data from a single database table and create DataRelations between the related DataTables in the DataSet. Once you retrieve the new key value for the row in the Hardware DataTable, the DataRelation will cause the new value to cascade down to the child DataTable(s) so those inserts will succeed.

     

    If you need to see the data look like it came from a JOIN query, you can use expression columns that reference the DataRelation.  So, the HardwareStatus column in the Hardware DataTable would look like it's also a part of the child DataTable.  If I remember correctly, the syntax would be "Parent(DataRelationName).HardwareStatus".

     

    I hope this information proves helpful.


    David Sceppa
    Thursday, March 25, 2010 6:07 AM
    Moderator

All replies

  • Look like you do not need help in Windows Forms. Try the ADO.Net DataSet forum under the data platform development category.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
    Visual C++ MVP
    Tuesday, March 23, 2010 11:38 PM
  • If provider for MySql supports batches, then you can combine multiple INSERT or UPDATE statements as a single SQL statement by providing semicilon separator between them. So INSERT would look something like

    insert INTO `HARDWARE`(`hardwareID`, `serialNumber`, `hardwareTypeID`, `hardwareStatus`) VALUES (@hardwareID, @serialNumber, @hardwareTypeID, @hardwareStatus);insert INTO `MyOtherTable`(..........);
     

    Another option is to create stored procedure that would accept all the values through parameters and the implement all the INSERT or UPDATE SQL statements inside of that stored procedure. Then your code would need to call single stored procedure

     


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, March 24, 2010 10:16 AM
    Moderator
  • DataAdapters are designed to submit updates to a single table.  Submitting updates to multiple tables is possible if the provider and store support batched queries (as Val points out) but this approach can become extremely complex, especially if you want to retrieve server generated values (which in this case you do) or if there's a chance that sending the changes in a DataRow results in a successful update against one table but a failed update against another table (always a possibility in a multi-user application).

     

    The simplest solution is to have each DataTable contain data from a single database table and create DataRelations between the related DataTables in the DataSet. Once you retrieve the new key value for the row in the Hardware DataTable, the DataRelation will cause the new value to cascade down to the child DataTable(s) so those inserts will succeed.

     

    If you need to see the data look like it came from a JOIN query, you can use expression columns that reference the DataRelation.  So, the HardwareStatus column in the Hardware DataTable would look like it's also a part of the child DataTable.  If I remember correctly, the syntax would be "Parent(DataRelationName).HardwareStatus".

     

    I hope this information proves helpful.


    David Sceppa
    Thursday, March 25, 2010 6:07 AM
    Moderator
  • Val and David, thank you for the replay. I changed my database design around, and i only really needed one table for the case. I removed the store hardware table and just put the storeID in the hardware table.

    Thursday, April 8, 2010 3:03 PM