Yet another DataAdapter and Transaction problem

[sergio.rolanski]

I was wondering if the following piece of code is wrong in some way.
The idea:

1. call webservice (which will return lots of information)
2. Fill dataset (with many tables) with data returned by webservice
3. Start transaction
4. Update database
5. Commit changes to database
6. Do it all over again until webservice returns especific value
saying that no more information is available

This is not actually using the transaction. When the application stop
at breakpoint i have set (look for comment below), i can see the data
through Query Analyzer, but I didn't commit the transaction yet!!! Any
thoughts?

Also I would like to know if the transaction get disposed after I
dispose the data adapter and its commands?

(null checks and some other stuff has been ommitted, ask if you need
more information to answer my question)

public doSomething() {
someArrayOfObjects = getDataFromSomeWebservice()

try {
_connection.Open();

for (someArrayOfObjects) {
// fill tables in the dataset with some data
// Here I add records to many tables in the dataset (but not always
all tables)

// Keeping this here so I can reuse the objects inside the next
loop
SqlCeDataAdapter da = new SqlCeDataAdapter();
SqlCeCommandBuilder cb = new SqlCeCommandBuilder();

// Connection is open outside of this loop
transaction = _connection.BeginTransaction();

// write the new data I have add to the tables in the dataset to
the database
foreach ( string tb in tables ) {
da.SelectCommand = new SqlCeCommand( "select * from " + tb,
_connection );

cb.DataAdapter = da;

cb.RefreshSchema();
da.InsertCommand = cb.GetInsertCommand();

da.Update( ds, tb );
DisposeAdapter( da ); // BREAKPOINT HERE
}

transaction.Commit();
}
} catch () {
transaction.rollback();
}
}

public void DisposeAdapter(SqlCeDataAdapter dbAdapter) {
if ( dbAdapter == null )
return;

SqlCeCommand cmd = dbAdapter.SelectCommand;
if ( cmd != null )
cmd.Dispose();

cmd = dbAdapter.InsertCommand;
if ( cmd != null )
cmd.Dispose();

cmd = dbAdapter.UpdateCommand;
if ( cmd != null )
cmd.Dispose();

cmd = dbAdapter.DeleteCommand;
if ( cmd != null )
cmd.Dispose();
}

 

[sergio.rolanski]

Well think my karma to find the answer after I have done the post is
still on going...

Correc me if I'm wrong, this would be the correct way to do it:

da.InsertCommand = cb.GetInsertCommand();
da.InsertCommand.Transaction = transaction;