client-side transaction for 3 stored procedures.....

[CLRBOY]

Greetings,

 

I have 3 SP and I want to build client-side(C#) transaction.

 

this is how it looks like right now :

I have one sqlconnection and three sqlcommands(each one per SP)

 

bool InsertOne = false;

bool InsertTwo = false;

bool InsertThree = false;

SqlTransaction myTransOne = sqlConnection1.BeginTransaction();

SqlTransaction myTransTwo = sqlConnection1.BeginTransaction();

SqlTransaction myTransThree = sqlConnection1.BeginTransaction();

 

sqlCommand1.Transaction = myTransOne;

try

{

....First SP.....

myTransOne.Commit();

InsertOne = true;

}

 

sqlCommand2.Transaction = myTransTwo;

try

{

....Second SP.....

myTransTwo.Commit();

InsertTwo = true;

}

 

sqlCommand3.Transaction = myTransThree;

try

{

....Third SP.....

myTransThree.Commit();

InsertThree = true;

}

 

if ((InsertOne == false) || (InsertTwo == false) || (InsertThree == false))

{

myTransOne.Rollback();

myTransTwo.Rollback();

myTransThree.Rollback();

MessageBox("........");

}

 

 

the problem is at run-time. I get exception when I'm trying to assign the (An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: SqlConnection does not support parallel transactions.)

 

so what do I have to do ? to give every command it's own connection ??

or maybe their is another way to do this ?

 

Any Answer will be appreciated.

[HJB417]

using(SqlTransaction myTransOne = sqlConnection1.BeginTransaction())
{
try 
{
	....First SP.....
	....Second SP.....
	....Third SP.....
	myTransOne.Commit();
}
catch
{
	try
	{
		myTransOne.Rollback();
	}
	catch{}
	throw;
}
MessageBox("........");
}

 

I would keep the connection:transaction ratio at 1:1.

[CLRBOY]

using(SqlTransaction myTransOne = sqlConnection1.BeginTransaction())
{
try 
{
	....First SP.....
	....Second SP.....
	....Third SP.....
	myTransOne.Commit();
}
catch
{
	try
	{
		myTransOne.Rollback();
	}
	catch{}
	throw;
}
MessageBox("........");
}

 

I would keep the connection:transaction ratio at 1:1.

 

 

Thank you... it works excellent and economize me 2 try-catch blocks.