using Transactionscope with 2 dbs

When I use transactions with sql server I usually do this...
using (TransactionScope scope = new TransactionScope)
{
using (SqlConnection1 = new SqlConnection . . . . .

and this all works great. But now, I need to stretch the transaction across
to sql databases using two different connections i.e.

using (TransactionScope scope = new TransactionScope)
{
using (SqlConnection1 = new SqlConnection . . . . .
{
work
work
}//dispose of connection

using (SqlConnection2, - new SqlConnection . . . . .
{
work
work
}//dispose of connection
scope.Complete();
}//dispose of transaction scope

Is this supposed to work this way? Can a TransactionScope stretch across
many databases and cause a rollback of them all if an exception occurs?

--
Regards,
Gary Blakely

RE: using Transactionscope with 2 dbs

try something like

try
{
using(TransactionScope scope = new TransactionScope())
{
using(conn1 = new SqlConnection(strConnection1))
{
Work1();

using(conn2 = new SqlConnection(strConnection2))
{
Work2();
}
}
scope.Complete();
}
}
catch(TransactionAbortedException ex)
{
ErrorHandler(ex);
}


--
Misbah Arefin
https://mcp.support.microsoft.com/profile/MISBAH.AREFIN
http://www.linkedin.com/in/misbaharefin


"GaryDean" wrote:

> When I use transactions with sql server I usually do this...
> using (TransactionScope scope = new TransactionScope)
> {
> using (SqlConnection1 = new SqlConnection . . . . .
>
> and this all works great. But now, I need to stretch the transaction across
> to sql databases using two different connections i.e.
>
> using (TransactionScope scope = new TransactionScope)
> {
> using (SqlConnection1 = new SqlConnection . . . . .
> {
> work
> work
> }//dispose of connection
>
> using (SqlConnection2, - new SqlConnection . . . . .
> {
> work
> work
> }//dispose of connection
> scope.Complete();
> }//dispose of transaction scope
>
> Is this supposed to work this way? Can a TransactionScope stretch across
> many databases and cause a rollback of them all if an exception occurs?
>
> --
> Regards,
> Gary Blakely
>
>
>

RE: using Transactionscope with 2 dbs

Hi Gary,

Sure, multiple connections are supported to be joined in the same
TransactionScope. And this is exactly the powerful feature of the new
System.Transactions namespace which help implicitly use MSDTC to perform
distributed transaction. (single connection based transaction can be done
via the ADO.NET 1.1 style ). Here are some articles that also mentioned
this:

#ADO.NET and System.Transactions
http://msdn2.microsoft.com/en-us/magazine/cc163847.aspx#S4

#Revisiting System.Transactions
http://msdn2.microsoft.com/en-us/magazine/cc163527.aspx

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------
>From: "GaryDean"
>Subject: using Transactionscope with 2 dbs
>Date: Wed, 16 Apr 2008 17:10:24 -0700

>
>When I use transactions with sql server I usually do this...
>using (TransactionScope scope = new TransactionScope)
> {
> using (SqlConnection1 = new SqlConnection . . . . .
>
>and this all works great. But now, I need to stretch the transaction
across
>to sql databases using two different connections i.e.
>
>using (TransactionScope scope = new TransactionScope)
> {
> using (SqlConnection1 = new SqlConnection . . . . .
> {
> work
> work
> }//dispose of connection
>
> using (SqlConnection2, - new SqlConnection . . . . .
> {
> work
> work
> }//dispose of connection
> scope.Complete();
> }//dispose of transaction scope
>
>Is this supposed to work this way? Can a TransactionScope stretch across
>many databases and cause a rollback of them all if an exception occurs?
>
>--
>Regards,
>Gary Blakely
>
>
>