Transactions in Typed Datasets

Question

Have a typed dataset with several related tables and relationships defined between these tables. When I process the datafeed, I add, modify and delete records, and then call the update for each table.

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

The reason for using a typed dataset is that I have to check the existing data to determine if I am adding, changing or deleting records ... so I need a complete dump of everything I work with (an alternative would be 10,000 database queries as I process the records one by one).

I need transaction support, but I don't see a way to do this with typed datasets. For example, I create a new query when creating a new override. But if the redefinition is not updated, I do not want to save the request.

Putting update calls under TransactionScopemeans that if any record failed, they all do not work. Not what I want.

How do I execute or reverse related rows in a typed dataset?

Answer 1

You can use regular transactions, as well as perform a transactional function from the TableAdapterManager, as shown below.

The first approach to using a 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);
    }

TAManagers . , , .

Answer 2