Transactions and SqlDataAdapters

  • Thread starter Thread starter toky
  • Start date Start date
T

toky

Is it possible to perform transaction with SqlDataAdapter.update? If so,
how?

I have two tables (one-to-many relationship), and when inserting/updating
data I need to do a database transaction so that things can rollback if
there are any problems. However, I'm also using the SqlDataAdapter update
method to make the changes to the table. So, is there any way to have a
transaction and SqlDataAdapter.update work together?
 
Assign the transaction to the
UpdateCommand.Transaction,DeleteCommand.Transaction and
InsertCommand.Transaction properties of the adapter.

If you are using a commandbuilder, then just assign the transaction to the
SelectCommand.Transaction.
 
--
e2e


Marina said:
Assign the transaction to the
UpdateCommand.Transaction,DeleteCommand.Transaction and
InsertCommand.Transaction properties of the adapter.

If you are using a commandbuilder, then just assign the transaction to the
SelectCommand.Transaction.
Click to expand...
 
Marina said:
Assign the transaction to the
UpdateCommand.Transaction,DeleteCommand.Transaction and
InsertCommand.Transaction properties of the adapter.

If you are using a commandbuilder, then just assign the transaction to the
SelectCommand.Transaction.
Click to expand...

But how, if is SqlAdapter generated by VS2005b2 for StronglyTyped dataset,
which
keep back SqlCommands in own class?

Example:
Have: Generated VS2005b2 partial classes, ADataTable and ATableAdapter,
BDataTable and BTableAdapter
Necessary: Update ADataTable and BDataTable with SqlAdapter.Update in
transaction context

I think do partial class for ATableAdapter and BTableAdapter, which
implemets Transaction property:

public partial class ATableAdapter
{
private SqlTransaction transaction;

public SqlTransaction Transaction
{
get { return transaction; }
set
{
transaction = value;
if( Adapter.InsertCommand != null )
{
Adapter.InsertCommand.Transaction = transaction;
}
if( Adapter.DeleteCommand != null )
{
Adapter.DeleteCommand.Transaction = transaction;
}
if( Adapter.UpdateCommand != null )
{
Adapter.UpdateCommand.Transaction = transaction;
}
for( int i = 0; i < this.CommandCollection.Length; i = i + 1 )
{
if( this.CommandCollection != null )
{
(System.Data.SqlClient.SqlCommand) ( this.CommandCollection
).Transaction = transaction;
}
}
}
}
}

And also for BTableAdapter...

Use it:

using(SqlConnection conn = new SqlConnection( "conn string" ))
{
SqlTransaction tran = conn.BeginTransaction();

ATableAdapter ata = new ATableAdapter();
ata.Connection = conn;
ata.Transaction = tran;
ata.Update( aTable );

BTableAdapter bta = new ATableAdapter();
bta.Connection = conn;
bta.Transaction = tran;
bta.Update( bTable );

tran.Commit();
}

Exception handling and rollbacking not include for simple

Sorry for my English
 
I'm not familiar with the 2005 wizards. But, I never use the ones in 2003.
It's just so much easier and more efficient to write the code yourself.

[email protected] said:
Marina said:
Assign the transaction to the
UpdateCommand.Transaction,DeleteCommand.Transaction and
InsertCommand.Transaction properties of the adapter.

If you are using a commandbuilder, then just assign the transaction to
the
SelectCommand.Transaction.
Click to expand...

But how, if is SqlAdapter generated by VS2005b2 for StronglyTyped dataset,
which
keep back SqlCommands in own class?

Example:
Have: Generated VS2005b2 partial classes, ADataTable and ATableAdapter,
BDataTable and BTableAdapter
Necessary: Update ADataTable and BDataTable with SqlAdapter.Update in
transaction context

I think do partial class for ATableAdapter and BTableAdapter, which
implemets Transaction property:

public partial class ATableAdapter
{
private SqlTransaction transaction;

public SqlTransaction Transaction
{
get { return transaction; }
set
{
transaction = value;
if( Adapter.InsertCommand != null )
{
Adapter.InsertCommand.Transaction = transaction;
}
if( Adapter.DeleteCommand != null )
{
Adapter.DeleteCommand.Transaction = transaction;
}
if( Adapter.UpdateCommand != null )
{
Adapter.UpdateCommand.Transaction = transaction;
}
for( int i = 0; i < this.CommandCollection.Length; i = i + 1 )
{
if( this.CommandCollection != null )
{
(System.Data.SqlClient.SqlCommand) ( this.CommandCollection
).Transaction = transaction;
}
}
}
}
}

And also for BTableAdapter...

Use it:

using(SqlConnection conn = new SqlConnection( "conn string" ))
{
SqlTransaction tran = conn.BeginTransaction();

ATableAdapter ata = new ATableAdapter();
ata.Connection = conn;
ata.Transaction = tran;
ata.Update( aTable );

BTableAdapter bta = new ATableAdapter();
bta.Connection = conn;
bta.Transaction = tran;
bta.Update( bTable );

tran.Commit();
}

Exception handling and rollbacking not include for simple

Sorry for my English
Click to expand...
 
Back
Top