5

Have a typed dataset with several related tables, and relations defined between those tables. As I process a datafeed, I'm adding, modifying, and removing records, then calling update on each table.

Requests        Reapprovals        UserRole
 RequestId ----- RequestId    ----- RoleId 
 Reason          RoleId  ----/      UserId

The reason for using a typed dataset is that I have to check existing data to determine whether I'm adding, modifying, or removing records... so I need the full dump of everything I'm working with (the alternative would be 10,000 queries against the database as I process the records one by one).

I want transactional support, but I'm not seeing a way to do it with typed datasets. For example, I'm creating a new request when I create a new reapproval. But if the reapproval fails to update, I don't want to keep the request.

Putting the update calls under a TransactionScope would mean that if any record fails, they all fail. Not what I want.

How would I commit or roll back related rows in a typed dataset?

James King
  • 6,233
  • 5
  • 42
  • 63
  • It might interest you to know that calling Update on a table does, indeed, send N queries to the database, one for each record you're updating/adding/removing. So you're not _really_ gaining much of a benefit by calling Update on a table or calling Update every time you change a single row. (There may be some benefits when using a "Bulk" send option, but I don't know how major) So while you can indeed use your full in-memory dump to determine which records to modify, I suggest wrapping each series of actions for a record in its own transaction scope. – Pandincus Dec 20 '10 at 15:52
  • Knew that rows update one at a time, and considered updating the rows one at a time as I modify them... the thing that niggled at me was that I call Update() on the table, which checks each row to see if it needs updated. Meaning that I'm iterating 10,000+ rows 10,000+ times to find the one row that has been updated. Wasn't sure if that performance hit would be huge or negligible. – James King Dec 20 '10 at 18:51
  • Pandincus> If you want to enter what you're saying as an answer, I'll accept it. It looks like my choices are either take the performance hit by doing what you suggest, or accept/reject the dataset changes as a whole : ( Thanks! – James King Jan 01 '11 at 19:28

2 Answers2

3

You can use regular transactions and also achieve transaction like feature from TableAdapterManager as like in below examples.

First Approach to use regular transaction,

   public void  savewithTransacition()
    {
        DataSet1TableAdapters.Table1TableAdapter taTbl1 = new DataSet1TableAdapters.Table1TableAdapter();
        DataSet1TableAdapters.Table2TableAdapter taTbl2 = new DataSet1TableAdapters.Table2TableAdapter();
        SqlTransaction st = null;
        SqlConnection sc = new SqlConnection("ur conneciton string");
        try
        {
            sc.Open();
            st = sc.BeginTransaction();

            taTbl1.Transaction = st;
            taTbl2.Transaction = st;
            st.Commit();
        }
        catch (System.Exception ex)
        {
            st.Rollback();
            throw ex;
        }


    }

Second..with table adapter manager..

  public void SaveWithManager()
    {
        DataSet1TableAdapters.TableAdapterManager mgr1 = new DataSet1TableAdapters.TableAdapterManager();
        DataSet1TableAdapters.Table1TableAdapter taTbl1 = new DataSet1TableAdapters.Table1TableAdapter();
        DataSet1TableAdapters.Table2TableAdapter taTbl2 = new DataSet1TableAdapters.Table2TableAdapter();

        mgr1.Table1TableAdapter = taTbl1;
        mgr1.Table2TableAdapter = taTbl2;
        mgr1.UpdateOrder = DataSet1TableAdapters.TableAdapterManager.UpdateOrderOption.InsertUpdateDelete; 
        mgr1.UpdateAll(this);
    }

With this option you can create TAManagers for group of tables to save. like if you want one group to save and even if another get fail.

indiPy
  • 7,844
  • 3
  • 28
  • 39
  • I'm digging through transaction manager info, but it's a whole new level to transactions that I'm not familiar with. Do you have any code examples for what you mean, or links to resources? Thanks! – James King Dec 21 '10 at 15:47
  • Modified with some sample code, you can opt anyone which is suitable for you. – indiPy Dec 21 '10 at 18:14
  • I've played with this, but I can't seem to make it do what I need it to. In your example, what I seem to get is the ability to update all of the tables in a dataset with one call. Which is nice, but still doesn't break individual row/related row updates into individual transactions. Maybe I'm not explaining what I need well enough, or maybe I don't understand something you're showing me? I'm starting to think this just can't be done. – James King Jan 01 '11 at 19:25
  • Can you keep them in loop? and transaction for each loop so you can take decision for each loop? – indiPy Jan 01 '11 at 19:34
  • it will be heavy but still it will serve your purpose. – indiPy Jan 01 '11 at 19:35
  • I'm already in a loop, so no issues there... I was trying to avoid querying 10,000 records 10,000 times for one record each. A call to update a DataTable with 10,000 records but only one change will have to iterate all 10,000 records to get that one changed record (plus all of its child records, and their child records)... which creates the perfect environment for a transaction. But like you say, it's heavy to do that 10,000 times. I'm pretty sure there's no way to do what I want : ( – James King Jan 06 '11 at 17:46
  • Have you tried Setpoint in transaction? I don't know how it will be useful for u in this context, but for large transaction we often use setpoint – indiPy Jan 13 '11 at 13:33
  • I don't see Setpoint on Transaction, TransactionManager, or TransactionScope... what class is that a member of? – James King Jan 13 '11 at 16:48
  • Ohh,my bad, its [Save Point](http://www.java2s.com/Code/VB/Database-ADO.net/Transactionsavepointandrollback.htm) not set point. Save point of Transaction object in ADO.Net helps to commit or rollback a portion of transaction instead of Rolling back entire transaction. – indiPy Jan 13 '11 at 17:06
0

You can use transaction scope with different scope options

pdiddy
  • 6,217
  • 10
  • 50
  • 111
  • The problem is separating individual updates from the main .Update() call, so that some may succeed while some fail. – James King Dec 21 '10 at 19:43
  • You can use the Suppress option can't you? this way, the code that is in your transaction scope with Suppress option will not take part of your parent transaction ... – pdiddy Dec 21 '10 at 23:26
  • I don't think you're understanding what I'm trying to do – James King Dec 22 '10 at 16:23