Microsoft Data blocks and transactions in more than 1 database

[Mike]

Hi,



I'm using Microsoft Data Block's SQLHelper to insert record in a DB.

As a parameter to the function I pass a stored procedure and all the
stored procedure parameters



I need to use transaction to do this, but my problem is that I have to
insert information across 4 databases, and as far as I know the
transaction is associated to a connection.

Am I right?



I use something like this:



Dim ConnectionOrders As SqlConnection = New
SqlConnection(csOrders)

Dim TransactionOrders As SqlTransaction =
ConnectionOrders.BeginTransaction





How can I solve this problem??



Thanks

 

[OZI]

The Microsoft's ADO.NET version 2.0 added a lot of new features to its earlier counterpart to add more flexibility and ease of use. As far as transactions are concerned, a new namespace called System.Transactions has been introduced that promises a significantly improved support for distributed transactions.

TransactionScope also has support for distributed transactions. We can implement transactions for multiple database connections using it. The following piece of code shows how we can implement transactional support for multiple databases using the TransactionScope class.
using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection codesDatabaseConnection = new SqlConnection(codesDatabaseConnectionString))
{
SqlCommand sqlCommandCodes = codesDatabaseConnection.CreateCommand();
sqlCommandCodes.CommandText = "Insert Into codes (codeID,codeText) values (1,'Test')";
codesDatabaseConnection.Open();
sqlCommandCodes.ExecuteNonQuery();
codesDatabaseConnection.Close();
}

using (SqlConnection statesDatabaseConnection = new SqlConnection(statesDatabaseConnectionString))
{
SqlCommand sqlCommandStates = statesDatabaseConnection.CreateCommand();
sqlCommandStates.CommandText = "Insert into States(stateID,stateName) values (1, 'Test')";
codesDatabaseConnection.Open();
sqlCommandStates.ExecuteNonQuery();
statesDatabaseConnection.Close();
}

transactionScope.Complete();
}



All of this was from http://www.sql-server-performance.com/articles/asp_ado/ado_transactions_p1.aspx







Hi,



I'm using Microsoft Data Block's SQLHelper to insert record in a DB.

As a parameter to the function I pass a stored procedure and all the stored procedure parameters



I need to use transaction to do this, but my problem is that I have to insert information across 4 databases, and as far as I know the transaction is associated to a connection.

Am I right?



I use something like this:



Dim ConnectionOrders As SqlConnection = New SqlConnection(csOrders)

Dim TransactionOrders As SqlTransaction = ConnectionOrders.BeginTransaction





How can I solve this problem??



Thanks