Transactions in Typed DataSets

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 c开发者_Python百科alling 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?

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.

You can use transaction scope with different scope options