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 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?

回答1:

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.

回答2:

You can use transaction scope with different scope options