c# - Dataset inserts odd child records -
the scenery: dataset main , 2 child tables (sales, salesdetail, payments), working sql server 2008 r2, on windows server 2012. dataset calls different stored procedure each insert, update , delete operation, on each table.
the error: routine works fine except once in while it: a)inserts child records not in original data table or b) inserts repeated child records.
i've noticed error happens when restore production database development machine, run app , first transaction gets several details records weren't ones typed. second time restore database, error doesn't happen.
the sql server logs show deadlocking when saving these transactions, rare.
the routine updating is:
(thanks bonnie advice on transactions: geek-goddess-bonnie.blogspot.mx)
namespace saleswincs { class class1 { sqlconnection conn; public int updatesales(dsetsales ds) { int32 _newid = 0; sqldataadapter da = null; sqldataadapter dadetail = null; using (transactionscope scope = utils.gettransactionscope()) { try { conn.open(); // preparing adapters da = getda2updatesales(conn); dadetail = getda2updatesalesdetail(conn); // prepare deleted, changed or added tables datatable deleteddetail = ds.salesdetail.getchanges(datarowstate.deleted); datatable addeddetail = ds.salesdetail.getchanges(datarowstate.added); datatable modifieddetail = ds.salesdetail.getchanges(datarowstate.modified); // delete rows if ((deleteddetail != null)) dadetail.update(deleteddetail); // main row da.update(ds, "sales"); // new id _newid = ds.sales[0].idsale; // updates detail rows if ((modifieddetalle != null)) dadetail.update(modifieddetail); // add new rows if ((addeddetail != null)) { foreach (dsetsales.salesdetailrow d in addeddetail.rows) { d.idsale = _newid; } dadetail.update(addeddetail); } // payment table processed above scope.complete(); ds.acceptchanges(); } catch (exception ex) { _newid = 0; } { if (conn.state != connectionstate.closed) conn.close(); } return _newid; } } private sqldataadapter getda2updatesales(sqlconnection conn) { sqldataadapter da = new sqldataadapter("select * sales idsale = @idsale ", conn); // prepare commands da.deletecommand = new sqlcommand("spd_sales", conn); da.insertcommand = new sqlcommand("spi_sales", conn); da.updatecommand = new sqlcommand("spu_sales", conn); da.selectcommand.commandtype = commandtype.text; da.selectcommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.insertcommand.commandtype = commandtype.storedprocedure; // da.insertcommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.insertcommand.parameters.add("@idclient", sqldbtype.int, 0, "idclient"); // other fields... da.insertcommand.parameters.add("@new_id", sqldbtype.int, 0, "idventa").direction = parameterdirection.output; // da.updatecommand.commandtype = commandtype.storedprocedure; da.updatecommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.updatecommand.parameters.add("@idclient", sqldbtype.int, 0, "idclient"); // other fields... da.deletecommand.commandtype = commandtype.storedprocedure; da.deletecommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.insertcommand.updatedrowsource = updaterowsource.both; da.insertcommand.commandtimeout = 900; da.updatecommand.commandtimeout = 900; return da; } private sqldataadapter getda2updatesalesdetail(sqlconnection conn) { sqldataadapter da = new sqldataadapter("select * salesdetail idsale = @idsale", conn); // prepare commands da.deletecommand = new sqlcommand("spd_saledetail", conn); da.insertcommand = new sqlcommand("spi_saledetail", conn); da.updatecommand = new sqlcommand("spu_saledetail", conn); da.selectcommand.commandtype = commandtype.text; da.selectcommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.insertcommand.commandtype = commandtype.storedprocedure; // da.insertcommand.parameters.add("@idsaledetail", sqldbtype.int, 0, "idsaledetail"); da.insertcommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.insertcommand.parameters.add("@idproducto", sqldbtype.int, 0, "idproduct"); da.insertcommand.parameters.add("@qty", sqldbtype.money, 0, "qty"); da.insertcommand.parameters.add("@cost", sqldbtype.money, 0, "cost"); // other fields da.insertcommand.parameters.add("@new_id", sqldbtype.int, 0, "idsaledetail").direction = parameterdirection.output; // da.updatecommand.commandtype = commandtype.storedprocedure; da.updatecommand.parameters.add("@idsaledetail", sqldbtype.int, 0, "idsaledetail"); da.updatecommand.parameters.add("@idsale", sqldbtype.int, 0, "idsale"); da.updatecommand.parameters.add("@idproducto", sqldbtype.int, 0, "idproduct"); da.updatecommand.parameters.add("@qty", sqldbtype.money, 0, "qty"); da.updatecommand.parameters.add("@cost", sqldbtype.money, 0, "cost"); // ... // da.deletecommand.commandtype = commandtype.storedprocedure; da.deletecommand.parameters.add("@idsaledetail", sqldbtype.int, 0, "idsaledetail"); da.insertcommand.updatedrowsource = updaterowsource.both; da.insertcommand.commandtimeout = 900; da.updatecommand.commandtimeout = 900; return da; } public class utils { /// <summary> /// transactionscoope gets returned here required , isolationlevel.readcommitted. /// </summary> /// <returns></returns> public static transactionscope gettransactionscope() { return new transactionscope(transactionscopeoption.required, new transactionoptions(){isolationlevel = isolationlevel.readcommitted}); } } }
Comments
Post a Comment